Conditional Formatting based on two columns within same report

[rhythmz]

I have a report which contains the following columns:

Location (loc)
Month\Yr. (mon_yr)
Person's Name (name)
Total Monthly Flight Time (tft_person)
Standard Deviation from Mean (std_dev)

as well as a footer that contains the monthly grouped data:

Average (avg_month)
Standard Deviation (stddev_month)
Significantly Above Average (above_avg) = avg_month + stddev_month
Significantly Below Average (below_avg) = avg_month - stddev_month

I want to color-code the 'Standard Deviation From Mean (std_dev)' column values for each person based on the following criteria

CASE
WHEN [tft_person] > [above_avg] THEN 'Excellent'
WHEN [tft_person] < [below_avg] THEN 'Poor'
ELSE 'Average' END

Excellent = Yellow
Average = Normal
Poor = Red


I have searched in vain through User Guides, Knowledge Base, & Cognoise. It seems all the examples I have found base the condition on a set column value threshold (i.e. Budget_total > 50%), however, I don't see a guide on basing a condition on < of one column & > of another column. I hope this makes sense.

Anybody know how I can set the background color of 'std_dev' column to:
Yellow IF [tft_person] > [above_avg]
Red IF [tft_person] < [below_avg]
Normal if else

I have tried numerous ways and get ERRORs like RSV-VAL-0034 & CRX-API-0016.

THank you for any help provided.

[Sridevi]

Hi,

Try doing it this way...

1.Create a dataitem with your expression

case when ( [tft_person] > [above_avg] ) then 'Excellent'
when ([tft_person] < [below_avg] ) then 'Poor'
else 'normal'
end

2. Then create a string variable for the conditional formating on this column.
Add Excellent,Poor,Normal to the variable and set the colors accordingly.

You can refer to the attached sample report xml code.

Thanks,
Sridevi

[rhythmz]

I am working on implementing your advice as we speak. Will check back.

Thanks Sridevi! ;D

[rhythmz]

Your advice also produces errors that cannot be located in IBM Knowledge Base (RSV-VAL-0022). I am limited in exploring your attachment because we do not have the Great Outdoors package loaded on our server. I have tried just about all that I know and will abandon this functionality. It is hard to believe that I can't do in 8 what I could do in 7.4.

I have been able to get this to work before but in those instances was able to define my conditions on specific numeric thresholds (i.e. budget_pct => 50%) whereas in this instant needed to based a condition of 1 column on the comparison of two other column values.

Thank you very much in your attempt to help but I am out of ideas at this point!

[Sridevi]

Please try to browse on how to use "String Variable" for conditional formatting. That should help you.
You are just looking at boolean variables, which will be used for cases like value > 50%.

Thanks,
Sridevi