Multiple SUMS withen a group

[BigDaddyE]

I have a list of Employee charges that I want to calculate sums on by type.

Employee Charge Type
Bob $1 car
Bob $3 bike
Steve $1 car
Bob $2 car
Bob $4 bike

So based on the example data above I want to see the following on the same line:


Bob Car = $3 Bike = $7
Steve Car = $1 Bike = $0

 

[kray4660]

Group by employee. Two running totals that evauluate when the type matches (i.e., Type = 'car').

I hope this helps.

 

[Madawc]

If you can't change the existing order, a crosstab would do it.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[BigDaddyE]

I am looking for something that looks more like

if Type = Car then sum Charge
else if Type = Bike Sum Charge
then reset for Employee group

I just dont know how to do this in Crystal Syntax and I figured that someone out there could do this really quickly.

 

[fisheromacse]

Following either of the above suggestions should get you the information you asked about, just in 2 different formats.

 

[BigDaddyE]

Please type it out for me because I need an example.

I tried this :

if {Type} = "Car" then Sum ({Charge})

and I placed this in the Employee group footer, but it totaled the entire reports Charge field. This would work if I only had one employee. But I have multiple Employees, so how do I reset this for each employee or please spell some other option out for me .

 

[fisheromacse]

insert a group on employee ID (or whatever unique identifier is present for each employee)

create two running totals.

{Running Total #1}
Field to Summarize = {table.Charge}
Type of Summary = SUM

Evaluate = Use a Formula ----> {table.Type}="Car"

Reset = on change of group (use the group you created above (employee ID))


then create a 2nd running total for bike

 

[BigDaddyE]

This is what I did and it worked. Sorry if I was confusing anyone.

//*******************************************************
//*** Create a hidden formula called @Reset and place it
//*** in the Employee Group Header, like this:

WhilePrintingRecords;
numbervar total := 0;
numbervar total2 := 0;
//*******************************************************

//*******************************************************
//*** Create a hidden formula called @Calc and place it
//*** in the DETAIL, like this:

WhilePrintingRecords;
numbervar total;
If {Type} = 'Car' then
total := total + {Charge};
Else If {Type} = 'Bike' then
total2 := total2 + {Charge};
//*******************************************************

//*******************************************************
//*** Create a formula called @Print and place it
//*** in the Employee Group Footer, like this:

WhilePrintingRecords;
numbervar total;
numbervar total;
total := total;
total2 := total2;
//*******************************************************

 

[BigDaddyE]

If you can improve this please

 

[lbass]

You could simply insert a crosstab that uses employee as the row, type as the column, and sum of charge as the summary.

If isntead you want to use conditional formulas, they should take this form:

if {table.Type} = "Car" then
{table.Charge}

if {table.Type} = "Bike" then
{table.Charge}

Place these in the detail section, insert a group on employee, and then right click on each of the formulas ->insert summary->sum at the group level. But this is more work than is really necessary.

-LB