In Crystal Reports, how to dynamically format a cross-tab on a gradient?

Question

This has already been asked elsewhere including Crystal Reports Cross Tab Conditional Formatting however I found it hard to discover an answer to this question and also found that the answers out there are very specific.

How does one dynamically and automatically format the cells within a Cross-Tab in Crystal Reports to show a gradient between any two colours?

Answer 1

I developed a generic bit of code that should be easy to modify and apply for any two-colour gradient scale.

Select the cell in your Cross-tab you want to modify and choose Format Field. Go to the Border tab. Click on the x+2 button next to Background. Enter the following code in the formula field, and save.

local Numbervar max := 100; -- Put in the highest possible value here. I'm using percentages so this goes up to 100.
local Numbervar min := 0; -- Put in the lowest possible value here. I'm using percentages so this is 0.

// Reference Yellow 255,231,63 -- Pick colour one here, for the low end of the scale. This is yellow. You'll need the RGB and plug them in below
local Numbervar c1r := 255; // red value for the first colour
local Numbervar c1g := 231; // green value for the first colour
local Numbervar c1b := 63; // blue value for the first colour

// Reference Green 105,192,98 -- Pick colour two here, for the high end of the scale. This is green.
local Numbervar c2r := 105; // red value for the second colour
local Numbervar c2g := 192; // green value for the second colour
local Numbervar c2b := 98; // blue value for the second colour

local Numbervar cRed;
local Numbervar cGreen;
local Numbervar cBlue;

if currentfieldvalue >= max then color(c2r,c2g,c2b)
else if currentfieldvalue <= min then color(c1r,c1g,c1b)

else
(
if c1r = c2r then cRed := c1r
else if c1r > c2r then cRed := Floor(c2r + (c1r-c2r) * (1-(currentfieldvalue-min)/(max-min)))
else cRed := Floor(c1r + (c2r-c1r) * (currentfieldvalue-min)/(max-min));

if c1g = c2g then cGreen := c1g
else if c1g > c2g then cGreen := Floor(c2g + (c1g-c2g) * (1-(currentfieldvalue-min)/(max-min)))
else cGreen := Floor(c1g + (c2g-c1g) * (currentfieldvalue-min)/(max-min));

if c1b = c2b then cBlue := c1b
else if c1b > c2b then cBlue := Floor(c2b + (c1b-c2b) * (1-(currentfieldvalue-min)/(max-min)))
else cBlue := Floor(c1b + (c2b-c1b) * (currentfieldvalue-min)/(max-min));

color(cRed, cGreen, cBlue)
)

color(cRed, cGreen, cBlue)