SSRS: Two cascading date parameters do not refresh

0
votes

I have an SSRS 2014 report with three parameters: @Period (text), @FromDate (date), and @ToDate (date). They work together by first selecting a value from the Period dropdown list (January, February, March etc...). Depending on what you period you choose, the @FromDate and @ToDate parameters change accordingly to reflect your choice. This works well, but the problem arises when you select a new period after having already selected one, as the date parameters do not refresh.

I have been looking at some suggestions and workarounds, but I have yet to find one that deals with two dependent date parameters. Any suggestions?

3
sql-serverreporting-servicesparametersreport
Are you using the parent (@Period) parameter for populating default or available values? Note the UI will not change but the @FromDate and @ToDate parameters are set correctly at runtime. – alejandro zuleta
@alejandrozuleta The Period parameter is populated only by available values, while FromDate and ToDate are populated only by default values. – Simon
In that case you can set both date parameter to be Internal, so your parameters will be populated only from the period parameter not from UI. But if you have to let user selects dates you will have to create two additional parameters to be Internal and populate them using an expression to determine if use the default values from @Period parameter or the values from the visible date parameters. – alejandro zuleta

3 Answers

0
votes

The Date parameters once generated cannot be changed. It does not have the cascading facility and according to Microsoft, it's by design (it's how they wanted it to behave):

Follow this link please

0
votes

As already stated, parameters which have no available values/default values from a cascading query won't refresh their default values.

Workaround: Create a one row dataset which calculates your DateFrom and DateTo dates, each in a separate column and depending on your @Period parameter, and assign the dataset to the available and default values of both your parameters. Disadvantage: you won't be able to edit the values when running the report, since the fields are populated by a dataset.

0
votes

This is working as intended, Microsoft doesn't want cascading date parameters to refresh if you change the parameter they are dependent on. However it's possible to get around it.

It requires two datasets for the two data parameters which return one row with the required data dependent on the @Period parameter e.g.

DECLARE @Dates as TABLE ([Period] INT, [Date] SMALLDATETIME) 

INSERT INTO @Dates VALUES
(1,DATEADD(s, 86340, DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) ) 
,(2,DATEADD(DAY , 7-DATEPART(WEEKDAY,GETDATE()),DATEADD(MINUTE,- 1,DATEADD(DAY,0,DATEADD(day,DATEDIFF(day,0,GETDATE())+1,0)))) )
,(3,DATEADD(MINUTE,- 1,DATEADD(DAY,0,DATEADD(day,DATEDIFF(day,0,GETDATE()),0)))) 

SELECT 
    [Period]
    ,[Date]
FROM
    @Dates
WHERE 
    [Period] = @Period

Set the available values of the @Period parameter too match and set the default value of the data parameters to their matching datasets.

Now when you change the @Period parameter it forces the date datasets to be be rerun and your date parameters will default to the new result.