Preset Date Parameters
*The Request:
- I have built two reports, one with a Start Date and an End Date, and another with preset ranges (Last 7 Days, Last 30 Days). I am trying to see if there is a way to have a single combined report with maybe the following three fields:
1. Date Options - Last 7 Days, Last 30 Days, Custom
2. Start Date - Auto-fill if Last 7 or Last 30 selected; Leave-alone if Custom
3. End Date - Auto-fill if Last 7 or Last 30 selected; Leave-alone if Custom
Can anyone help me consolidate the report, please?
*The Report:
- SSRS report returning data for a specified date range. The date range is determined from two parameters: StartDate and EndDate.
*The Scenario:
- 95% of the time, the report is run for either the past 7 days or the past 30 days.
The Solution:
Param1:
Select parameter visibility: Visible,
Data type: Integer,
Available Values - Specify values:
Label Value
Custom 0
Last 7 Days 1
Last 30 Days 2
Param2 (Start Date):
Select parameter visibility: Visible,
Data type: Date/Time,
Default Values - Get values from query:
Dataset - dataset1
Value field - StartDate
Param3 (End Date):
Select parameter visibility: Visible,
Data type: Date/Time,
Default Values - Get values from query:
Dataset - dataset1
Value field - EndDate
dataset1:
Query -
SELECT CASE WHEN @Param1 = 1 THEN DATEADD(d,-7,GETDATE()) WHEN @Param1 = 2 THEN DATEADD(d,-30,GETDATE()) ELSE NULL END AS StartDate, CASE WHEN @Param1 = 0 THEN NULL ELSE GETDATE() END AS EndDate
With this, when Custom is selected (value 0) for Param1, the query returns no start or end date so the user must select a date for both. If "Last 7 Days" (value 1) is selected then StartDate is a week ago today. If "Last 30 Days" is selected then StartDate is 30 days ago today. In either case, EndDate is today.
I have tested this locally and it works. You must note that I am using default value to set the 2 date parameters which means if I change Param1 value, the 2 date parameters are unaffected. They will stay with whatever values have been set. You will need to refresh the report to remove the currently assigned values. The only exception is if you first select Custom, then switch to one of the others without setting values for the 2 date fields. Since there is no values set, switching Param1 to another selection will populate the default dates.
***Related Info
***Forum Post