CODEWITHSUNDEEP
dynamicreporting-servicesparameters
user2701207
user2701207

Reputation: 13

HOW can I have a SSRS parameter display or not display based on a prior parameter (dynamically)?

My report has a Parameter with 5 choices. I was thinking that, if a specific choice was made, that another Parameter would prompt the user to answer an additional question/setting. Can this be done in SSRS 2008? If not, I will just hard code 1 way or the other.

Upvotes: 1

Views: 2126

Answers (1)

djangojazz
djangojazz

Reputation: 13242

Sure it can. You basically bind a dataset with a predicate to the first variable.

UPDATED

  1. Say I declare parameter 'choices'.

  2. I create a dataset also called Choices that is just a simple two row return:

    Select 'ChoiceA' as Choice
union
Select 'ChoiceB'

  3. I go back to parameter in step 1 and choose on the left pane 'Available Values' and say 'Get values from a query' choose the dataset I called 'Choices' and then choose 'Choice' as the value and the label. Now when you preview the report the parameter has a drop down with two choices. But you wanted to know if it could affect more, it can.

  4. Add another dataset called 'SubChoices' and make it's dataset be:

     Select * 
 from (
  Select 'ChoiceA' as Choice, 'Yes' as SubChoice
  Union
  Select 'ChoiceA', 'No'
  Union
  Select 'ChoiceB', NULL
) as x 
where Choice = @Choices
order by SubChoice desc

    Notice I am declaring a dataset yet referencing a variable I had done above. I just created a dependency on this dataset for the first one. UPDATE: I added an order by clause and changed the inside nested select to be more tailored to your specific case.

  5. Now create a parameter called 'SubChoices' and make it's 'Available Values' from 'Get values from a query' and use 'SubChoices' as the dataset and the subchoice as the value and label. UPDATE: Ensure you set it to 'Allow null value' under general. This time set the 'Default Values' to ALSO be the query and Subchoice. What this will do is take the FIRST ONE it gets and pop that one in as the default. Since you only want choices for certain first choices if I do not want values for ChoiceB I set a 'NULL' in the dataset meaning nothing. So if a user choices ChoiceA they get two options and YES should default, if they choose Choice B they get a null, meaning nothing.

  6. Now test the report, you should havea first parameter with a drop down for two values, 'ChoiceA' or 'ChoiceB'. You have another parameter next to it grayed out. Once you fill out the first one, the second one will display values that only display values associated from the first. Due to the fact of my having a where clause the second parameter is DEPENDENT on the first and thus will not be able to be used till the first one is done and also will only display the values related to the first value.

HTH, dependent values are a powerful part of SSRS.

Upvotes: 1

Related Questions