Solved

Problem with SSRS calendar report

Posted on 2013-12-04
15
880 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

627 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