I would like to achieve a simplified result similar to the "Color Scale" function in Excel i.e. gradient colouring based on the lowest value (red) to highest value (green), except in my cross tab using Crystal Reports 2008. My cross tab looks a little like this:
HOURS L1 L2 L3 L4 Total
1 hours | 5 | 0 | 1 | 16 | 22 |
2 hours | 0 | 1 | 0 | 10 | 11 |
3 hours | 8 | 2 | 6 | 12 | 28 |
TOTAL |13 | 3 | 7 | 38 | 61 |
The principle of my function is find the maximum value in the cross table then use 20%, 40%, 60%, 80% values to colour the background. Function is as follows (in the format background section):
if currentfieldvalue < ((Maximum (MakeArray (CurrentColumnIndex, CurrentRowIndex, 1)))*0.2) then color(255,0,0)
else if (currentfieldvalue >= ((Maximum (MakeArray (CurrentColumnIndex, CurrentRowIndex, 1)))*0.2) and
currentfieldvalue < ((Maximum (MakeArray (CurrentColumnIndex, CurrentRowIndex, 1)))*0.4)) then color(255,192,0)
else if (currentfieldvalue >= ((Maximum (MakeArray (CurrentColumnIndex, CurrentRowIndex, 1)))*0.4) and
currentfieldvalue < ((Maximum (MakeArray (CurrentColumnIndex, CurrentRowIndex, 1)))*0.6)) then color(255,255,0)
else if (currentfieldvalue >= ((Maximum (MakeArray (CurrentColumnIndex, CurrentRowIndex, 1)))*0.6) and
currentfieldvalue < ((Maximum (MakeArray (CurrentColumnIndex, CurrentRowIndex, 1)))*0.8)) then color(146,208,80)
else if (currentfieldvalue >= ((Maximum (MakeArray (CurrentColumnIndex, CurrentRowIndex, 1)))*0.8)) then color(0,176,80)
It's not elegant, nor does it work properly, any assistance/suggestions would be much appreciated. I wasn't expecting it to be so complicated as originally I was working with the below assuming it would work, except it tells me that "CurrentFieldValue" is not a field.
if CurrentFieldValue < ((Maximum (CurrentFieldValue))*0.2) then color(255,0,0)
else if ... etc.
