This would be a classic sql statement of the type you would want.
select x, y, z
from client, accounts
where client.client_id = accounts.client_id
But if you are just designing the report with Crystal you go into the database expert and join the tables in question. I believe you want an inner join.
You are going to have a set of account entries for every client. Each of these will show up as a single record, this is rudimentary.
customer 1 Item 1
customer 1 Item 2
customer 1 Item 3
customer 2 Item 1
customer 2 Item 2
etc.
In each line item, you determine which 'slot' the value goes into. You can use a formula as listed above by Synapse or a similar one to determine whether it will be a positive number which should return the value of the item as a positive or negative.
Next you need a formula to determine what the difference is in days between your date of reporting (your 'date in question') and the date of the individual item.
datediff("d",{table.receiptdate},{@dtReportDate}) where @dtReportDate is your reporting date. It can be any date, of course, but you would have to set up a parameter.
Then you need a formula to determine which day's slot the item fits into.
select {@numCalculateDateDiff}
Case is <0:
"Error - Future Date, not past due"
Case is <31:
"0-30"
Case is <61:
"31-60"
Case is <91:
"61-90"
Case is <121:
"91-120"
Case is >=121:
"Over 120"
default :
"Error"
You can do this with an if-then, of course, if you are more comfortable.
Then you need a set of formulas for each date slot, which you can lay out across your page.
//
if {@strDaysSlot}="0-30"
then
{@numDollarvalue}
else
0.00
You build one of these for each of your slots. As you can see, it looks for the string value returned by the case statement listed above and either returns the dollar value or a zero.
Then you group by the customer. This is critical, since the very next step is to do a summary field for each of the above slot values. That way each item gets treated as it's own.
In the group, put the client name, and the summary fields as they go across.
Suppress the detail records, and only display the summary fields. This allows you to show only the part you care about which is the totals.
You should end up with an output that has a row for each client, and a set of headings that say "0-30", "31-60", etc across the top, and zeroes under each where the aging didn't find anything.
You'll also want to put a total off to the right, because everyone wants those. Grand totals at the bottom. Everyone will want those too.
Everything else is dressing, I should think.
xxxxxx 0-30 31-60 61-90 91-120 120+
client 0.00 10.00 0.00 0.00 0.00
client 0.00 0.00 0.00 10.00 2.00
client 0.00 0.00 10.00 0.00 0.00
client -50.00 0.00 0.00 0.00 0.00
This is a pretty standard aging report structure. Hopefully this information will get you close to what you need.
Good luck.
Scott.
