Sum a Field to show in Group Header 1

[rwn]

I have a formula that is:
@TotalPrice= {Job.Unit_Price}*{Delivery.Remaining_Quantity}

When I do a summary against the Group Header Job, it actually counts the jobs in the detail and gives an incorrect amount. For example.
GH1 Job xyz @TotalPrice = $30
Three detail lines representing unique deliveries.
The Total in the GF1 is $90 vs I need to to be $30 representing the Jobs actual @Total Price.

 

[lbass]

If you are grouping on Jobs, and there should be only one value per job, then insert a maximum on your formula.

-LB

 

[rwn]

There can be many Jobs per GH and if one Job os $30 and another is $600 the MAXIMUM formula then indicates $600.

 

[rwn]

A bit more detail.

The Group Header is Job, a Job has three delivery dates so the field Job.Total_Price ($30.00) is being read three times ($90) at the total of the Group Footer vs just once ($30).

I hope this helps .

 

[lbass]

If the group is inserted on the job field, then there can be only one job per group. Apparently you are grouping on some other field.

You need to use a running total that sums on change of job and resets on change of whatever your higher order group is (I guess it isn't job). Then place the running total in the group footer.

-LB

 

[rwn]

Group 1 Date
Group 2 PO Number
Group 3 Job #
Details: Deliveries for each Job.

The Total Price is the same amount when in the Details section, and thus the total is based on the Details vs just on the Group 3 Job #.

 

[lbass]

You would get the correct value by using maximum at the Job group level, but for the date group, use the running total, evaluate on change of job, reset on change of date.

-LB

 

[rwn]

Here is what I have:

Field to Summarize= Job.Total_Price
Evaluate= On Change of Group 4: Job.Job
Reset=On Change of Group: Group#1: {PromisedDate}

 

[rwn]

Thanks, this did the trick.

Field to Summarize: Job.Total_Price
Evaluate: Job.Job
Reset: Group 1 Date.


Now I need a grand total for this. Can you give me once more advise.

 

[lbass]

You can create a second running total set up the same way except set reset to never. Place the result in the report footer.

-LB

 

[rwn]

Actually, this Running Total didn't work for when it SUMS it totals incorrectly. The Running Total is as such:
Field to Summarize: Job.Total_Price
Evaluate: Job.Job
Reset: Group 1 Date.

The Grouping is as such:
Group 1 Date
Group 2 PO Number
Group 3 Job #
Details: Deliveries for each Job.

Thus the problem, during the Summary if a Job is listed more than once with a delivery it sums the additional Job.Total_Price when it shouldn't. A Job should only be sumarized once.

 

[lbass]

The evaluation should be on change of group job #. As long as the job cannot appear in more than one Date or PO Number group, this should evaluate correctly.

-LB

 

[rwn]

That is correct. The Job only appears on one Date and One PO Number.

The Running Total is now:
Field to Summarize = Job.Total_Price
Type of Summary = SUM
Evaluate = Group #1 Promise Date Monthly
Reset = Group #1 Promise Date Monthly

but is does a strange thing, it only evaluates the first job in each month vs all of them.

 

[lbass]

You should be evaluating on change of the job group.

-LB

 

[rwn]

That did the trick, it is so wonderful that this forum offers that ability to bounce issues off one another. Thank you again!!