Nested iif issue in SSRS

Question

Compilation error for nested iif

=IIf(Fields!no_of_employees.Value = max(Fields!no_of_employees.Value,"DataSet1"),"Lime","Gold",
       IIf(Fields!no_of_employees.Value = min(Fields!no_of_employees.Value,"DataSet1"),"Red","Gold"
       ))

I was trying to implement nested iif.

Answer 1

If you look at your statement, you have passed 4 arguments to the first IIF

To write a normal IIF you would do something like

=IIF(
     ConditionA=True,
     TrueResult,
     FalseResult
    )

When writing a nested IIF you would do something like this

=IIF(
     ConditionA=True,
     TrueResult,
     IIF(
         ConditionB=True,
         TrueResult,
         FalseResult
         )
    )

To fix your code, remove the first instance of "Gold", .

I prefer to use SWITCH() instead of nested IIFs as I think it's easier to read. If you want to use SWITCH() then you can rewrite your expression as follows.

=SWITCH(
   Fields!no_of_employees.Value = max(Fields!no_of_employees.Value,"DataSet1"), "Lime",
   Fields!no_of_employees.Value = min(Fields!no_of_employees.Value,"DataSet1"), "Red",
   True, "Gold"
)

The final True just acts like an ELSE. You can read more about SWITCH() here .

https://learn.microsoft.com/en-us/sql/reporting-services/report-design/expression-examples-report-builder-and-ssrs?view=sql-server-ver16#DecisionFunctions