Sum IIF with multiple values in SSRS

Question

I am having an issue with my expression.

I am trying to write: if it finds these specific codes in the Dataset column, Sum them and return the amount. However, I am receiving #ERROR.

Any help with this expression is really appreciated.

=Sum(IIf(Sum(Fields!BillCode.Value) LIKE "EPOC, EPTX, STOR, PARK, ANTR", (Fields!Amount.Value), "$0.00"))

Anup Agrawal · Accepted Answer · 2016-01-08T21:14:23.333

You need an expression like this:

=SUM(IIF(UCASE(Fields!BillCode.Value) = "EPOC" 
         OR UCASE(Fields!BillCode.Value) = "EPTX" 
         OR UCASE(Fields!BillCode.Value) = "STOR" 
         OR UCASE(Fields!BillCode.Value) = "PARK" 
         OR UCASE(Fields!BillCode.Value) = "ANTR"
        , Fields!Amount.Value
        , 0 ))

OR If the BillCode value has other information besides the code then something like this

=SUM(IIF(UCASE(Fields!BillCode.Value) LIKE "*EPOC*" 
         OR UCASE(Fields!BillCode.Value) LIKE "*EPTX*" 
         OR UCASE(Fields!BillCode.Value) LIKE "*STOR*" 
         OR UCASE(Fields!BillCode.Value) LIKE "*PARK*" 
         OR UCASE(Fields!BillCode.Value) LIKE "*ANTR*"
        , Fields!Amount.Value
        , 0 ))

To format it to show as currency either set your textbox format to C0

OR Set the Expression =Format(SUM.....,"C0")

OR Right Click and go to textbox properties and set the format

Thank you. I believe my issue was that I had not separated each value exclusively in the expression. — Geo, Jan 08 '16 at 16:24
Note there is one Parenth missing at the end. Should be: =SUM(IIF(UCASE(Fields!BillCode.Value) = "EPOC" OR UCASE(Fields!BillCode.Value) = "EPTX" OR UCASE(Fields!BillCode.Value) = "STOR" OR UCASE(Fields!BillCode.Value) = "PARK" OR UCASE(Fields!BillCode.Value) = "ANTR" , Fields!Amount.Value , 0 )) — Geo, Jan 08 '16 at 19:59

score 0 · Answer 2 · answered Jan 07 '16 at 21:47

Try expression like this

=Sum(IIF(InStr(Fields!BillCode.Value, "EPOC") > 0
             OR InStr(Fields!CODE.Value, "EPTX") > 0
             OR InStr(Fields!CODE.Value, "STOR") > 0
             OR InStr(Fields!CODE.Value, "PARK") > 0
             OR InStr(Fields!CODE.Value, "ANTR") > 0,
         Fields!Amount.Value, 0))

Sum IIF with multiple values in SSRS

2 Answers2