Solved

Problem with SSRS calendar report

Posted on 2013-12-04
15
845 Views
Last Modified: 2013-12-19
I adapted the method you can see in this article

http://rduclos.wordpress.com/2010/02/13/ssrs-2008-generate-calendars-based-on-a-date-range/

My filters appear to be breaking the report. You can see the problem in the screenshot I attached. Instead of Monday through Friday, the days are a jumble. And the 18th is displaying after the 22nd. Any ideas on what this could be would be appreciated!
0
Comment
Question by:J C
  • 8
  • 7
15 Comments
 
LVL 12

Expert Comment

by:duttcom
ID: 39696930
There's no file attached, but at first glance, could it be that your server is running with date/time settings that are in the format DD/MM/YYYY? I note that the code on that page you have linked to uses the US date format MM/DD/YYYY.
0
 

Author Comment

by:J C
ID: 39696943
Sorry here is the screenshot. I'll check that.
ss.png
0
 

Author Comment

by:J C
ID: 39697009
I am using the tablix filter but I wonder if I need to be passing the parameters directly to the stored procedure instead? Maybe that's the issue?
0
 
LVL 12

Expert Comment

by:duttcom
ID: 39697027
I think you may be right - the source page has a comment on it from someone who did not use the stored procedure and had some problems with dates appearing out of order.

I'm not sure if you read the other article linked to in the source pages, but there does seem to be some more detail there on the report setup which specifically shows how to group the matrix to create a calendar -

https://www.simple-talk.com/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/
0
 

Author Comment

by:J C
ID: 39697074
I added the parameters to the stored procedure and removed the filters I had created within the tablix. When I try to run the report it tells me that the stored procedure expected the @ProjID parameter but it was not provided. I have a parameter defined for the report named ProjID and I am selecting a value. Here is the stored procedure. I included a screenshot

USE [ProjectMgmt]
GO
/****** Object:  StoredProcedure [dbo].[sp_ScheduleCalendar]    Script Date: 12/04/2013 14:12:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_ScheduleCalendar] (@StartDate DATETIME, @EndDate DATETIME, @ProjID varchar(16), @Lot varchar(16), @Status varchar(16)) AS
SELECT @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)) --FirstDayOfMonth
SELECT @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0)) --LastDayOfMonth

; WITH Months AS (
	SELECT
	 [Month] = DATEPART(MONTH,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
	 [Year] = DATEPART(YEAR,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
	 FirstDayOfMonth = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)),
	 LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)),
	 FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)))+1,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
	 LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)))
	UNION ALL SELECT
	 [Month] = DATEPART(MONTH,DATEADD(MONTH,1,FirstDayOfMonth)),
	 [Year] = DATEPART(YEAR,DATEADD(MONTH,1,FirstDayOfMonth)),
	 FirstDayOfMonth = DATEADD(MONTH,1,FirstDayOfMonth),
	 LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)),
	 FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(MONTH,1,FirstDayOfMonth))+1,DATEADD(MONTH,1,FirstDayOfMonth)),
	 LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)))
	FROM
	 Months
	WHERE
	 LastDayOfMonth < @EndDate
), Dates AS (
	SELECT
	 [Month],
	 [Year],
	 [Date] = FirstDayOfCalendar,
	 FilterDate = LastDayOfCalendar
	FROM
	 Months
	UNION ALL SELECT
	 [Month],
	 [Year],
	 [Date] = DATEADD(DAY,1,[Date]),
	 FilterDate
	FROM
	 Dates
	WHERE
	 [Date] < FilterDate
), TaskStartDate AS (
	SELECT
	 e.StartDate,
	 [Day] = DATEPART(DAY, e.StartDate),
	 [Month] = DATEPART(MONTH, e.StartDate),
	 [Year] = DATEPART(YEAR, e.StartDate),
	 TaskCode = e.TaskCode,
	 ProjectID = e.projid,
	 ProjectName = e.name,
	 Lot = e.phase,
	 description = e.description,
	 status = e.status,
	 AED = e.ActualEndDate
	 
	FROM
	 [ProjectMgmt].[dbo].[Lot_Schedules] e
	 WHERE e.projid = @ProjID AND e.phase = @Lot AND e.status = @Status
) SELECT
 DisplayOnCalendar = DENSE_RANK() OVER (ORDER BY d.Year, d.Month),
 d.Month,
 [Day] = DATEPART(DAY,d.[Date]),
 d.Year,
 [WeekDay] = DATEPART(WEEKDAY, d.[Date]),
 [Order] = DENSE_RANK() OVER (PARTITION BY d.Year, d.Month ORDER BY d.Date), 
 d.Date,
 tcd.TaskCode,
 tcd.ProjectID,
 tcd.Lot,
 tcd.AED,
 tcd.ProjectName,
 tcd.description,
 tcd.status
FROM
 Dates d
 LEFT JOIN TaskStartDate tcd ON tcd.Month = DATEPART(MONTH,d.[Date]) AND tcd.Day = DATEPART(DAY,d.[Date])

OPTION (MAXRECURSION 1000)

Open in new window

ss2.png
0
 
LVL 12

Expert Comment

by:duttcom
ID: 39697198
I think this is occuring because when you click the preview tab in SSRS it runs the report immediately instead of waiting for the parameters to be passed to it.

You have declared the start and end dates with your code, but not the ProjID. If you can give the ProjectID a default value in the same way as the start and end dates, the report should run.
0
 
LVL 12

Expert Comment

by:duttcom
ID: 39697216
Also double check that the parameter has been specified correctly in the  dataset, with the parameter name @ProjID and the parameter value  [@ProjID]
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:J C
ID: 39697257
I am selecting the ProjID before running the report so the value should exist right?

In the dataset I am not specifying the parameters, I just have the name of the stored procedure which it executes and it seems to pass the start and end date without issue. Not sure why it's failing.
0
 
LVL 12

Accepted Solution

by:
duttcom earned 500 total points
ID: 39697262
The parameter needs to be in the dataset. Even though you are using a stored procedure to create the dataset, you will still need to make sure that the parameter is being passed to it.

For example -
SSRS Parameter
0
 

Author Comment

by:J C
ID: 39697268
So it should look something like:

sp_ScheduleCalendar @ProjID = [@ProjID] ?
0
 
LVL 12

Expert Comment

by:duttcom
ID: 39697274
Right-click on the dataset and choose properties. The second option on the left is parameters.

Set them to look like the example above.
0
 

Author Comment

by:J C
ID: 39697296
Looks like the system created them automatically.
ss3.png
0
 
LVL 12

Expert Comment

by:duttcom
ID: 39697325
Thanks for your patience, this is difficult without being able to fiddle with the code. I notice you have referred to the project ID in a couple of different ways in the stored procedure. Is it possible that one of these is incorrect?

ProjectID = e.projid, then later WHERE e.projid = @ProjID etc

I'm not sure what's going on with ProjectID there. It might be worth stepping through the instances of ProjId and ProjectID to see if one of them is looking to a value that isn't set.

Also, what happens if you access the report via a web browser and include the ProjID value in the url or right-click on the dataset and run the stored procedure as a query from there, entering the parameter values manually?
0
 
LVL 12

Expert Comment

by:duttcom
ID: 39697352
I think ProjectID = e.projid on line 60 should be ProjID = e.projid since you don't refer anywhere else to ProjectID
0
 

Author Comment

by:J C
ID: 39710602
The issue seems to be that I was using filters on the tablix level. It mostly seems to be working now leaving the parameters set on the dataset level only. The issue I am running into now is that it only shows the days where a task exists whereas the original report would show all the days within the date range.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now