SSRS Expression Nest IIF Proper Syntax

Rob M.
Rob M. used Ask the Experts™
on
I am trying to create an expression in SSRS.

If WeightedIndexFloor.Value is NULL and Fields!LienType.Value = "Equity" then i want to return "NA". If the value is anything other that "Equity" then I want to return "None" for that NULL value.

This is part of the expression. It gives me NA when Lien Type = "Equity".

=IIF(ISNOTHING(Fields!WeightedIndexFloor.Value) and Fields!LienType.Value="Equity","NA",Fields!WeightedIndexFloor.Value)

However I am having an issue trying to nest the second IIf statement where Fields!WeightedIndexFloor.Value Is NULL and Fields!LienType.Value <> "Equity" and I need to return "None" for the NULL value.

=IIF(ISNOTHING(Fields!WeightedIndexFloor.Value) and Fields!LienType.Value<>"Equity","None",Fields!WeightedIndexFloor.Value)

Would someone be able assist with the proper expression?

Thanks in advance.

Rob M.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT / Software Engineering Consultant
Top Expert 2016
Commented:
Try this:

=IIF(ISNOTHING(Fields!WeightedIndexFloor.Value),IIF(Fields!LienType.Value="Equity","NA","None"),Fields!WeightedIndexFloor.Value)

Open in new window

(Updated: Corrected typo on IFF making it IIF)

»bp
Nest IIF's can get ugly, you could use Switch:
=Switch (   IsNothing(Fields!WeightedIndexFloor.Value) and Fields!LienType.Value =  "Equity", "NA"
	    ,  IsNothing(Fields!WeightedIndexFloor.Value) and Fields!LienType.Value <> "Equity", "None"
	    ,  True, Fields!WeightedIndexFloor.Value
	    )

Open in new window

Top Expert 2008

Commented:
Bill's suggestion seems to be the way to go.  I'd just change the IFF to IIF
=IIF(ISNOTHING(Fields!WeightedIndexFloor.Value),IIF(Fields!LienType.Value="Equity","NA","None"),Fields!WeightedIndexFloor.Value)

Open in new window

Author

Commented:
Thanks all. Both expressions are working solutions. I appreciate your responses.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial