none
[Forum FAQ] How to use parameter to control the Expand/Collapse drill-down options in SSRS report?

    General discussion

  • In SQL Server Reporting Services (SSRS), drill-down is an action we can apply to any report item to hide and show other report items. They all are ways that we can organize and display data to help our users understand our report better. In this article, we are talking about how to use parameter to control the Expand/Collapse drill-down options in SSRS report.

    Consider that the report has a dataset (dsSales) with following fields: SalesTerritoryGroup, SalesTerritoryCountry, CalendarYear, SalesAmount.
    1. The report has the following group settings:
    Parent Group: SalesTerritoryGroup
     Child Group: SalesTerritoryCountry
      Child Group: CalendarYear
       Details: SalesAmount

    2. Add three parameters in the report:
    GroupExpand:
    Available Values: “Specify values”
    Label: Yes           Value: Yes
    Label: No            Value: No
    Default Values: “Specify values”
    Value: Yes
    CountryExpand:
    Available Values: “Specify values”
    Label: Yes           Value: =IIF(Parameters!GroupExpand.Value="No",Nothing,"Yes")
    Label: No            Value: No
    Default Values: “Specify values”
    Value: =IIF(Parameters!GroupExpand.Value="No","No","Yes")
    YearExpand:
    Available Values: “Specify values”
    Label: Yes          
    Value: =IIF(Parameters!GroupExpand.Value="No" or Parameters!CountryExpand.Value="No",Nothing,"Yes")
    Label: No            Value: No
    Default Values: “Specify values”
    Value: =IIF(Parameters!GroupExpand.Value="No" or Parameters!CountryExpand.Value="No","No","Yes")

    3. Right click SalesTerritoryCountry icon in the Row Groups dialog box, select Group Properties.

    4. Click Visibility in the left pane. Select “Show or hide based on an expression” and type with following expression:
    =IIF(Parameters!GroupExpand.Value="Yes", False, True)
    Select “Display can be toggled by this report item” option, and select “SalesTerritoryGroup” in the drop down list.

    5. Use the same method setting CalendarYear, (Details) drill-down with following expression:
    =IIF(Parameters!CountryExpand.Value="Yes", False, True)
    =IIF(Parameters!YearExpand.Value="Yes", False, True)

    6. Click SalesTerritoryGroup text box in the tablix. Select InitialToggleState property in the Properties dialog box, and type following expression:
    =IIF(Parameters!GroupExpand.Value="Yes", True, False)

    7. Use the same method setting SalesTerritoryCountry, CalendarYear text box with following expression:
    =IIF(Parameters!CountryExpand.Value="Yes", True, False)
    =IIF(Parameters!YearExpand.Value="Yes", True, False)

    After that, when we preview the report, we can use these three parameters to expand/collapse drill-down.

    Note:

    In our test, we may meet following issue. We can check the expression of InitialToggleState property to troubleshooting the issue.

    Applies to
    Reporting Services 2008
    Reporting Services 2008 R2
    Reporting Services 2012

    Monday, January 13, 2014 6:32 AM