[RESOLVED] CR Summarizing based on a condition

[Besoup]

Ok I have a field called invoice_amount and another called age_of_invoice... I want to summarize the invoice_amount into 4 different ages, ie <30 days, 31-60 days, 61-90 days and anything higher. This summary will have to be group level also. Anyone know how to do this?

[brucevde]

Create a Formula and Group by the Formula. The following is in Crystal Syntax.

Select {age_of_invoice}
Case Is < 30 :
30
Case 31 To 60 :
60
Case 61 To 90 :
90
Default :
91

[Besoup]

Not quite sure if I understand what you mean or if you understand what I am trying to do... I have 4 fields that need to be group level sums of the Invoice_Amount based on the age of the invoice.

Groups are already set up by vendor, each vendor needs to have totals of the invoices depending on how old the invoice is. Each vendor has multiple detail lines, one for each invoice.

Am I making sense? :P

[Besoup]

Figured out a way to do it... had to make a suppressed field for each of the age ranges (detail level) then I had to make a group sum for each of them. There is probably a better way to do it but this seems to work.

[brucevde]

There are probably several ways to create this report. Running Totals would work as well. This is how I understood your report


Group #1 - Vendor
Group #2 - Age of Invoice formula
Details - Invoice amount
Group #2 Footer - Sum Amount by Age of Invoice
Group #1 Footer - Sum Amount by Vendor

[Besoup]

Yeah I am actually rewriting a system that was on a Wang Mainframe and trying to get the reports as close to the old ones (in layout) as possible.

The layout is:

Group header- Vendor No. - Current Invoice Amt - 31 through 60 - 61 through 90 - Over 90
Details- Invoice info with amount and age
Group Footer - blank