How to display Summary information in Detail Section.

[mhepner]

I am trying to display a Summary field in the Detail section of a report. The report is grouped by Customer. A customer may have multiple IPDETAIL and INBOUND records. I want to show the total volume for a particular customer. The formula to calculate the volume is as follows:

If {Waste.unit} = 'T' and {Waste.ps} = 'P' then
{Ipdetail.quantity}
else If {Waste.unit} = 'Y' and {Waste.ps} = 'P' then
{inbound.tons}
else If {Waste.unit} = 'U' and {Waste.ps} = 'P' then
{inbound.tons}
else 0

Thank you.

 

[FVTrainer]

The above formula only calculates a value for each transaction. If you want the sum of that formula, do the following (in the formula below, I've refered to your formula as {formula1} and the field you're grouping on {Customer}. Replace those items with your actual formula and field names):

1. Create a new formula. Enter the following text:

WhilePrintingRecords;
Sum({formula1},{Customer})

2. Place the formula in the details section.

 

[mhepner]

That worked great. Thank you.

My company wanted me to add another grouping Waste ID. For each company I could have several Waste IDs and they are non company specific.

How can I do the same exact thing as above with multiple groupings?

Thanks again for the help.

 

[FVTrainer]

So your data looks like:

Customer
WasteID 1
trans
trans
WasteID 2
trans

If you want a summary total at the transaction level for the WasteID group that the transaction is a part of, create the following formula and place it in the details section:

WhilePrintingRecords;
Sum({formula1},{WasteID})

Note that all that is changing is the "group" parameter of the Sum() function. So for each additional group, just create a formula based on the pattern above, changing the second agrument in the function to be the field of the group on which you want to total.

If you want to see the summary of the WasteID group at the transaction level, but the summary of the Customer at the WasteID group level, place the formula above in the details section and place the original formula in the WasteID group footer (or header if you want your subtotal there).

 

[mhepner]

I tried that but for some reason it is calculating the totals for every customer that has that waste id instead of listing it per customer. What they want me to do is print everything on the detail line. So you'll have:

Customer1 Waste ID1, Total Volume, etc.
Customer1 Waste ID2, Total Volume, etc.

Customer2 Waste ID1, Total Volume, etc.
Customer2 Waste ID2, Total Volume, etc.

Thanks.

 

[FVTrainer]

Ok, it makes sense that the formula would behave as you have described.

Well, how about this: You're really not grouping on Customer, you're grouping on the unique combination of customer and wasteid. So, create a new formula called "CustomerWasteID" that combines these two elements:

{Customer} + "-" + {WasteID}

If the WasteID is a numeric field, then the formula should be:

{Customer} + "-" + ToText({WasteID},0)

Now, instead of grouping on Customer, group on this formula field. Change the original formula I wrote to:

WhilePrintingRecords;
Sum({formula1},{@CustomerWasteID})

and place the formula in the details section. If you're using the GroupName field in the report, you'll probably want to replace it with the {Customer} field, as the GroupName field will now show something like "Jones-001" which probably isn't what you want.

 

[mhepner]

Do I need to reset the value of the formula back to zero after each occurance of a Customer and Waste? My report still seems to be adding all occurances of a waste ID regardless of what customer it is.

 

[FVTrainer]

Here is what I did to model what I thought was the problem described:

Using the Xtreme Sample Database that comes with Crystal, I created a report that includes the following tables (and using the Smart Linking to get the joins):

Customer
Orders
Orders_Detail
Product

Your problem resembles a scenario where you want to see a Customer and the products they've ordered. Obviously, multiple customers can order the same product. So, I created the following formula and grouped on this formula:

Formaul CustomerProdcut:
------------------------
ToText({Customer.Customer ID},0) + "-" + ToText({Product.Product ID},0)

In the details section of the report I placed the quantity field from the Orders_Detail table. This corresponds to the formula you cited in your original post (for the sake of the example, it doesn't matter if its an "if" formula or a database field, since your formula is a first pass formula and you are trying to sum it).

I then added a subtotal at the group level, so I could see what the target value for each group was.

Finally, I created the following formula and put it in the details section:

Formula SumCustomerProduct:
---------------------------
WhilePrintingRecords;
Sum({Orders_Detail.Quantity},{@CustomerProduct})

So apparently I don't understand your problem correctly, because when I look at the results of SumCustomerProduct in each Customer's detail section, it looks like what I understand your request to be. I am only getting the total of that product for the individual customer.