Link to home
Start Free TrialLog in
Avatar of J C
J CFlag for United States of America

asked on

Problem with SSRS calendar report

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!
Avatar of duttcom
duttcom
Flag of Australia image

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.
Avatar of J C

ASKER

Sorry here is the screenshot. I'll check that.
ss.png
Avatar of J C

ASKER

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?
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/
Avatar of J C

ASKER

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
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.
Also double check that the parameter has been specified correctly in the  dataset, with the parameter name @ProjID and the parameter value  [@ProjID]
Avatar of J C

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of duttcom
duttcom
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of J C

ASKER

So it should look something like:

sp_ScheduleCalendar @ProjID = [@ProjID] ?
Right-click on the dataset and choose properties. The second option on the left is parameters.

Set them to look like the example above.
Avatar of J C

ASKER

Looks like the system created them automatically.
ss3.png
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?
I think ProjectID = e.projid on line 60 should be ProjID = e.projid since you don't refer anywhere else to ProjectID
Avatar of J C

ASKER

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.