Nesting IIF in Reporting Services

1
votes

I have the expression below running as my BG colour =IIF(RowNumber(Nothing) MOD 2 =1,"#FCDFFF","Transparent")

But there are columns identified for additional formatting. For example, I want to colour a particular row base on a condition but knowing that I already have the above expression running across all rows how do I make that a reality?

1
reporting-services

1 Answers

2
votes

If you want various checks, it's easiest to use a Switch expression.

I have set up a simple test based on your scenario:

enter image description here

enter image description here

enter image description here

Now say I want to keep the alternating colour, but also highlight any values over 100 as red.

Change the BackgroundColor property expression to:

=Switch(Fields!Value.Value > 100, "Red"
    , RowNumber(Nothing) MOD 2 =1, "#FCDFFF"
    , True, Nothing)

This way, you keep the alternating colour but the first check takes precedence:

enter image description here

You can add more tests to the Switch as required.

Also, note that I have changed "Transparent" to Nothing in the expression - "Transparent" will actually work but gives a runtime warning:

[rsInvalidColor] The value of the BackgroundColor property for the text box ‘Value’ is “Transparent”, which is not a valid BackgroundColor.

Using Nothing gives the required result without any warnings.