Nested iif with multiple conditions SSRS

Question

I need to write a formula for a SSRS report. I am not for sure about the exact syntax, but it I am thinking it should be a nested iif but with multiple criteria, checking the value of the chart and division fields. At the end of the day if chart=110300 and division=100 then "Intercompany AP - USA" or if chart=110300 and division=200 then "Intercompany AP - RUS" other wise, then just display the chartname. Something like this but actually written correctly.

iif Fields!chart.Value="110300" and Fields!division.Value="100" then
Fields!chartname.Value="Intercompany AP - USA" if Fields!chart.Value="110300"
and Fields!division.Value="200" then Fields!chartname.Value=
"Intercompany AP - RUS" else Fields!chartname.Value

I greatly appreciate any help on this!

score 24 · Accepted Answer · answered Mar 01 '13 at 23:40

You pretty much solved this one yourself! To write this in T-SQL you right click the Chart Name and change its value to the following expression:

IIF(Fields!chart.Value="110300" AND Fields!division.Value="100","Intercompany AP - USA",IIF(Fields!chart.Value="110300" AND Fields!division.Value="200","Intercompany AP - RUS","Default Chart Name")

See here for explanation on how the IIF function works

From the link you can see that it takes the following format, where commas are used instead of "Then" or "Else":

IIF ( boolean_expression, true_value, false_value )

So to breakdown the expression:

IIF(Fields!chart.Value="110300" AND Fields!division.Value="100",
    "Intercompany AP - USA",
     IIF(Fields!chart.Value="110300" AND Fields!division.Value="200",
        "Intercompany AP - RUS",
        "Default Chart Name"
     )
)

Thank you so much for your heko @tom jenkin!! – jenhil34 Mar 04 '13 at 14:37 — jenhil34, Mar 04 '13 at 14:37

Nested iif with multiple conditions SSRS

1 Answers1