Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problem with SSRS calendar report

Posted on 2013-12-04
15
Medium Priority
?
892 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

885 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