Conditional formatting/highlighting in CrossTab

[MargaretL]

I'm creating a report in Crystal Reports v 14 (2011) with a crosstab (and I'm admittedly new to crosstabs), and trying to apply some conditional formatting (green/yellow/red highlighting). This is a report of physicians and patient satisfaction survey scores. The data is something like this:


Question1 Question2 Question3
Doctor A 78.1% 78.2% 83.7%
Doctor B 87.0% 86.6% 84.7%
Doctor C 93.1% 96.4% 92.9%

Each question has a different target and red line, also in the database table. For example, the Target for Question 1 is 90% and the Red Line is 85%. So, for Question 1, Doctor C should be highlighted in green (met target), Dr. B should be highlighted in yellow (between target and red line), and Dr. A should be highlighted in red (below red line).

I tried to apply a conditional format by selecting the cell in the crosstab, choosing "Format Field" and entering the conditional format under the Border/Background.

The conditional formatting code I'm using is:
if {Score} >={Target} then crLime;
if {Score} <={RedLine} then crRed
else crYellow

However, when I use this, the conditional formatting seems to spread to the whole table rather than being cell-specific. I also tried the "highlighting expert," but to no avail.

I'd appreciate any input on how to approach this.

Thanks!

 

[lbass]

You would have to add both target and red line to the crosstab as summary fields (Make them the first two summaries). Use maximums so that their values appear correctly above the score in each cell. Then in preview mode, select the target summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar target := currentfieldvalue;
true

Repeat for the redline field, where you would enter:

whileprintingrecords;
numbervar redline := currentfieldvalue;
true

Then select the score summary->right click->format field->borders->color->background->x+2 and enter:

whileprintingrecords;
numbervar target;
numbervar redline;
if currentfieldvalue >= target then
crlime else
if currentfieldvalue <= redline then
crred else
cryellow

In design mode, grab the bottom border of the target summary and bring it up to minimize its height. Repeat for the redline summary. You can make these so small that the blank rows virtually disappear.

-LB