SSRS Date Range Parameter
Summary
A method for providing a selection of pre-set date ranges plus the ability to enter a custom range of dates.
Until the SSRS improvements in 2016, this will require Nullable date parameter boxes to be used. (Why could not the date boxes be pre-filled with the date range selected? that would be a good solution. However exhaustive testing has proved this to be impossible. Two text boxes can be set to the selected dates in a date range but then they can never be edited. An editable date parameter can be set to show the date range selected and be editable only if the date range parameter has a default but after initial selection the dates shown will never be updated even on selection of a different date range.)
The DateRange data source
This is a table valued function. It has a single parameter (@DateRangeKey) that is set to 0 to return all rows – the full range of options. Once the choice has been made, set @DateRangeKey to the selected range’s ID and only that row is returned. This is then used to set defaults of SSRS parameters.
CREATE FUNCTION [parm].[fnDateRanges](@DateRangeKey int)
/*
Use to populate SSRS date ranges to provide a set of standard relative date ranges
(e.g. Last Day, Last Month).
Note that the DateRangeKey should not alter because this value will be saved in report
subscriptions and other preset range definitions.
PARAMETER
@DateRangeId : set to 0 to return all date ranges. Use this mode for populating a
dropdown list parameter named "DateRangeId", Label "Date Range".
HISTORY
21/7/2016 AI : Added "Week (starting Sun) to Yesterday".
03/02/2017 AI: Corrected calculation of @LastMonday.
22/02/2017 AI: + "4 Months to Yesterday".
27/03/2017 AI: Similar correction to calculation of @LastSunday.
*/
RETURNS @retDateRanges TABLE
(
-- Columns returned by the function
DateRangeKey int PRIMARY KEY NOT NULL,
DateRange varchar(50) NOT NULL,
StartDate date NULL,
EndDate date NULL,
StartDateKey int NULL,
EndDateKey int NULL
)
AS
BEGIN
DECLARE @KnownMonday date = '1900-01-01';
DECLARE @KnownSunday date = '1905-12-31';
DECLARE @Today date = CAST(GETDATE() AS date);
DECLARE @Yesterday date = DATEADD(d, -1, CAST(getdate() AS date));
DECLARE @LastMonday date = DATEADD(DAY, ( -1 * (DATEDIFF(DAY, @KnownMonday, @Today) % 7) ), @Today);
IF (@LastMonday = @Today) SELECT @LastMonday = DATEADD(DAY, -7, @LastMonday);
DECLARE @LastSunday date = DATEADD(DAY, ( -1 * (DATEDIFF(DAY, @KnownSunday, @Today) % 7) ), @Today);
IF (@LastSunday = @Today) SELECT @LastSunday = DATEADD(DAY, -7, @LastSunday);
DECLARE @LastDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(GETDATE())), GETDATE()) AS date) ;
DECLARE @FirstDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(@LastDayOfPrecedingMonth)-1), @LastDayOfPrecedingMonth) AS date) ;
DECLARE @FirstDayOfYesterdaysMonth date = CAST(DATEADD(dd,-(DAY(@Yesterday) - 1), @Yesterday) AS date);
WITH CTE AS (
SELECT * FROM
( VALUES
-- Daily values
( -1, 'Yesterday', @Yesterday, @Yesterday ),
( 1, 'Today', CAST(GETDATE() AS date), CAST(GETDATE() AS date) ),
( 2, 'Yesterday and Today', @Yesterday, @Today ),
( 7, '7 Days to Today', DATEADD(dd, -6, @Today), @Today),
( 14, '14 Days to Today', DATEADD(dd, -13, @Today), @Today),
( 21, '21 Days to Today', DATEADD(dd, -20, @Today), @Today),
( 28, '28 Days to Today', DATEADD(dd, -27, @Today), @Today),
( 30, '30 Days to Today', DATEADD(dd, -29, @Today), @Today),
( 60, '60 Days to Today', DATEADD(dd, -59, @Today), @Today),
( 90, '90 Days to Today', DATEADD(dd, -89, @Today), @Today),
(107, 'Last 7 Days', DATEADD(dd, -6, @Yesterday), @Yesterday ),
(110, 'Last 10 Days', DATEADD(dd, -9, @Yesterday), @Yesterday ),
(114, 'Last 14 Days', DATEADD(dd, -13, @Yesterday), @Yesterday ),
(121, 'Last 21 Days', DATEADD(dd, -20, @Yesterday), @Yesterday ),
(128, 'Last 28 Days', DATEADD(dd, -27, @Yesterday), @Yesterday ),
(130, 'Last 30 Days', DATEADD(dd, -29, @Yesterday), @Yesterday ),
(131, 'Last 31 Days', DATEADD(dd, -30, @Yesterday), @Yesterday ),
(156, 'Last 56 Days', DATEADD(dd, -55, @Yesterday), @Yesterday ),
(160, 'Last 60 Days', DATEADD(dd, -59, @Yesterday), @Yesterday ),
(190, 'Last 90 Days', DATEADD(dd, -89, @Yesterday), @Yesterday ),
(195, 'Last 141 Days (20 weeks + 1 day)', DATEADD(dd, -140, @Yesterday), @Yesterday ),
(196, 'Last 365/6 Days', DATEADD(year, -1, @Yesterday), @Yesterday ),
-- Weekly Values
(201, 'Week (starting Mon) to Yesterday', @LastMonday, @Yesterday),
(202, 'Week (starting Sun) to Yesterday', @LastSunday, @Yesterday),
(205, 'Last week', CASE @@datefirst
WHEN 1 THEN CAST(dateadd(day,-datepart(weekday,GETDATE())+1,dateadd(week,-1,GETDATE())) AS date)
WHEN 7 THEN CAST(dateadd(day,-datepart(weekday,GETDATE())+2,dateadd(week,-1,GETDATE())) AS date)
END, CASE @@datefirst WHEN 1 THEN CAST(dateadd(day,-datepart(weekday,GETDATE()),dateadd(week,0,GETDATE())) AS date)
WHEN 7 THEN CAST(dateadd(day,-datepart(weekday,GETDATE())+1,dateadd(week,0,GETDATE())) AS date) END),
(210, '4 Weeks (starting Mon) to Yesterday', DATEADD(week, -3, @LastMonday), @Yesterday),
(211, '4 Weeks (starting Sun) to Yesterday', DATEADD(week, -3, @LastSunday), @Yesterday),
-- Monthly Values
(301, 'Calendar Month to Yesterday', @FirstDayOfYesterdaysMonth, @Yesterday),
(302, 'Two Months to Yesterday', DATEADD(MM, -1, @FirstDayOfYesterdaysMonth), @Yesterday),
(303, 'Three Months to Yesterday', DATEADD(MM, -2, @FirstDayOfYesterdaysMonth), @Yesterday),
(304, 'Four Months to Yesterday', DATEADD(MM, -3, @FirstDayOfYesterdaysMonth), @Yesterday),
(306, 'Six Months to Yesterday', DATEADD(MM, -5, @FirstDayOfYesterdaysMonth), @Yesterday),
(311, 'Last month', @FirstDayOfPrecedingMonth, @LastDayOfPrecedingMonth ),
(312, '2 months ago', DATEADD(MM, -1, @FirstDayOfPrecedingMonth), DATEADD(DD, -1, DATEADD(MM, -0, @FirstDayOfPrecedingMonth)) ),
(313, '3 months ago', DATEADD(MM, -2, @FirstDayOfPrecedingMonth), DATEADD(DD, -1, DATEADD(MM, -1, @FirstDayOfPrecedingMonth)) ),
(314, '4 months ago', DATEADD(MM, -3, @FirstDayOfPrecedingMonth), DATEADD(DD, -1, DATEADD(MM, -2, @FirstDayOfPrecedingMonth)) ),
(316, '6 months ago', DATEADD(MM, -5, @FirstDayOfPrecedingMonth), DATEADD(DD, -1, DATEADD(MM, -4, @FirstDayOfPrecedingMonth)) ),
(322, '12 months ago', DATEADD(MM, -11, @FirstDayOfPrecedingMonth), DATEADD(DD, -1, DATEADD(MM, -10, @FirstDayOfPrecedingMonth)) ),
(999, 'Custom date range', CAST(NULL as date), CAST(NULL AS date) )
) DT (DateRangeKey, DisplayName, StartDate, EndDate)
)
INSERT @retDateRanges
SELECT
CTE.DateRangeKey,
CTE.DisplayName,
CTE.StartDate,
CTE.EndDate,
StartDateKey = CAST(CONVERT(VARCHAR, CTE.StartDate, 112) AS INT),
EndDateKey = CAST(CONVERT(VARCHAR, CTE.EndDate, 112) AS INT)
FROM
CTE
WHERE
(@DateRangeKey = 0)
OR
(@DateRangeKey = CTE.DateRangeKey)
RETURN
END
Shared Datasets
Create a shared dataset named DateRangeParameter
Query
SELECT DateRangeKey, DateRange, StartDate, EndDate, StartDateKey, EndDateKey FROM parm.fnDateRanges(@DateRangeKey)
Parameter: @DateRangeKey
Report Datasets
Create two report datasets, each based on the shared data source DateRangeParameter
- DateRangeParameter
set @DateRangeKey to 0 (fixed value). This is used as the available values source of the Date Range parameter option list. - DateRangeParameterSelected
set @DateRangeKey to the report parameter of the same name.
Report Parameters
Create the following parameters
- DateRangeKey
(“Date Range”, visible, Integer)
Use DateRangeParameter as source of Available Values. - StartDateKey, EndDateKey
(Integer, Allow Null, Hidden)
Use DateRangeParameterSelected as AvailableValues source (StartDateKey for both Value and Label field) and as Default Values source (StartDateKey for Value field). - ManualStartDate
(“Start Date”, Date/Time, allow Null, visible) - ManualEndDate
(“End Date”, Date/Time, allow Null, visible)
Dataset Parameters
Use the following expressions in the dataset date range parameters.
- =IIf(Parameters!DateRangeKey.Value = 999, Format(Parameters!ManualStartDate.Value, “yyyyMMdd”), Parameters!StartDateKey.Value )
- =IIf(Parameters!DateRangeKey.Value = 999, Format(Parameters!ManualEndDate.Value, “yyyyMMdd”), Parameters!EndDateKey.Value )
Revision to allow Fixed Start Date
This parameter arrangement doesn’t allow scheduling of a report with, say, a fixed start date and a moving end date (say, yesterday). Now although we don’t often want to have fixed start dates – leading to ever-increasing report size – sometimes it’s useful. Here’s a revision for the start parameter. It makes it slightly less intuitive in that the manual start date will be taken even if the Date Range is not ‘manually entered’.
=IIf(Not IsNothing(Parameters!ManualStartDate.Value), Format(Parameters!ManualStartDate.Value, "yyyyMMdd"), Parameters!StartDateKey.Value )
Date Range Limitation
Modification to limit the maximum period selectable in a report.
- Add report parameter MaxDateRangeDays (Integer, Hidden). Set the AvailableValues and Default values to specified values, e.g. 92.
This is difficult – maybe create a function.
Date Range Label
Expression source. Note that changes should be made if the Revision for Fixed Start Date is deployed.
=IIf(Parameters!DateRangeKey.Value = 999, Format(Parameters!ManualStartDate.Value, "dd/MM/yyyy") & " to " & Format(Parameters!ManualEndDate.Value, "dd/MM/yyyy"), Format(First(Fields!StartDate.Value, "DateRangeParameterSelected"), "dd/MM/yyyy") & " to " & Format(First(Fields!EndDate.Value, "DateRangeParameterSelected"), "dd/MM/yyyy"))