summary of sum?

[atthevilla]

Crystal 10, Oracle 10.
I need to determine get largest gift for each donor:
maximum({TABLE.PAID_AMT},{TABLE.COREID}). But I need to count multiple transactions from that came in on the same day from a single donor as a single gift (they split the money between two or more funds). How can I arrive at the maximum of the sum of gifts made on the same day by the same person?

 

[lbass]

It sounds like you have a group on funds that divides up the donations, and then maybe a group on date and then a group #3 on donor. The easiest thing to do might be to insert a subreport in the donor group so that you can simply insert a maximum on the gift amount at the donor level. The subreport would be linked on date and donor only.

-LB

 

[atthevilla]

The groups are donorid and giftdate. No grouping on funds is necessary. If

111000 John 1000 06/01/2006
111000 John 500 06/01/2006
122000 Sam 750 05/01/2006
122000 Sam 500 04/01/2006

Sam's largest gift is $750
John's largest gift is $1500 because the $1000 and $500 were made on the same day and so are considered one gift. I need to display $1500 for John.

I want to avoid subreports if possible because this is an export.

 

[dpatrickcollins]

The simplest way I can think of is to convert your table links to a SQL statement that pre-consolidates your "multiple-gift-on-same-day" rows into a single row using GROUP BY. Then, your Crystal experience is fine. In other words:

SELECT ID, DATE, SUM(AMNT)
FROM TABLE
GROUP by ID, DATE

If that luxury is not an option (i.e. the report is already built, you don't feel comfortable with SQL), then do the following:

1) Group by Person
2) Group further by ID and Date combined (make a formula that concatenates the two and use as your group field.
3) You can do this step at any time, but suppress detail and the group footer sections of group 2. We won't need them.
4) Create a Sum(AMNT) subtotal for the second group and place it in the group header so it is visible.
5) Use TOP N aka Group Sort expert to Sort Second group by biggest sums first. This will place the maximum consolidated sum at the very top for each Person.
5) To suppress all but the top row for each Person, use a formula in Section Expert>Suppress similar to the following:

Previous({@group_field}) = {@group_field}

6) This will give you the maximum consolidated amount for each person. Make any other cosmetic changes and you are done.

These are a few steps, but that is the price we pay for having a data structure not in agreement with our data objective. Note that if you are also including grand totals, running totals will have to be used in lieu of standard grand total functions or it will pick up the hidden amounts. If yours is destined for export, however, this is probably less of a concern.

 

[lbass]

I would use a command to write the SQL just as dpatrick suggests. Then link the command to the main table on ID. Place the summary field (sum(amt)) in the detail section and insert a maximum on it at the ID level. Then drag the result into the Group #1 header (ID) and suppress the other sections.

-LB