Crystal Reports Crosstab highlighting

Question

Using Crystal Reports 11.5:

I have created the following crosstab table:

Time    Fac1     Fac2     Fac3
0-10     2        1        2  
10-20    9        1        23  
20-30    34       1        21  
...

The table illustrates that we spent 0-10 minutes twice at Fac1, once at Fac2, etc.

I am trying to highlight the cell for each facility that indicates that the highlighted cell and the preceding cells represent 50% of the stops at that facility. For example, for Fac1, the 34 should be highlighted because that indicates 50% (or more) of the stops at that facility. For Fac3 23 should be highlighted because 2+23 = 25 which is > than 50% of the stops at that location.

I cannot figure out how I can go about doing this. The only thing I can come up with is a running total for that column to count up the stops up to that time period, and if that number >= TOTAL*.50 (where TOTAL = total stops at that facility), highlight the cell. However
1) I don't know how to do a running total with a crosstab - it doesn't seem to work as I want and
2) That would highlight the following cells as well, which I don't want - just the cell that transitions the high 50% and low 50%.

Can anyone help me on this?

Unknown Unknown · Accepted Answer · 2012-03-30T11:14:08

If you are using a SQL datasource, you should be able to add a new field to the dataset to hold the running total on the same basis as the columns in the crosstab. You can then format the crosstab cell with a formula comparing the running total value with the column total.

To prevent highlighting the subsequent cells, add a second running total field similar to the first but in the opposite direction, then amend the format formula to only highlight the cell where both running totals are more than 50% of the column total.

Crystal Reports Crosstab highlighting

1 Answers