Sum Group with whileprinting records formula

[crystalite]

I am using Crystal 8.5. I want to get a sum in my GH1 using the Cost of the first option in each group (GH2).
I have the following formulas:

// to count the options in the details
whileprintingrecords;
numbervar counter;
counter:=counter+1;
counter

// to get the cost of the first option in the details
if {@counter} = 1 then {Cost}

//reset counters in GH2 header
WhilePrintingRecords;
numbervar counter:=0;
numbervar sumcounter :=0;

//total for group (1st record) in GF2
whileprintingrecords;
numbervar sumcounter;

Here is detail data that may help explain situation better:

Feature# Option# Cost
Car (GH1)
Window (GH2)
Window a (D) 1 1 10
Window b (D) 1 2 20
Window c (D) 1 3 30
Door (GH2)
Door a (D) 2 1 5
Door b (D) 2 2 15
Door c (D) 2 3 25
Tire (GH2)
Tire b (D) 3 2 6
Tire c (D) 3 3 16
Tire d (D) 3 4 26
Brakes(GH2)
Brake p (D) 4 7 8
Brake q (D) 4 8 18
Brake r (D) 4 9 28

I would appreciate any help.

 

[lbass]

Is Option# an actual database field or is this your formula that counts? What determines the sequence of options in the database? There should be some other field that would determine the sequence.

-LB

 

[crystalite]

Option # is an actual database field. The sequence in the database is by option #. The sequence starts with the first option number, doesn't have to be "1", just the lowest number.

 

[lbass]

Create a SQL expression {%minopt} like this:

(
select min(`Option#`)
from table A
where A.`feature` = table.`feature`
)

...where "feature" = your group #2 field. Since the SQL expression goes directly to the database, if you have certain record selection criteria, you might have to build these in also, if they affect the result.

Then in the report, create a formula {@firstcost}:

if {table.Option#} = {%minopt} then {table.cost}

You can place this in the detail section and insert a sum on it at the Group #1 level, and then drag the result into the GH#1.

-LB

 

[crystalite]

Thanks for your help LB.

I'm new to crystal, where would I create the SQL expresiion?

 

[lbass]

Go to the field explorer->SQL expression->new and enter it there. If you are unsure about the punctuation for your particular datasource, check in database->Show SQL query and see how the punctuation is used there.

-LB