question

NickRyanNZ-4337 avatar image
0 Votes"
NickRyanNZ-4337 asked Eshwar-8614 edited

SSRS parameter default based on another parameter

I have a report where the title was set based on 2 of my parameters. I would now like to set that title as a default and allow the user to overtype it.

So, parameter 1 is an ID of an Account, Site or Retailer. Parameter 2 is a pick list of Account, Site or Retailer so the code knows what the ID refers to (as some sites have the same number as accounts). A dataset takes those 2 values and returns EntityName.

I have now added a parameter called @EntityName which I have given a default value of EntityName from that dataset.

This appeared to work well for the first value I entered. The derived name appeared in the parameter box, I overtyped it with a new value which appeared correctly as the title of my report.

I then tried typing a new Account ID in but it didn't refresh the value in the EntityName parameter like I was expecting so it remained as the overtyped value.

I wondered if this was down to the Advanced Option of the parameter which I changed to Always Refresh. That didn't work so I tried setting the same thing for the ID column guessing that this might cause all data to be refreshed but that didn't work, either.

Is what I'm trying to do possible and if so, how?

These are my parameters before I enter anything.

116088-image.png

If I enter an account number and press tab, the Cover Page Name is filled in.

116096-image.png

I can then overtype it with the value I want to show and run the report.

116151-image.png

I then change the account number to something else but when I press tab, the value in the Cover Page Name is not refreshed for the new details.

116142-image.png

My definition of the parameter that has the prompt Cover Page Name has a default value set from a dataset that has the Account Site Retailer parameters as inputs and will set the Value field based on those which seems to happen when I enter the first Account value but doesn't happen subsequently.

Changing the refresh setting to Always on the Cover Page Name parameter doesn't make any difference.

It is difficult to make this a cascading parameter, I think, as the logic that sets the Cover Page Name is quite complex and is in a stored procedure.

Does that make it any clearer what I'm trying to achieve?


sql-server-reporting-services
image.png (12.2 KiB)
image.png (13.4 KiB)
image.png (13.9 KiB)
image.png (14.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Eshwar-8614 edited

Hi @NickRyanNZ-4337
What your report looks like, I think you may need to use cascading parameters. Could you use examples to show your current report status and the report status you expect?
Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Joyzhao-MSFT ,
It is not cascading parameter but the default value should change based on other parameter selection, I have a similar requirement.

Ex: If there is a parameter call YearType and values for that are Fiscal/Calendar, If I select Calendar then parameter StartDate default value should be 1/1/2021 and if I select Fiscal then it should may be 6/1/2021 so the default value should get auto refreshed as soon as I change YearType

May be it is not possible but can you confirm?
change-report-parameter-default-values-dynamically

Regards,
Eshwar

0 Votes 0 ·