Different "IF" statement for different outcomes in field - SSRS

Question

Good day,

I am creating a report where I want to assign a unique "IF"statement depending on the variables in a field.

For example, in a column, if a row displays "MCAD", I want it to calculate this formula in the column next to it:

iif(Fields!jhapostingdate.Value>"12/30/2016",(Sum(Fields!CurrentBalance.Value)-1803418)/6000000," ")

If the row displays "RGAD", I want it to calculate this formula in the column next to it:

iif(Fields!jhapostingdate.Value>"12/30/2016",(Sum(Fields!CurrentBalance.Value)-6977142)/10000000," ")

And so on and so forth....

I understand the following expression is incorrect, but it might make my intentions clear to you all:

=iif(Fields!OfficerCode.Value="mcad",(iif(Fields!jhapostingdate.Value>"12/30/2016",(Sum(Fields!CurrentBalance.Value)-1803418)/6000000," "),"")

,iif(Fields!OfficerCode.Value="rgad",(iif(Fields!jhapostingdate.Value>"12/30/2016",(Sum(Fields!CurrentBalance.Value)-6977142)/10000000," "),"")

,iif(Fields!OfficerCode.Value="srf",(iif(Fields!jhapostingdate.Value>"12/30/2016",(Sum(Fields!CurrentBalance.Value)-3427940)/3000000," "),"")

,iif(Fields!OfficerCode.Value="maf",(iif(Fields!jhapostingdate.Value>"12/30/2016",(Sum(Fields!CurrentBalance.Value)-6977142)/10000000," "),"")

Is there a specific function or expression for this?

Any input is appreciated!

Thank you for your response, I have never used the switch function however it does make a lot of sense after some research. Unfortunately I am receiving the following "The Value expression for the textrun ‘Textbox64.Paragraphs[0].TextRuns[0]’ contains an error: [BC30198] ')' expected" — MarioS, Jan 23 '17 at 21:12
That error indicates that you are missing a closing parenthesis. Al's answer looks OK so you might compare what you have with his answer. You may have missed the last line with the one closing parenthesis. — Hannover Fist, Jan 23 '17 at 22:17

alejandro zuleta · Accepted Answer · 2017-01-23T22:21:18.820

You can simplify it by using Switch:

=Switch(
Fields!OfficerCode.Value="mcad",
  iif(Fields!jhapostingdate.Value>DateSerial(2016,12,30),(Sum(Fields!CurrentBalance.Value)-1803418)/6000000," "),
Fields!OfficerCode.Value="rgad",
  iif(Fields!jhapostingdate.Value>DateSerial(2016,12,30),(Sum(Fields!CurrentBalance.Value)-6977142)/10000000," "),
Fields!OfficerCode.Value="srf",
  iif(Fields!jhapostingdate.Value>DateSerial(2016,12,30),(Sum(Fields!CurrentBalance.Value)-3427940)/3000000," "),
Fields!OfficerCode.Value="maf",
  iif(Fields!jhapostingdate.Value>DateSerial(2016,12,30),(Sum(Fields!CurrentBalance.Value)-6977142)/10000000," "),
true,""
)

Also might need to use DateSerial function to create a hardcoded date:

DateSerial(2016,12,30) will produce 12/30/2016 date while "12/30/2016" is just a string. You can also use CDate("2016-12-30").

Let me know if this helps.

Different "IF" statement for different outcomes in field - SSRS

1 Answers1