Crystal reports conditional summary

Question

I am trying to create a conditional summary in a Crystal Report using invoice data where if an item code appears more than once on the invoice, only include the amount in the total once, and for the greatest amount. I also want this reflected in the number of items, eg if there are 4 items but two are the same item code, then the nbr of items for the invoice should be 3. Here is a sample of the data:

TDate   Invoice ID  Fee line    Item Code   Nbr Items   Amount
 05/01/2017 12024200    11425100||11    11610   1   54.20
 05/01/2017 12024200    11425100||12    55238   1   156.25
 05/01/2017 12024200    11425100||13    55238   1   96.60
 05/01/2017 12024200    11425100||15    55276   1   80.55

So the totals for Invoice 12024200 should look like this (ignore the values for the second occurrence of Item Code 55238).

 05/01/2017 12024200    11425100||11    11610   1   54.20
 05/01/2017 12024200    11425100||12    55238   1   156.25
 05/01/2017 12024200    11425100||13    55238   1   96.60
 05/01/2017 12024200    11425100||15    55276   1   80.55
 Total                                          3  291.00

Answer 1

To address the 2 issues in your question:

1)

Create a summary with "Distinct Count" over "Item Code" and set location to the invoice group or report footer.

2)

Create a group (G1) over "Invoice ID" ( you can place your invoice totals here).

Create an inside group (G2) and place a summary for max(Amount) by setting summary location to G2. It should print each item max cost (just for testing purpose).

Create a formula and place it on G2 footer.

WhilePrintingRecords;
Global NumberVar Total;

Total := Total + Maximum ({table.Amount}, {table.ItemCode});
Total;

You should see something like a running total when this formula is visible. Suppress all unnecessary fields.

To print the value in another group footer (like the invoice group footer) just create another formula with:

WhilePrintingRecords;
Global NumberVar Total;
Total;

To reset the value between invoices (if your report supports multiple invoices), create another formula and place in the invoice group header

WhilePrintingRecords;
Global NumberVar Total;
Total := 0;
Total;