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
Comments