Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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:

image

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.

image

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.

Download file:  SwitchExample

Talk to you soon,

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

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

Comments

Posted by Anonymous on 20 February 2010

Pingback from  Ricciolosalon: An Extraordinary Beauty Salon La Verne | A2K44 Health | Business Beauty Wisdom

Posted by jonathan.cohen on 1 March 2011

It seems that switch cannot be used in aggregates - SUM,  etc. Isthere any way around that?

Posted by anbilchelambi.t on 21 February 2014

I have same issue while returning date value.

Am using SWITCH for my date field but it is taking only the first value...

Leave a Comment

Please register or log in to leave a comment.