July 17, 2018 at 1:04 pm
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.
=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"...).
July 17, 2018 at 2:26 pm
Prometheus112 - Tuesday, July 17, 2018 1:04 PMHi All,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?
Not sure where my post went...so I'll try again.
I would guess that when it worked in the header you had a different data set.
The error is usually due to nulls in the data set or when using aggregates/math when the values are seen as character data types instead of numeric data types.
Sue
July 17, 2018 at 2:48 pm
Sue_H - Tuesday, July 17, 2018 2:26 PMPrometheus112 - Tuesday, July 17, 2018 1:04 PMHi All,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?Not sure where my post went...so I'll try again.
I would guess that when it worked in the header you had a different data set.
The error is usually due to nulls in the data set or when using aggregates/math when the values are seen as character data types instead of numeric data types.Sue
Hi Sue, thanks for the reply, but I am checking against the results of the same set - it works for the conditional header text, but not for the aggregate functions. To check for nulls, I run the SPROC with the same parameters directly in SQL Server and check the result set for NULLs (stuff into a Temp Table and then look for nulls).
July 17, 2018 at 2:57 pm
Prometheus112 - Tuesday, July 17, 2018 2:48 PMSue_H - Tuesday, July 17, 2018 2:26 PMPrometheus112 - Tuesday, July 17, 2018 1:04 PMHi All,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?Not sure where my post went...so I'll try again.
I would guess that when it worked in the header you had a different data set.
The error is usually due to nulls in the data set or when using aggregates/math when the values are seen as character data types instead of numeric data types.Sue
Hi Sue, thanks for the reply, but I am checking against the results of the same set - it works for the conditional header text, but not for the aggregate functions. To check for nulls, I run the SPROC with the same parameters directly in SQL Server and check the result set for NULLs (stuff into a Temp Table and then look for nulls).
So out of the two then data types is left. But if that's also not the issue, I don't have any other ideas. I still suspect one of the two though.
Sue
July 17, 2018 at 2:58 pm
All, I fixed the issue - here is something to watch out for when you do complex return values in the =SWITCH statement:
Basically, if you are putting things in an aggregate function be sure to check that you are operating on numeric fields and that you specify Fields!myField.Value
In my actual code - not the example above - I missed .Value on the end of one of my sum() expressions. This led it to throw warnings (unfortunately, it did not throw errors, which I would have seen) about trying to aggregate a non-numeric data type. I went back through the table and ensured all of the columns were data type int but then I located the fact that I did not call out .Value on one of my aggregates, which lead it to fail.
Hope this helps with someone else struggling with a complex Switch. All examples on the web are super simple substitutions.
July 17, 2018 at 3:13 pm
Prometheus112 - Tuesday, July 17, 2018 2:58 PMAll, I fixed the issue - here is something to watch out for when you do complex return values in the =SWITCH statement:Basically, if you are putting things in an aggregate function be sure to check that you are operating on numeric fields and that you specify Fields!myField.Value
In my actual code - not the example above - I missed .Value on the end of one of my sum() expressions. This led it to throw warnings (unfortunately, it did not throw errors, which I would have seen) about trying to aggregate a non-numeric data type. I went back through the table and ensured all of the columns were data type int but then I located the fact that I did not call out .Value on one of my aggregates, which lead it to fail.Hope this helps with someone else struggling with a complex Switch. All examples on the web are super simple substitutions.
Pretty much what I said in my first reply:
or when using aggregates/math when the values are seen as character data types instead of numeric data types.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy