Add a Date Range Dataset in SQL Server Reporting Services


By:   |   Updated: 2014-12-16   |   Comments (6)   |   Related: > Reporting Services Development


Problem

A few years ago, I was working as a contractor for a Federal Government organization on the National Help Desk. It was my responsibility to build and maintain managerial statistical SQL Server Reporting Services reports. It would slow down my productivity each time I had to come up with a new date range as a default to be used in a new report.  How can I manage dates in a consistent way in SQL Server Reporting Services?

Solution

After spending a lot of time focusing on coming up with default date ranges, I finally decided to come up with a date range dataset to be used in all of my reports. Here's how to do it. Note: For purposes of this example, I have updated the Microsoft Adventure Works 2008LT database for more current date ranges.

Create a Simple SSRS Report with the Wizard

Please see the tutorial on how to "Create a Simple Report with the Wizard."

Add Date Range Dataset in Reporting Services

1. Under Report Data on the Left-hand side of the report designer, Right-click on Datasets:

Add New Dataset

2. Click on Add Dataset and enter the following information:

  • Name = DateRange
  • Select "Use a dataset embedded in my report."
  • Datasource = Database for the report
  • Query Type = Text
  • Query = Copy and paste Select Statement from code sample below - "SELECT GETDATE() as Today..."
  • Finally, Click the OK button
New Dataset Properties Window

3. Right-Click on Parameters and click "Add Parameter...":

Add Parameter

4. Enter the following on the "General" tab:

  • Name = StartDate
  • Prompt = Start Date
  • Data type: Select "Date/Time" from the dropdown list
  • Select parameter visibility: Visible
Parameter - General Tab

5. Enter the following on the "Default Values" tab:

  • Select Get values from a query
  • Dataset = DateRange
  • Value field = Choose the Start Date from of the date range from the dropdown list. Note: I have selected FirstDayofLastYear for this example.
  • Click the OK button
Parameter - Default Values Tab

6. Right-Click on Parameters and click "Add Parameter...":

Add Parameter

7. Enter the following on the "General" tab:

  • Name = EndDate
  • Prompt = End Date
  • Data type: Select "Date/Time" from the dropdown list
  • Select parameter visibility: Visible
Parameter - General Tab

8. Enter the following on the "Default Values" tab:

  • Select Get values from a query
  • Dataset = DateRange
  • Value field = Choose the End Date from of the date range from the dropdown list. Note: I have selected LastDayofLastYear for this example.
  • Click the OK button
Parameter - Default Values Tab

9. Preview your report and the parameterized dates should appear, pre-filled:

Preview Report

Code samples

Sample Code for query against the AdventureWorks2008LT Database for Report Sample. Please note that you must put the variables of @StartDate and @EndDate in your query for the report:

SELECT
  c.ParentProductCategoryName
, c.ProductCategoryName
, d.ModifiedDate  
, SUM(d.LineTotal) Sales
FROM SalesLT.Product p
JOIN SalesLT.vGetAllCategories c 
  ON c.ProductCategoryID = p.ProductCategoryID
JOIN SalesLT.SalesOrderDetail d 
  ON d.ProductID = p.ProductID  
--WHERE d.ModifiedDate >= @StartDate AND d.ModifiedDate <= @EndDate
GROUP BY  
  c.ParentProductCategoryName
, c.ProductCategoryName
, d.ModifiedDate
ORDER BY
  c.ParentProductCategoryName
, c.ProductCategoryName

Copy and paste this code into your DateRange Dataset:

SELECT GETDATE() as Today
,DATEADD(d,-1,GETDATE()) AS Yesterday 
,DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) AS FirstDayOfThisWeek
,DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) AS LastDayOfThisWeek
,DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) AS FirstDayOfLastWeek
,DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) AS LastDayofLastWeek
,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AS FirstDayOfThisMonth
,DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) AS LastDayOfThisMonth
,DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AS FirstDayOfLastMonth
,DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) AS LastDayOfLastMonth
,DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0) AS FirstDayOfLastFullQuarter
,DATEADD(D, 0, DATEDIFF(D, 0, DATEADD(s,-1,DATEADD(q,1,DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0))))) AS LastDayOfLastFullQuarter
,DATEADD(q,DATEDIFF(q,0,GETDATE()),0) AS FirstDayOfThisQuarter
,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) AS FirstDayOfThisYear
,DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) AS LastDayOfThisYear
,DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) AS FirstDayOfLastYear
,DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) AS LastDayOfLastYear
Next Steps


Last Updated: 2014-12-16


get scripts

next tip button



About the author
MSSQLTips author Erin Cook Erin Youtzy has been dabbling in SQL at the workplace since she earned her BS degree in 2004. She is currently a DBA at UNM.

View all my tips





Comments For This Article




Thursday, April 30, 2020 - 8:46:05 AM - Randy Tunison Back To Top (85515)

Thank you.  This is genius.  


Wednesday, June 12, 2019 - 8:50:20 AM - David H. Back To Top (81431)

I love this! I am using it now in a report. My limited SQL knowledge is going to show on this: My only question is: when I use these date ranges it gives me monday to sunday when I need sunday to saturday. I have tried adjusting the numeric values and refreshing the fields, but it still gives me the first day of the week as a monday. 

Thanks again for this!

-David


Thursday, May 25, 2017 - 5:28:27 PM - dplres81715 Back To Top (56063)

Kerry Moseley,

So with some help, I figured out a way to do just what you're requesting.  Copy this code into a new Dataset (Visual Studio) and make it shared.  It will give you the Subscription report options like you want.  

As long as this is uploaded to your report servers shared dataset, you can link to it and have all the date Label/Value options.  

SELECT

DateTable.Value

,DateTable.Label

FROM

(

SELECT convert(datetime,convert(varchar(8),getdate(),112)) AS [Value], 'Today' AS [Label], 10 AS [SortOrder] UNION

SELECT convert(datetime,convert(varchar(8),DATEADD(d,-1,GETDATE()),112)) AS [Value], 'Yesterday' AS [Label], 20 AS [SortOrder] UNION

SELECT convert(datetime,convert(varchar(8),DATEADD(d,-2,GETDATE()),112)) AS [Value], '2 Days ago' AS [Label], 30 AS [SortOrder] UNION

SELECT convert(datetime,convert(varchar(8),DATEADD(d,-3,GETDATE()),112)) AS [Value], '3 Days ago' AS [Label], 40 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) AS [Value], 'ThisWeek Mon' AS [Label], 50 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),1) AS [Value], 'ThisWeek Tue' AS [Label], 51 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),2) AS [Value], 'ThisWeek Wed' AS [Label], 52 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),3) AS [Value], 'ThisWeek Thur' AS [Label], 53 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),4) AS [Value], 'ThisWeek Fri' AS [Label], 54 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),5) AS [Value], 'ThisWeek Sat' AS [Label], 55 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) AS [Value], 'ThisWeek Sun' AS [Label], 56 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) AS [Value], 'PreviousWeek Mon' AS [Label], 60 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),1) AS [Value], 'PreviousWeek Tue' AS [Label], 61 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),2) AS [Value], 'PreviousWeek Wed' AS [Label], 62 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),3) AS [Value], 'PreviousWeek Thur' AS [Label], 63 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4) AS [Value], 'PreviousWeek Fri' AS [Label], 64 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),5) AS [Value], 'PreviousWeek Sat' AS [Label], 65 AS [SortOrder] UNION

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) AS [Value], 'PreviousWeek Sun' AS [Label], 66 AS [SortOrder] UNION

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AS [Value], 'Month First Day Of This Month' AS [Label], 70 AS [SortOrder] UNION

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) AS [Value], 'Month Last Day Of This Month' AS [Label], 80 AS [SortOrder] UNION

SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AS [Value], 'Month-Prev First Day Of Last Month' AS [Label], 90 AS [SortOrder] UNION

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) AS [Value], 'Month-Prev Last Day Of Last Month' AS [Label], 100 AS [SortOrder] UNION

SELECT DATEADD(q,DATEDIFF(q,0,GETDATE()),0) AS [Value], 'Quarter FirstDayOfThisQuarter' AS [Label], 110 AS [SortOrder] UNION

SELECT DATEADD(D, 0, DATEDIFF(D, 0, DATEADD(s,-1,DATEADD(q,2,DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0))))) AS [Value], 'Quarter LastDayOfThisQuarter' AS [Label], 120 AS [SortOrder] UNION

SELECT DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0) AS [Value], 'Quarter-Prev FirstDayOfLastFullQuarter' AS [Label], 130 AS [SortOrder] UNION

SELECT DATEADD(D, 0, DATEDIFF(D, 0, DATEADD(s,-1,DATEADD(q,1,DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0))))) AS [Value], 'Quarter-Prev LastDayOfLastFullQuarter' AS [Label], 140 AS [SortOrder] UNION

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) AS [Value], 'Year FirstDayOfThisYear' AS [Label], 150 AS [SortOrder] UNION

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) AS [Value], 'Year LastDayOfThisYear' AS [Label], 160 AS [SortOrder] UNION

SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) AS [Value], 'Year-Prev FirstDayOfLastYear' AS [Label], 170 AS [SortOrder] UNION

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) AS [Value], 'Year-Prev LastDayOfLastYear' AS [Label], 180 AS [SortOrder]

) AS [DateTable]

ORDER BY

[SortOrder]


Monday, February 27, 2017 - 9:18:36 AM - Kerry Moseley Back To Top (46730)

 Hi,

 

Thanks for the above solution.  Was wondering though if rather than defaulting the date ranges, cis there a way of allowing the user to select the date range, i.e., create another parameter with "This week", "Last Week" etc and have the date paremeters filled in based on that selection.

I hope that makes sense...

 

Thanks

 


Wednesday, September 02, 2015 - 7:28:21 PM - Joseph Simpson Back To Top (38590)

This is a useful query. Thank you for sharing.


Tuesday, December 16, 2014 - 4:44:34 AM - narsimha Back To Top (35615)

 

Thanks for sharing this tip. having one data set for all date ranges is a good Idea. its saves time.



download





Recommended Reading

Resolving the Maximum Request Length Exceeded Exception in SQL Server Reporting Services

Multiple Row Grouping Levels in SSRS Report

SSRS Trending Report Example

Cross Tab Report in SSRS

SQL Server Reporting Services Reusable Code Blocks








get free sql tips
agree to terms


Learn more about SQL Server tools