I need a field to display different calculations based on the value of a parameter.
For example, MyBikeCompany has 3 classifications of bikes: Road, Mountain, and Hybrid. For their report, they want aggregates by region of Road, Mountain, Road + Mountain, Hybrid, and Total.
There is a line-item page that I want to switch based on the BikeType parameter or the IsHybrid flag (for better or worse, Road or Mountain bikes could have a hybrid designation, and the flag is the main way to identify it). The expression for the field would look something like this:
=SWITCH(Parameters!IsHybrid.Value = "Y", SUM(Fields!HybridFlag.Value), Parameters!BikeType.Value = "ALL", (SUM(Fields!RoadFlag.Value) + SUM(Fields!MountainFlag.Value) + SUM(Fields!HybridFlag.Value)), Parameter!BikeType.Value = "R", SUM(RoadFlag.Value), Parameter!BikeType.Value = "M", SUM(MountainFlag.Value))
The above will not work and renders an #Error in that field on the report. The same logic will, however, work for strings in the header (i.e. =Switch(Parameters!IsHybrid.Value = "Y", "Hybrid Totals by Region"...).
This would lead me to think that Switch can only operate on strings, but the documentation for SSRS expressions is so poor that I only found SWITCH on blogs. I am open to IF THEN ELSE logic if I can make it work, but nesting IIF in SSRS expressions is also difficult.
Does anyone know why this is so difficult in SSRS expressions for something that a general purpose programming language could do in 3-5 lines of code?