SSRS cascading parameter doesn't change value when upstream parameters change

Question

Post reply

SSRS cascading parameter doesn't change value when upstream parameters change

Bunter58

SSC Veteran

Points: 229
More actions
July 21, 2021 at 9:35 pm

#3912634

My report has a title that is based on the values in a couple of parameters. I'd like to add a feature to the report where the user can overtype the value for that title when the logic that sets it doesn't give quite the result they'd like.
I tried adding a parameter for that title for which I set a default based on the title returned in the dataset based on those other 2 parameters. That seemed to work great, I'd enter an account ID and press tab and the title parameter was filled in. I could overtype that and the amended text would appear as the title of the report.
However, if I enter a new account ID and press tab, the title remains as that I typed earlier instead of being refreshed as I'd expect.
I thought that setting Always Refresh on the Title parameter would do the trick but it doesn't.
This screen capture shows the starting position when I first click Preview in Visual Studio. The parameter I refer to as Title above is the one marked Cover Page Name.
I enter a value for Account ID and press tab and the Cover Page Name is filled in.
I overtyped it with the value I want to use instead and run the report where the revised text appears.
I then changed the Account ID and pressed tab but the Cover Page Name parameter is not updated.
Can anyone advise on what I've done wrong?
Nick

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80338 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Lisa Slater Nicholls Hall of Fame Points: 3439 More actions · Answer 2

I've seen this happen occasionally. Here is a post that explains one of the contributing issues: You have to be careful in your use of default values. You will find a lot of other such posts.

Additionally, sometimes it helps to mess around with the parameter sequence. For example, move the "month" and "account, site, retailer or MCC" parameters to *after* the cover page name, if they are unrelated.

Another contributing factor is: is the user's value (as typed into the free-form dependent parameter) still valid after the parent parameter change? If so, I think it is by design that it won't change, and there's a good chance it *is* still valid, given the free-form nature of the child parameter.

You're correct that the "Always refresh" doesn't do what you want/expect (and again, reading posts on the web will show you that you are definitely not alone!). Notice that the prompt for the choices says "Refresh DATA when the parameter changes." So, it's going to affect any QUERY that uses this parameter (assuming it works as advertised) -- but that's not the same as affecting any report EXPRESSION that happens to reference your parameter. Therefore, you *might* be able to change the behavior, if you are using a default expression, to create a new dataset with one row and one column that references the parameter parameter and returns the correct result, and then use this new dataset to provide the child parameter value, possibly as an "available value" rather than as a default. (In some instances, you can monkey around with using "available value" rather than "default", even *without* the extra dataset -- but I'm not able to test this or find an example right now.)

If you can't figure out what is causing the problem, you can work around it: Make "Cover Page Name" a "Default Cover Page Name", and basically make it readonly by making it a dropdown with one item. Then add an "Custom Cover Page Name" parameter as free-form text. Allow Blank (or null if you prefer) for the latter, default to space(0) (or null/Nothing) and make the usage of the former conditional on the latter being blank (or null) in your report design.

HTH,

LSN

This reply was modified 3 years, 2 months ago by Lisa Slater Nicholls.

Bunter58 SSC Veteran Points: 229 More actions · Answer 3

Thanks. I went with your workaround suggestion, in the end. Changing the order of the parameters didn't help.

Thinking about it, the default can't be refreshed all the time otherwise it would always be replacing your overtyped value. It would be nice if you could tie things together like "Always refresh when the value of x parameter changes" or something like that.