Crystal Reports Cross Tab Conditional Formatting

Posted by ltran on Stack Overflow See other posts from Stack Overflow or by ltran
Published on 2012-10-07T09:48:27Z Indexed on 2012/10/08 15:38 UTC
Read the original article Hit count: 173

Filed under:

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.

© Stack Overflow or respective owner

Related posts about crystal-reports