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