Group by Range for Invoice Pricing by Volume

[KM50111]

Using CR9/ODBC(RDO) to SQL2000.

I need to create an invoice where there is different pricing by volume and different pricing by product.

I was hoping to create a group by the volume range, but a group is not allowed when counting the records. Any suggestions on how to calculate the pricing and then sum for the invoice total?

Example:
Product 1
1-10 cost = $5; 11-20 cost = $4; 21+ cost = $3

Product 2
1-10 cost = $8; 11-20 cost = $7; 21+ cost = $6

Invoice:

Product 1 (14 sold)
(1-10) 10 @ $5 = $50
(11-20) 4 @ $4 = $16
Product 1 Total = $66

Product 2 (22 sold)
(1-10) 10 @ $8 = $80
(11-20) 10 @ $7 = $70
(21+) 2 @ $6 = $12
Product 2 Total = $162

Invoice Total = $228

I've grouped by product with a distinct count summary. I've tried group sections at the product group and hardcoded the ranges, subtracting the range from the distinct count. I am able to display the cost at each volume range, but not add them up.

I've also tried a formula with a manual running total to determine the volume range for each sale, but then a group is not allowed on the range formula.

Your help is greatly appreciated. Thanks.

 

[lbass]

If you are getting the desired amounts in the range groups, then use a formula like this to collect them:

//{@accum} to be placed in the range group section:
whileprintingrecords;
currencyvar sumx := sumx + {@rangetotal};
currencyvar grtot := grtot + sumx;
sumx

Add a reset formula to the product group header:
//{@reset}:
whileprintingrecords;
currencyvar sumx;
if not inrepeatedgroupheader then
sumx := 0;

//{@displayprodtot} to be placed in the product group footer:
whileprintingrecords;
currencyvar sumx;

//{@displaygrandtotal} to be placed in the report footer:
whileprintingrecords;
currencyvar grtot;

-LB