SSRS 2017 Cascading parameters with dates

0
votes

I have a parameter called Year and you can choose one year at a time. I have a date/time parameter with a calendar called start date and another one called end date.

I would like it to work such that if I pick the year to be 2017 it will show the calendar start and end dates for 2017.

Since my start date and end date parameters are not defaulted from a dataset I am not sure how to control that.

Any help will be appreciated.

2
sql-servertsqlreporting-servicesparameters
I assume you mean you want to default the start and end date parameters and that the user can then modify them? i.e. the Year parameter is a quick way to set start and end dates? - Dale K
Yeaj if I pick year 2017 I want the calendar start date and end date show months for 2017 . I am using SQL server database - Liliana Torres
You can tag the database version, and you can try the answers below and ask if you if you need more assistance. - Dale K

2 Answers

0
votes

You can do it as an expression, but as its easier in SQL you can just create a second dataset with the following:

select convert(date, '1 jan ' + convert(varchar(4), @Year)), dateadd(day, -1, dateadd(year, 1, convert(date, '1 jan ' + convert(varchar(4), @Year))))
0
votes

I used a recursive CTE common table expression that references itself to solve this. Then you can set the Dataset of the start_date and end_date parameters.

Dataset for the default values of the start and end date parameters

DECLARE @year AS INT
SET @year = 2018

SELECT [start_date] = DATEFROMPARTS(@year, 1, 1), [end_date] = DATEFROMPARTS(@year, 12, 31)

Dataset for the available values of the start and end date parameters

DECLARE @year AS INT
SET @year = 2018

;WITH
source_data_dates
AS
(
    SELECT 
          [date_start] = DATEFROMPARTS(@year, 1, 1)
        , [date_end] = DATEFROMPARTS(@year, 12, 31) 
)
,
year_date_list([rn], [date_value]) 
AS
(
    SELECT 
          [rn] = 1
        , [date_value] = CAST([date_start] AS DATETIME) 
    FROM 
        source_data_dates
    UNION ALL
    SELECT 
          [rn] = [rn] + 1
        , [date_start] = CAST(DATEADD(DAY, [rn], [date_start]) AS DATETIME) 
    FROM 
          year_date_list
        , source_data_dates 
    WHERE 
        [rn] <= DATEDIFF(DAY, [date_start], [date_end])
)
SELECT 
      [rn]
    , [date_value]
FROM 
    year_date_list
OPTION (MAXRECURSION 0)

Results:

screenshot