Summary of distinct count field 1

[crystaldev1]

I'm using CR 9 and SQL Server 2005. For ex, I have group #1 "Cars" and group #2 "Colors". In group #1 footer, I have a summary to sum the field of "Prices". The problem is in group #2, there may be more than one "Prices" of the same car.

Cars
Red
Toyota $10,000
Dodge $15,000
Blue
Toyota $10,000
Ford $5,000

I would like to sum the distinct cars in group #2. So the correct sum is $30,000, not $40,000. I would appreciate any help on this. Thanks.

 

[lbass]

Create a set of formulas like this:

//{@reset} to be placed in GH1:
whileprintingrecords;
currencyvar sumcar;
stringvar cars;
if not inrepeatedgroupheader then (
sumcar := 0;
cars := ""
);

//{@accum} to be placed in the detail section:
whileprintingrecords;
currencyvar sumcar;
stringvar cars;
if instr(cars,{table.car}) = 0 then (
sumcar := sumcar + {table.price};
cars := cars + {table.car}
);

//{@sumprices} to be placed in the group #1 footer:
whileprintingrecords;
currencyvar sumcar;

-LB

 

[crystaldev1]

Thanks lbass.