camelCaseWarrior
camelCaseWarrior

Reputation: 369

Reporting services cascading parameter not working

I want the report to pick default date time values based on a location parameter the user picks, but the problem is that the date time parameters don't update when the user picks a different location. I made sure to put the location parameter above the date time parameters but it is still not functioning as I expect, what am I doing wrong here?

Upvotes: 1

Views: 10586

Answers (5)

Volvox
Volvox

Reputation: 621

I've been having the same issue, and realized it was a simple mistake causing this issue. Posting here for OP and anyone else searching for this issue.

You need to ensure that all parameters, in SSRS, are set to the correct value field, not the display field. You can replicate the cascading parameter issue that many have identified by simply selecting a "Name" field in the parameter default values instead of the "Value" field requested. This will populate the drop down correctly but nothing will be selected, and the cascade will stop. It may then appear as though the cascading parameters are not refreshing because of the Closed be Design issue.

  1. In SSRS In Report Data tab, right click parameter and select Parameter Properties.
  2. Select Default Values.
  3. Ensure that Value Field is set to the correct value for your sproc, not the value that you present to report users.

Upvotes: -1

Edward
Edward

Reputation: 8616

Unfortunately this is "by design". Microsoft Connect - SSRS - default not refreshed for cascading parameters.

Posted by Microsoft in 2007:
"As described, this is not a bug. We do not re-evaluate the default value for a subsequent parameter unless the selected value is no longer in the valid values list. We do not know whether the current value was specifically requested by the user or it is there because of the default. You could make a case to have control over this behavior through some sort of property but it is currently working as designed."

I think this is a bug in SSRS. But Microsoft have closed the issue. Please log onto connect and vote this to be fixed if it is affecting you too

Microsoft Connect - Your feedback improving Microsoft products.

Workaround / hack is here: Boyan Penev - SSRS Cascading Parameters Refresh: Solved.

However the hack only works if the dependent parameter has a LIST of valid values, and other users report that it only works for the first dependent parameter.

If the dependent parameter is free-form entry or a date, the hack cannot be used at all.

Upvotes: 3

Karyn Thomas
Karyn Thomas

Reputation: 9

I have found that if you use a stored procedure to render the child parameter, by passing in the parent selection, the dataset will get refreshed

Upvotes: 0

unforgiven1987
unforgiven1987

Reputation: 464

Unfortunately, SSRS does not refresh cascading parameters once they have been set. Other than reloading the report, the only way that I am aware of is to create a custom UI using the ReportViewerDialog.

Upvotes: 0

mr.theTrain
mr.theTrain

Reputation: 891

Consider putting your datetime values in a dataset with logic based on the location parameter

SELECT CASE @Location WHEN 'Paris' THEN GETDATE()+10
                      WHEN 'Berlin' THEN GETDATE()+11
                      WHEN 'New York' THEN GETDATE()+8
       ELSE GETDATE() END as DefaultDate

Then set the date parameter default value to this dataset.

Upvotes: 1

Related Questions