Sum of 'field#2' for all 'field#1' in table ---

[Divinity2779]

I am using Crystal 8.5 for MAS 90 and am having trouble figuring out how to formulate what I need:

I have a table which contains the following fields and recordsets in those fields. The formula I am trying to create would take all Item No that match each other in all other records in the table and sum the Qty. Ordered. So, for the case below Item # 111 would have a Total Qty. on Order of 200.

Sales Ord.# Item No: Qty. Ordered:
10 111 100
222 100
333 100
444 100

20 111 100
222 100
333 100

I hope this makes sense. I have a feeling this might be a dumb question, but I am still new to this.

Thanks

 

[synapsevampire]

Are you stating that you want to group by the Sales Ord, but the grouping should reflect the sum of all qty order for ALL other Sales Ords for the same Item Nos in each Sales Ord?

Try posting example data, and then the expected output to clearly identify requirements, rather than leaving it up to the imagination what the underlying data looks like.

If you need the qty for all of the sales ords, you'll need a subquery or subreport to accomplish this.

To use a subreport, group by the Sales Ord, and then the Item Nos, use Insert->Subreport, add in the same tables to the subreport, create the sum for just the Item Nos, link the subreport via the Item Nos and then you should be able to return the sum via a shared variable:

Main Report Item Nos group header code:
whileprintingrecords;
shared numbervar SumItemNos := 0;

Right click the Item Nos and select insert section below and place the subreport in there

Item Nos Group Footer A
Place the subreport, and the subreport should have the following code in a formula:
whileprintingrecords;
shared numbervar SumItemNos := sum({table.qty})

Item Nos Group Footer B formula to display the total
Main Report Item Nos group header code:
whileprintingrecords;
shared numbervar SumItemNos;

-k

 

[lbass]

If you are designing the report from scratch, insert a group on {table.item#}, not on sales order #, and then right click on {table.qtyordered} and insert a summary.

If you need to have a group on sales order #, you could go the subreport route as SV suggests, or if you don't have too many items, you could use the running total expert to create a running total for each item. You would select {table.qtyordered}, sum, evaluate based on a formula:

{table.item#} = 111

Reset on change of group (sales order) or never depending upon your needs.

-LB