SSRS: IIF statement with grouped and summarized data
Updated: Sep 6, 2021
Data source: sample data
Create a dataset and insert a table
Group the data by some category.
As you can see from the attached picture, the data is grouped and each group contains summary data, totals.
Add a calculated field price_description to create a logic, when everything that has price higher then 1 will be expensive and if the condition is false, such a product will be described as cheap.
Click on the fx button to build an expression
Add the new calculated field to the table
IIF statement works fine, but there is an error in each line that contains summarized data.
To get rid of this error, create the calculated field with IIF statement first and do summarizing afterwards.
I removed the totals from the table and the group as well
Add back the grouping and Total by the Price. Preview the report.
Errors are gone.
Nested IIF statement
Using a nested IIF statement to add one more condition. 3 conditions in total.
If the price is less or equal to 1 then CHEAP, If is greater than 1 and less than or equal to 2 then EXPENSIVE. Everything over 2 is LUXURY.
Add a calculated field, give it a name, click fx to build an expression.
I removed the summarization to avoid the errors. And sure enough there are luxury products on the list too.
The same as IIF this is case sensitive, mind the spelling.
You can use Switch function with TRUE. If the previous two conditions are false, the last one will be true. Or you can explicitly write all 3 conditions.
Add Total after the calculated fields are done to avoid the errors