By: Erin Cook | 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:
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
3. Right-Click on Parameters and click "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
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
6. Right-Click on Parameters and click "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
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
9. Preview your report and the parameterized dates should appear, pre-filled:
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
- Please see Create a Simple Reporting Services Report with the Wizard tip.
- Check out all of the SQL Server Business Intelligence tips.
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-12-16
