• Marek Vavrovic

SSRS: IIF statement with grouped and summarized data

Updated: Sep 6

Switch Function

Nested IIF statement

IIF statement


Data source: sample data

Dataset:

SELECT

[product_brand]

,[product_name]

,[product_retail_price]

,[product_cost]

,[product_weight]

FROM [Products]

Step 1

Create a dataset and insert a table

Group the data by some category.

Step 2

Add Total

As you can see from the attached picture, the data is grouped and each group contains summary data, totals.

Step 3

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.

Step 4

Click on the fx button to build an expression

=IIF(Fields!product_retail_price.Value>1,"Expensive","Cheap")

Step 5

Add the new calculated field to the table

Step 6

Preview Report

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.

Step 7

I removed the totals from the table and the group as well

Step 8

Add back the grouping and Total by the Price. Preview the report.

Errors are gone.

2.

Nested IIF statement


Using a nested IIF statement to add one more condition. 3 conditions in total.


=IIF(Fields!product_retail_price.Value<=1,"Cheap",IIF(Fields!product_retail_price.Value<=2,"Expensive","Luxury"))


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.


Step 1

Add a calculated field, give it a name, click fx to build an expression.

Step 2

Report preview

I removed the summarization to avoid the errors. And sure enough there are luxury products on the list too.

3.

Switch Function


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.

=Switch(Fields!price_ex.Value="Cheap","<= 1$",

Fields!price_ex.Value="Expensive","<= 2$",

True,"> 2$")

Report preview

Add Total after the calculated fields are done to avoid the errors



3 views0 comments

Recent Posts

See All