Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Nested iif with multiple conditions SSRS

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!

like image 272
jenhil34 Avatar asked Mar 01 '13 20:03

jenhil34


1 Answers

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"
     )
)
like image 143
Tom Jenkin Avatar answered Nov 02 '22 21:11

Tom Jenkin