SSRS parameter default based on another parameter

Nick Ryan (NZ) 121 Reputation points
2021-07-19T02:57:20.24+00:00

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
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,793 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Joyzhao-MSFT 15,566 Reputation points
    2021-07-20T02:50:55.053+00:00

    Hi @Nick Ryan (NZ) ，
    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.

  2. Mulla Syed Fahima 0 Reputation points
    2024-04-17T13:13:27.6666667+00:00

    Hi @Joyzhao-MSFT ,I have faced the same issue , After using the cascading parameter as well. says, first parameter is student Id and second one is subject Id . now when I select Student Id ,based on cascading the second parameter will populate only the list of subject Id's comes under that particular student . I have created 2 different datasets for 2 parameters and second dataset(subject Id) will have student Id filtered in that dataset property. Now When ever I filter student Id , I want Subject Id to be select by default based on highest marks that got for particular subject Id and later in that dropdown they can see another subject Id's as well. Is there any possible way??

    0 comments