choosing max/min subtotals within a group

[JamesFlowers]

hi,

I have a grouping of customer that has a group within it of contract , in a contract there can be many lines.
Each line contributes towards a subtotal per contract.

We need to discover potential min/max revenue per customer based pn the highest and lowest value of a contract within that customer (as only one will be valid)

customer
contract 1 1000
lin 1 500
line 2 400
line 3 100
contract 2 1500
line 1 1000
line 2 500
contract 3 800
line 1 800

so we want to see 1500/800 as potentials.

thanks

James

James Flowers
Crystal Consultant

 

[lbass]

Are you just trying to arrive at a display of minimum and maximum? Or do you need to work with the results?

You could do a group sort using contract totals as the sort field, and then create a formula:

sum({table.amount},{table.contract})

...and place it in both the customer group header and group footer, and then suppress the group #2 and detail sections.

Or, if you literally want a display like "1500/800", you could create a formulas like these:

//{@reset} for the customer group header:
whileprintingrecords;
numbervar max;
numbervar min;
if not inrepeatedgroupheader then (
max := 0;
min := 0
);

//{@accum} for the contract group header or footer:
whileprintingrecords;
numbervar amt := sum({table.amount},{table.contract});
numbervar max;
numbervar min;
if amt > max then
max := amt;
if onfirstrecord or
{table.customer}<> previous({table.customer}) then
min := amt;
if amt < min then
min := amt;

//{@Display} for the customer group footer:
whileprintingrecords;
numbervar max;
numbervar min;
totext(max,0,"")+"/"+totext(min,0,"")

Then you could drag the customer groupname into the group footer and suppress the other sections, if you wished.

If you wanted to work with the results, you would use separate display formulas and not convert them to text, as in (with an example calculation):

//{@DisplayMax}:
whileprintingrecords;
numbervar max;
max*.5

//{@DisplayMin}:
whileprintingrecords;
numbervar min;
min*.75

-LB