Is there a way, in SSRS, to have a parameter called "Pay Period" that allows only selection of a specific date range? For example, only allowing 09/19/2016 through 09/25/2016, and not allowing 09/20/2016 through 09/26/2016. Any Monday - Sunday combination is fine, but otherwise, no go.
I was thinking of a drop-down list that offered these ranges (as opposed to two different parameters) but am unsure of how to make that work.
I would use a single parameter for the start date and calculate the end date as 6 days past this (if you need it).
For the parameter, I would create a field for the label while using the single start date field for the data.
Parameter Query:
DECLARE @START_DATE DATE = '01/01/2016'
DECLARE @END_DATE DATE = '10/31/2016'
;WITH GETDATES AS
(
SELECT @START_DATE AS THEDATE
UNION ALL
SELECT DATEADD(DAY, 1, THEDATE) FROM GETDATES
WHERE THEDATE < @END_DATE
)
SELECT THEDATE, CONVERT(VARCHAR(10), THEDATE, 101) + ' - ' + CONVERT(VARCHAR(10), DATEADD(D, 6, THEDATE), 101) AS PAYPERIOD
FROM GETDATES
WHERE DATEPART(WEEKDAY, THEDATE) = 2
OPTION (maxrecursion 0)
This would let the user see the date range but only pass the starting date to the report to use as needed.
DATE PAYPERIOD
2016-01-04 01/04/2016 - 01/10/2016
2016-01-11 01/11/2016 - 01/17/2016
2016-01-18 01/18/2016 - 01/24/2016