Restrict Aging report to print only accounts over 45 days past due

What data do you have in the database?

DO you have the balance and payment dates?

mlmcc

The report I currently have is a report that prints statements for customers.  It includes amounts that are current and past due.  The buckets are defined by a parameter, I'm entering 45 for the days between columns.

I just want to include a new parameter that says print the statement only if the customer has invoices older than 45 days.  Or, in other words, don't print if the customer only has an amount in the current bucket.

Where InvoiceDate - 45 < Date()

Just looking for invoices over 45 days old won't work if a payment has been made more recently.

In the database are the amounts saved or do you need to calculate the amounts for the various buckets?
ie in the database do you have columns like
current amount due
over 30 days past due
etc?

mlmcc

The database does not save amounts in those buckets.  The Crystal report I'm looking to modify is putting the amounts in the appropriate buckets.  The report is a customer statement report.  It prints a statement per customer and shows the past due invoices and at the bottom shows the current amounts, 45days past, 45-60 days past, and over 60.  The number days between buckets is defined by a parameter.  So the report already functions correctly.  I just want to add the extra parameter so no statements are generated if there are only current amounts.

But no, the buckets are Crystal generated and not in the database.

What formula is used to put the transactions in buckets?

mlmcc

There's three formulas, one for each bucket:

Current:
if {@InvoiceOrDueDate} >= ({?StatementDate} - {?DaysBetweenCols}) then
    {@AmtLessRetainage}

31-60 (range can vary):
if {@InvoiceOrDueDate} < {?StatementDate} - {?DaysBetweenCols}
 and {@InvoiceOrDueDate} >= {?StatementDate} - ({?DaysBetweenCols} * 2) then
    {@AmtLessRetainage}

Over 60:
if {@InvoiceOrDueDate} < {?StatementDate} - ({?DaysBetweenCols} * 2) then
    {@AmtLessRetainage}

@InvoiceorDueDate, ?StatementDate, and ?DaysBetweenCols are all user defined in the report parameter.

Ido's first suggestion back in the first post may work.  Assuming that the report is grouped by customer, then if you can use a group summary to get the total for 45 (or more) days for the customer, then you could use group selection to only show the groups (customers) with amounts in that time period.  Any other customers would be hidden (suppressed).  Whether or not that is an option may depend on how you're calculating the totals for 45 days, etc. (which may depend on what is in the @InvoiceOrDueDate and @AmtLessRetainage formulas).

 Note that group selection is really suppression, meaning that the report will be reading the records for other customers.  It just won't be displaying them (although those customers may appear in the group tree).  Any CR summaries will include those other customers.

 Even if group suppression will work for you, if you have a lot of data, but most customers don't meet your "45 days" condition, then it might be worthwhile looking into using some kind of query to identify those customers on the server (as already suggested), so that all of that "extra" data isn't sent to the report (and then suppressed).

 James

Thanks for the input!  I think editing the stored procedure in SQL is going to be the way to go with this one, as several of you have pointed out.