Using Multiple IIF statements in a SQL expression in SQL report builder

0
votes

I'm adding a new column to an existing report called Refund Due. In this column I'm writing an expression for the following logic:

  1. If incurred > 0 then populate refund due column with No

  2. If incurred =< 0 and status = "closed" then populate refund column with Yes

  3. If incurred =< 0 and status does not equal "closed" then populate refund column with "Refer"

I wrote the following expression 

=IIF (Fields!GROSS_CLAIMS_INCURRED.Value>"0", "No", IIF(Fields!GROSS_CLAIMS_INCURRED.Value<="0" AND Fields!STATUS.Value="Closed", "Yes", IIF(Fields!GROSS_CLAIMS_INCURRED.Value<="0" AND Fields!STATUS.Value="Open", "Refer", IIF(Fields!GROSS_CLAIMS_INCURRED.Value<="0" AND Fields!STATUS.Value="Reopen", "Refer"))

But get the following error message when I preview the report:

The Value expression for the textrun ‘Textbox154.Paragraphs[0].TextRuns[0]’ contains an error: [BC30516] Overload resolution failed because no accessible 'IIf' accepts this number of arguments.

How can i fix this problem or which expression must i use to get required results?

Thanks in Advance for help.

1
sql-serverreportbuilder
You have 4 IIF statements but only 2 closing brackets at the end - have a look at that and see if that rectifies the issue. - DKyleo
Your last IIF only has two parameters. It needs three. By the way, this is a job for CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END MUCH easier to read. - O. Jones

1 Answers

0
votes

SWITCH is a much cleaner way of multiple conditional expressions than nested IIF statements:

=SWITCH(Fields!GROSS_CLAIMS_INCURRED.Value>"0", "No",
Fields!GROSS_CLAIMS_INCURRED.Value<="0" AND Fields!STATUS.Value="Closed", "Yes",
Fields!GROSS_CLAIMS_INCURRED.Value<="0" AND Fields!STATUS.Value="Open", "Refer",
Fields!GROSS_CLAIMS_INCURRED.Value<="0" AND Fields!STATUS.Value="Reopen", "Refer"
)