September 27, 2012 at 8:15 pm
Hi,
I'm trying to do something that (I think) should be fairly straightforward.
I'm creating a report (SQL Server 2008 Reporting Services) where a sequential series of dates should be displayed in an expanding list (as in image Ex.jpg).
However, when I expand March, it includes the dates for July as well. (See img Ex2.jpg). April, May and June are as expected. When I expand the date range in the tablix filter to include 2012 dates, the resultant report is even worse: December 2011 appears in August, February 2012 and March 2012 are bound up in the month of January 2012, and May, June and July are bound up in April. (See Ex3.jpg)
The design of the report is as shown in image Ex.Design.jpg.
The Row groups are grouped on the expressions:
Year: =year(Fields!CalDate.Value)
Month: =Year(Fields!CalDate.Value) and month(Fields!CalDate.Value)
Date: Details row
The Cell expressions are:
Year: =YEar(Fields!CalDate.Value)
Month: =monthname(month(Fields!CalDate.Value))
Date: =FormatDateTime(Fields!CalDate.Value,dateformat.ShortDate)
The dataset is generated by the following query:
WITH CTE (dtDate)
AS
(
SELECT CAST(DATEADD(DAY, -555, GETDATE()) AS DATE)
UNION ALL
SELECT DATEADD(DAY, 1, dtdate)
FROM cte
WHERE dtDate <= GETDATE()
)
SELECT *
FROM cte
ORDER BY 1
OPTION (MAXRECURSION 731)
The column "dtDate" is mapped to the field "CalDate"
I've no idea why something seemingly so straightforward would result in a report displayed like this. Any thoughts, suggestions or remedies welcomed!
Many thanks,
Simon
September 28, 2012 at 7:27 am
I would just use a date/calendar table: a table containing nothing but dates and date parts. Here is some sample data of a few of the columns I have in mine:
DateID date ShortDateName LongDateName DateOnlyCYYearFYYearCYQuarterFYQuarterCYQuarterNameFYQuarterName
20070102 2007-01-02 00:00:00.000 Tue 2-Jan-2007 Tuesday, January 2nd 2007 2007-01-022007200711CY2007Q1FY2007Q1
It is hard to get everything to line up so that it is clear, but this sort of table helps to solve a lot of common problems in reporting and is well worth building.
October 11, 2012 at 4:36 pm
Thanks for the reply, Daniel. I came up with the solution...
Whilst I didn't use your solution, it prompted me to rethink how I format the data coming back from the data source. Rather than use SSRS to split the date into constituent parts and group on that, I split the date up in the query itself, and then used SSRS to simply group on the results:
i.e.:
WITH CTE (dtDate)
AS
(
SELECT CAST(DATEADD(DAY, -555, GETDATE()) AS DATE)
UNION ALL
SELECT DATEADD(DAY, 1, dtdate)
FROM cte
WHERE dtDate <= GETDATE()
)
SELECT YEAR(dtDate) AS YEAR, MONTH(dtDate) AS MONTH, dtdate
FROM cte
ORDER BY 1, 2
OPTION (MAXRECURSION 731)
This also then allowed me to simplify some of the grouping expressions used in SSRS.
My report now works as I wanted it! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply