Highlight the Maximum and Minimum Group without sorting

[MRyerson]

I have a report which lists customers alphabetically. The detail of the report, which is suppressed, is invoices. The visible group shows total sales for the date range selected for the report.

The boss wants the list to remain alphabetical, but he wants the line (or field) containing the customer with the most sales and the one with the least sales highlighted.

Is there any way to do this? The highlighting condition would have to be evaluated at the end of the report, and then applied to the proper group footer (or field in the group footer).

I don't think it is possible, but I need a second opinion.

Marty

 

[RJFost]

Why not a hidden field that calculates the Maximum/Minimum sales, then format the customer field to be highlighted if their particular sales equal that Max/Min value? You'll likely need to specify that the highlight formula is evaluated after the Max/Min operations.

 

[lbass]

Create an unlinked subreport "Maximum" that is placed in the report header that contains the fields and grouping necessary to result in the same total sales per customer as within the main report. In the subreport, do a topN sort, N=1, and uncheck "Include Others." Also in the subreport, create a formula {@max} and place it in the subreport group footer:

shared numbervar max := sum({table.sales},{table.customer});
max;

Create a second unlinked subreport "Minimum" in the exact same way (also for the report header), except do a bottomN sort, N=1, and create a formula {@min} to be placed in the subreport group footer:

shared numbervar min := sum({table.sales},{table.customer});
min;

Then, in the main report, go to format section->group footer->color->background->x+2 and enter:

if sum({table.sales},{table.customer}) = {@max} then cryellow else
if sum({table.sales},{table.customer}) = {@min} then crRed else crnocolor;

Suppress all sections within the subreports and eliminate the borders so that the subreports do not show in the report header, but do not suppress or hide the report header, since then the shared variables would not work.

-LB

 

[kenhamady]

An alternative to a subrpeort is to create 2 invisible cross-tabs in the report header (TopN and BottomN) and capture the customer names in the cross-tab to use later in the report. The technque is described in my newsletter and can also be used to calculate running totals within a cross-tab:

http://www.kenhamady.com/news0212.html#cdug

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[MRyerson]

Thanks to everyone who responded. After trying them all, I think the one that will work best in this situation is the sub-report solution.

Why didn't I think of that!)

Marty