Reporting Services Switch Function

Reporting Services Switch Function

Recently a client requested to create a Reporting Services expression that emulated the CASE WHEN statement from T-SQL. The expression would use the selected parameter value to dynamically set the Go to report value of an Action under the properties of a textbox. In other words, if a user chose a parameter value of Report1 the go to report value would be set to Report1, if the user chose Report2 value would be set to Report2, so on and so forth.

I looked at several varying expression methods to solve this problem including the IIF method, but I eventually settled for the SWITCH function because it offered the most flexibility. One small limitation was that the value was only updated when the report was refreshed. If a user changed the value of the parameter, he or she would be required to click the view report button to refresh the value. So how do you use the switch function? To specify the value for this example drop a text box on your report, right-click it and choose Text Box Properties. The following dialogue box will appear:

Choose Action from the list of available choices in the right pane. Ensure that the Go to report radio button is selected. Then click the expressions button next to the Specify a report drop down list. The expression dialogue window will appear.

In the Set expression for ReportName textbox you would type an expression similar to the following:

=SWITCH ( Parameters!WhichReport.Value="Report2", "Report2", Parameters!WhichReport.Value="Report3", "Report3", Parameters!WhichReport.Value="Report4", "Report4" )

The SWITCH function should be used when you have three or more conditions to test. When evaluated to true the function returns the first value in the series of available conditions.

I welcome any comments, criticisms or suggestions regarding this post. How would you have solved this problem? I have attached a small report project that simulates my approach. As always if you have any questions regarding this post please feel free to email me at pleblanc@pragmaticworks.com.

Talk to you soon,

Patrick LeBlanc, founder www.SQLLunch.com and www.TSQLScripts.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.