Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Add a Date Range Dataset in SQL Server Reporting Services


By:   |   Last Updated: 2014-12-16   |   Comments (4)   |   Related Tips: > 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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

 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

This is a useful query. Thank you for sharing.


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

 

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


Learn more about SQL Server tools