reporting-servicesssrs-tablix

Switching IIF statements - SSRS report

I am trying to switch two conditions in an expression in SSRS - the following expression is valid and is currently working. But I want the record count portion to be evaluated first.

=IIF(max(Fields!Batch.Value) < CStr(Format(TODAY(),"yyyymmdd")),
"Overnight Processing Issue.  Please use prior day report results.",
IIF(CountDistinct(Fields!ClaimNumber.Value) = 0, "There are no claims with Open Claims without Financial issues.", "")
)

But when I try to switch the order of these statements:

=IIF((CountDistinct(Fields!ClaimNumber.Value) = 0, "There are no claims with Open Claims without Financial issues.", ""),
IIF(MAX(Fields!Batch.Value) < CStr(Format(TODAY(),"yyyymmdd")),"Overnight Processing Issue.  Please use prior day report results."))

I get an error telling me the definition of the file is not valid. I don't see an obvious error in the new expression but obviously SSRS doesn't like it. Suggestions on what modifications I can make to make this a valid expression.

enter image description here

Solution

  • Not sure if this is exactly the login you are looking for but it should not give an error.

    Your code had the entire IIF's 'Condition', 'True part' and 'false part' in parentheses, I've changed that but still can't be sure if this is the logic you need.

    The Generic version of what you are trying to do is

    =IIF(
    This is true, 
    then return this,
    otherwise... IIF(
                     This is true,
                     then return this,
                     else return this
                     )
   )

    So read this and see if it makes sense....

    =IIF(
    CountDistinct(Fields!ClaimNumber.Value) = 0, 
    "There are no claims with Open Claims without Financial issues.",
    IIF(
        MAX(Fields!Batch.Value) < CStr(Format(TODAY(),"yyyymmdd")),
        "Overnight Processing Issue.  Please use prior day report results.",
        "")
    )