nested iif problem

[DavidGraham167]

I have an expression to sum a number of fields

=Fields!quarter1.Value+Fields!quarter2.Value+Fields!quarter2.Value+Fields!quarter3

This works correctly as long as there are the 4 fields.

But depending upon what the user has specified there may only be 1, 2, 3 or 4 fields.
If there are not 4 fields it then fails and gives and error.

I thought I could use a switch statement but this also fails as it has to solve each switch element which it cannot do.

I think the answer is to use nested iif statements, but I don't think I have got the nesting correct
"error: [BC30516] Overload resolution failed because no accessible 'IIf' accepts this number of arguments"

Here is a siplified version of my iif statements.
I think I need the nesting deeper but am not sure.

=iif(Parameters!MyParam3.value=1,"Is 1",
iif(Parameters!MyParam3.value=2,"Is 2",
iif(Parameters!MyParam3.value=3,"Is 3",
iif(Parameters!MyParam3.value=4,"Is 4"))))

[jmcilhinney]

IIf has three parameters: the condition to test, the value to use if the condition is true and the value to use if the condition is false. Have you provided all three for each of your IIf calls?

[DavidGraham167]

Yes there was a missing 3rd parameter in the last iif.
The following works.
=iif(Parameters!MyParam3.value=1,"Is 1",
iif(Parameters!MyParam3.value=2,"Is 2",
iif(Parameters!MyParam3.value=3,"Is 3",
iif(Parameters!MyParam3.value=4,"Is 4",""))))

But if I make it more complex to calculate the sums of the fields then it is failing, it does not show any value if there are less than 4 fields.
=iif(Parameters!MyParam3.value=1Fields!quarter1.Value,
iif(Parameters!MyParam3.value=2Fields!quarter1.Value+Fields!quarter2.Value,
iif(Parameters!MyParam3.value=3,Fields!quarter1.Value+Fields!quarter2.Value+Fields!quarter2.Value+Fi elds!quarter3,
iif(Parameters!MyParam3.value=4,Fields!quarter1.Value+Fields!quarter2.Value+Fields!quarter2.Value+Fi elds!quarter3+Fields!quarter4,""))))

[FunkyDexter]

The occasional missing comma aside, I suspect your falling foul of the fact that SSRS evaluates the whole of an iif statement, even the bits it will never actually run. So if, for example MyParam3 is 1, this:-
Fields!quarter1.Value+Fields!quarter2.Value+Fields!quarter2.Value+Fields!quarter3+Fields!quarter4
will still get evaluated and fail. Don't ask me why MS made that decision, it seem dumb as hell to me but there you have it.

The upshot is you have to write your whole statment in a way that can always be evaluated. That's going to be tricky in your scenario if fields 2, 3 and 4 will genuinely not be returned. Is it possible to take a different tack? Could you, for example, return a zero or null in those fields (SSRS will interpret a null as zero in this scenario)?