Dynamic Dates Report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Dates Report

Author

Topic

Wrencher7827
Starting Member

4 Posts

Posted - 2012-03-16 : 23:11:31

Hello all,

I'm trying to work out an SQL query to be used in SSRS tablix report. The report is a break down a count of items purchased. The start and end dates will vary, the report rows will be Products, the column headers will be the year periods. The start and end dates will be parameters passed from user input. I've hardcoded it for this example.

Product 12/1/08-11/30/09 12/1/09-11/30/10 12/31/10-8/31/11
A
B
C
D

SELECT #12/1/2008# AS StartDate
, #8/1/2011# AS EndDate
, Fix(DateDiff("yyyy",[StartDate],[EndDate])-Int(Format([enddate],"mmdd")>=Format([startdate],"mmdd"))) AS NoofGroups
, Table1.OrderDate
, Switch([orderdate] Between [startdate] And DateAdd("yyyy",1,[startdate]),"Group1", ([orderdate] Between DateAdd("yyyy",1,[startdate]) And DateAdd("yyyy",2,[startdate]),"Group2","Group3")
FROM Table1
WHERE (((Table1.OrderDate) Between #12/1/2008# And #8/1/2011#))

The problem I'm having is while I can get the count of groups needed, I don't know how to do the “Switch/Case" part correctly so that it uses the right number of groups depending on the start and end dates.

Thanks very much.

Wrencher7827

P.S. Some of this code was written in Access 2010 so it may not yet have the correct syntax for SSRSr2 Report Builder 3.0. I’ll be working on it in AdventureWorks. I just wanted to get this out there for some guidance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 23:17:02

why not use a matrix container with column grouping as datefield

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Wrencher7827
Starting Member

4 Posts

Posted - 2012-03-17 : 17:43:56

Is there an example/sample available so I can study it? I understand how to do calendar years, months and fiscal years for report columns as we have a Date Values table to join to various data tables.

What I don't understand how to program is a range of data that needs to be broken into years based on any startdate. I don't see an option in report builder that automatically breaks data into separate columns based on 12-month periods, not calendar periods, but maybe I've missed it.

Wrencher7827
Starting Member

4 Posts

Posted - 2012-03-17 : 20:29:24

Hello again,

I found some code that works to break the data in a tablix into calendar year columns!

1. Add a matrix to the report
2. Drag the row group field from dataset to the rows section in the matrix
3. Drag the date field from dataset to the columns section in the matrix
4. Drag sales amount or any other values from dataset to the data section in the matrix
5. Click on the matrix
6. Right click the column group in the group pane (located in the bottom area of the designer) and select properties
7. Set the group on expression to =Int(Format(Fields!Date.Value,"yyyy"))
8. Click on the sorting tab, and set the sorting expression also same =Int(Format(Fields!Date.Value,"yyyy")), set the order to "Z to A" meaing descending (as per your requirement)
9. Right click the cell that displays the column header and the set the expression as Format(Fields!Date.Value,"yyyy").

Now, how to edit this to work with StartDate in 12-month periods!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-18 : 10:52:32

for that flexible window, implement the filter in sql backend or inside report

the logic will be like


datefield>= @StartDate
AND datefield< DATEADD(mm,DATEDIFF(mm,0,StartDate)+13,0)
GROUP BY DATEPART(mm,StartDate)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Wrencher7827
Starting Member

4 Posts

Posted - 2012-03-20 : 00:01:30

Thank you visakh16 for all the help. I was able to get the report to work as we needed. In the report's SQL Query, I pulled in both YearVal and MonthVal from the joined Date Values table and the set the code for PrdVal,

Case When MonthVal >= Month(StartDate) then Year(OrderDate) else Year(OrderDate) -1 as PrdVal

. Then used PrdVal as column group in matrix. Bumped the resulting report against our current report (which the new report is replacing) and the output matches whichever startdate is selected.

Again thanks...!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 14:56:05

welcome...glad that you sorted it out!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources