CODEWITHSUNDEEP
reporting-servicesswitch-statementconditional-formattingiif
derek_
derek_

Reputation: 1

SSRS conditional formatting column with alternating row highlighting and NULL handling

I have an SSRS report where I'm supposed to conditionally format a column red/green depending on if an expression is +/- 5% (less is good, so less is green). I also need to have the entire report have alternating row highlighting. I can get these to function correctly until I throw in some NULL handling. 

=Switch
(
IsNothing(Sum(Fields!August2015.Value)),IIf(RowNumber(Nothing) Mod 2 = 0, "#d9d9d9", "Transparent"),
(Fields!August2015.Value-Fields!CorporateAvg.Value)/Fields!CorporateAvg.Value >= .05, "Pink", 
(Fields!August2015.Value-Fields!CorporateAvg.Value)/Fields!CorporateAvg.Value <= -.05, "LightGreen",
(Fields!August2015.Value-Fields!CorporateAvg.Value)/Fields!CorporateAvg.Value > -.05 And 
(Fields!August2015.Value-Fields!CorporateAvg.Value)/Fields!CorporateAvg.Value < .05, IIf(RowNumber(Nothing) Mod 2 = 0, "#d9d9d9", "Transparent")
)

It looks like the issue is that once something is green or red the alternating row highlighting resets and throws the rest of the rows off. Here's an example:

Example of issue

enter image description here

Upvotes: 0

Views: 598

Answers (1)

Jonnus
Jonnus

Reputation: 3038

I believe the issue is around the section 

IIf(RowNumber(Nothing) Mod 2 = 0, "#d9d9d9", "Transparent")

As no scope has been set it is not clear when the row count is reset to zero. You cold always see what this rownumber value is by temporarily adding a second expression to the cell to show just the RowNumber(Nothing) value

Have you tried using CountDistinct and RunningValue instead (assuming you have a unique value per row). For example, replacing the noted section above with 

IIf(RunningValue(CountDistinct(Fields!Serial.Value), Sum, "DataSet1") mod 2), "#d9d9d9", "Transparent")

This is then based on the dataset you are reading and the sort order of the results, and not just the order of the rows as they appear on the report itself.

Upvotes: 0

Related Questions