Tuesday, February 28, 2012

How to show a section in a SSRS report based on a parameter?

Today, I got a question how you can display a section in a SQL Server Reporting Services (SSRS) report based on a parameter value. In this blog I will describe how you can achieve this. I will make use of a general ledger card as example. This general card will show financial entry information. Depending on the parameter value: CostAnalysis it will show costcenter information.

Photo credit: Nisha A

Example of general ledger card without costcenter information:
Example of general ledger card with cost center information:

How to achieve this?
  • Add a extra detail line in the report for the costcenter columns.
  • Add the costcenter columns to this detail line.
  • Add a parameter CostAnalysis to your report. In this example I will make the posibility to choose to display the costcenter code or the costcenter code + costcenter description.
  • Select Available Values, Specify values and Add 3 labels
  • Specify default value 0. So costcenters will not be displayed by default.
  • Select the costcenter detail line of the left. Right mouse click and select row visibility.
  • Select Show or Hide based on expression. Add next expression:
    =IIF((Parameters!CostAnalytics.Value=0) True,False)
    This will display the costcenter line if parameter value = 1 OR 2
  • In the costcenter columns we need to add logic to display only the costcenter code or the costcenter code + description. Select the costcenter column and add next expression to this column: =Fields!Costcenter.Value + IIF (Parameters!CostAnalytics.Value = 2, " - " + Fields!CostcenterDescription.Value, "")
    This will print the costcenter code and only the description in case the CostAnalytics parameter value = 2
In this way you can do the same for currency information etc.
Enjoy it to build nice customer experience reports.