Compare Individual & Group Sum Percentages 2

[FMHarris]

I'm using CR10 and have created a CrossTab that shows each sum and it's percentage of sum for each row by column e.g.:

D H N S U Tot
a 2 1 2 3 2 10
20% 10% 20% 30% 20% 100%
b 1 2 1 4 2 10
10% 20% 10% 40% 20% 100%
c 3 3 1 2 1 10
30% 30% 10% 20% 10% 100%
Tot 6 6 4 9 5 30
20% 20% 13% 30% 17% 100%

What I'm trying to do is this:
By column if any individual row pct is greater than the group pct I want to highlight the background yellow.

Using the example above, column D/row c would be highlighted because the row sum pct is 30% which is greater than the group sum pct of 20%.

Can someone help me with this, please?

 

[lbass]

Let's call your column field {table.city} and your row field {table.street}, and your summary field is {table.qty}. Make sure you have a group in your main report on {table.city}. If you can't do this, insert a subreport where you can, and insert the crosstab in the subreport. Then create a formula {@citypct}:

sum({table.qty},{table.city})% sum({table.qty})

Add this as your first summary field in the crosstab, and change the summary to maximum. Then add the sum of {table.qty} twice, changing the summary to percentage the second time.

Go into preview mode and select {@citypct} in the inner cell->right click->format field->common tab->suppress->x+2 and enter:

whileprintingrecords;
numbervar citypct := currentfieldvalue;
false

Then select the percentage field (third summary) for the cell->right click->format field->borders->color->background->x+2 and enter:

whileprintingrecords;
numbervar citypct;
if citypct < currentfieldvalue then
crYellow else crNocolor

This will highlight any percentage that is lower than the city percentage.

In design mode, suppress the first summary and drag the summary boundaries to minimize its height.

-LB

 

[FMHarris]

That worked. Thanks LBass!