Search code examples
reporting-servicesssrs-2008

Multi Dropdown parameters and their available values in SSRS reporting

I am looking for help in SSRS reporting. Consider this scenario -- First dropdown has Asia, Europe. if Asia is selected - second dropdown should show China, India, Japan. If Europe is selected, second dropdown should show Italy and Spain.

So the question: In the second dropdown's Available values:

1st available value and Label is like

=IIF(Parameters!Continent.Value = "Asia", "China", "Italy")

2nd available value and label is like 

  =IIF(Parameters!Continent.Value = "Asia", "India", "Spain")

3rd available value and label is like

=IIF(Parameters!Continent.Value = "Asia", "Japan", "*******")

What I am looking for is to not show the the third value when Europe is selected. How can I set it to be not visible if Europe is selected?

I hope this scenario makes sense. Thanks in advance!!

Solution

  • You want NOTHING.

    =IIF(Parameters!Continent.Value = "Asia", "Japan", NOTHING)

    Nothing is how to use a blank (NULL) value in SSRS.

    In theory, you should have 2 tables and use parameters. A table for your continents with Continent and ContinentID Another for your countries with a Continent ID field that relate to the Continent in the above table.

    Then you have the continent selection based on your first table: Select * from Continent

    And your country selection based on your first parameter:

    SELECT * 
FROM Country 
WHERE ContinentID = @Continent

    This way, only countries that are on the selected continent show up in the country selection.