SSRS Report - IIF statement syntax question

JackR 0
Viewed 29

In a tablix - we wish to fill a column textbox (background) with "Yellow" if a threshold is not met. Here is the expression

=IIF((Fields!TotalMonths.Value-Fields!TotalReversalMonths.Value)<(Fields!Baseline.Value,"AgencyData"),"Yellow","No Color")

[Total Months] and [Total Reversal Months] are from the tablix dataset, Baseline is from a different dataset "AgencyData".

Get the following compiler error

The BackgroundColor expression for the text box ‘Textbox15’ contains an error: [BC30516] Overload resolution failed because no accessible 'IIf' accepts this number of arguments.

Pulling my hair out over this one. Help - please!

iif reporting-services
1 Answers

Here's your code in a more readable format:

=IIf(
        (Fields!TotalMonths.Value-Fields!TotalReversalMonths.Value) 
    <   (Fields!Baseline.Value,"AgencyData"),
    "Yellow",
    "No Color"
)

While the error message certainly suggests that you have unbalanced parentheses, it's clear you do not. The issue is in the 3rd line:

(Fields!Baseline.Value,"AgencyData")

I don't think this how you get a value from another dataset. It's like a naked tuple, which I think is improper syntax, hence the confusing error message.

If there's only one row, you can use:

First(Fields!Baseline.Value,"AgencyData")

If you need to select a particular row in that data set based on the values in the current context you should look at the Lookup, LookupSet, or MultiLookup functions.

Luke Kubat0
Related