Summaries of line formula for group repeating detail lines in calculation

[MikeMV]

I am using CR-2011 to extract daily sales order totals. The orders/totals have to be grouped by salesperson by customer and by order. In the order detail table I don't have a line total, I created a formula for this purpose, I then created 3 summaries to add the total of all like totals, the summary for group 3 (sales order) is OK, but the other 2 summaries repeat the total of the order for as many times as there are lines in the order detail table for a given order.

I have tried using running totals instead of summaries, I have tried changing the type of link between the tables, but the system still repeats the total of all lines for a given order as many times as there are in the order detail table.

I will greatly appreciate any feedback you can provide.

Salesperson: 01
Customer: 42502

Sales Order 1234
Line 1 OrderQty=10 Price=12 LineTotal=120
Line 2 OrderQty=5 Price=2 LineTotal=10

Sales Order 1234 Total=130

Sales Order 4567
Line 1 OrderQty=7 Price=10 LineTotal=70

Sales Order 4567 Total=70

Total for Customer 42502= 330

Total for Salesperson 01 = 330

SorMaster.Salesperson
SorMaster.Customer
SorMaster.SalesOrder
SorMaster.OrderDate
SorDetail.SalesOrder
SorDetail.OrderQty
SorDetail.Price
Tbl.Salesperson.Salesperson
Tbl.Salesperson.Name
ArCustomer.Customer
ArCustomer.Name
Grouping on:
SorMaster.Salesperson
SorMaster.Customer
SorMaster.SalesOrder
Record Selection SorMaster.OrderDate=CurrentDate

I am linking Tbl.Salesperson.Salesperson to SorMaster.Salesperson and
SorMaster.Customer to ArCustomer.Customer only to extract the name field to place on the report.

 

[IanWaterman]

Please show content of yor formula.

Are you showing detail lines? If they are suppressed, show them and see how many records come back for each order line

Ian

 

[Madawc]

Use a running total to count the occurances. Use the suppress option within Formatting on the total field to suppress it where you don't want it.

The use of Crystal's automated totals is outlined at FAQ767-6524.

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

 

[MikeMV]

Ian,

LineTotal={SorDetail.MPrice}*{SorDetail.MOrderQty}

I am suppressing detail lines, but when I show them I see 37 detail lines (for this particular customer/date) for 33 orders. If I do a summary field on the SorMaster.SalesOrder field it gives me an incorrect count of 37 orders, but if I do a unique count it gives me the correct number of orders of 33. In this particular case for every order that has 2 detail lines it is adding the totals twice.

I went into the SQL database and made sure that I have 33 orders for that day with 37 detail lines just in case, the data is correct.

This one has me confused, it is a seemingly simple report, but it is not working. This morning I created a new report from scratch to rule out the possibility that the one I am working with may be corrupt, but I still get incorrect results.

Thanks for your reply.

 

[IanWaterman]

A running total should work.

Sort the records so that duplications are alongside each other.

Create an RT to sum your formula
In the evaluate section of the RT select on change of field, select the filed which is duplicated.

You will need two RTs one for Customer Group and one for sales person, set the reset as appropriate for each group.

Ian