Link to home
Start Free TrialLog in
Avatar of sfrisk
sfrisk

asked on

Crystal reports-include all records for group, but select based on aging date.

I created an aging report showing all of the customers that have a balance on their account. I want to modify the report so that if there is a balance, either debit or credit, over 60 days, the report will list all invoices (current, over 30, over 60, etc) for that customer.  Any customer that is current over 30 will not show up on the report.

I've tried "whileprintingrecords" and it only gives me the transactions that are over 60 days and not anything more current.   I also tried counting over 60 days transactions and then suppressing the account based on any count less than 1.  This didn't work either.  Any ideas???
Avatar of vasto
vasto
Flag of United States of America image

Can you post the sql, which is used to make the calculation? What is the database type?

I guess you are calculating the balance by restricting the report show just records older than 6 days. You might consider using subreports for the details (which will be slow) or to retrieve the data with a stored procedure or command.
Avatar of sfrisk
sfrisk

ASKER

This is what I was using for the count:

If datediff("d",{tarInvoice.TranDate},CurrentDate) >= 60 then 1 else 0

I created a formula under each grouping to suppress if = 0.  That didn't work.

It's a SQL database using SQL Server 2005.  Crystal Reports is Version 10.
You don't want to suppress the record if it is not older 60 days you want to suppress if the balance of the records older than 60 days is 0

 
For complex reports I am using stored procedured.
Avatar of sfrisk

ASKER

I want to show every record if one record is over 60 days.  If there aren't any records over 60 days, then I want to suppress the entire customer.  I'm trying to get a summarized list of those customers who have an delinquent account, but need to show all of the records for the account in the event that there was a recent credit memo issued.  If I show only those records that are over 60 days, then accounting would be calling the customer when their account may not be delinquent because the credit memo dropped off based on the transaction date.
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sfrisk

ASKER

Works great!!  Thanks.