Aging Report

[mswilson16]

Hi all,

I am trying to create an aging report. I have two tables, Client and Accounts. Client stores the client information (name, credit limit etc etc) Accounts stores payments and invoices. I need to be able to create a report that will tell me the clients balance as of a certain date, and also tell me the aging of the balance as of the specified date.

I figure that I will need an if statement to say if the accounts record is of a type of P or I, if P then it goes as a minus to the total, and if I then it goes as a positive.

Then I need to be able to say how the balance ranges over 0-30, 31-60,61-90 days...

If someone could help me that would be great.

Thanks in advance

mswilson

 

[synapsevampire]

If you post specifically what the output might look like, it will be easier to help.

Also your software version should be included with any technical post about any software product, the methods vary across versions.

Createing a formula such as:

numbervar My30dayTotal;
if currentdate-{table.invoicedate} <= 30
and
{table.type} in ["P", "I"] then
(
if {table.type} = "P" then
MyTotal:=MyTotal+{table.amount}
else
MyTotal:=MyTotal-{table.amount}
);

You'll need numerous formulas, but this should get you started. Also you'll need display formulas (footer), and to reset the formulas to zero in the group headers if you want them at a group level.

-k

 

[mswilson16]

Customer Name: Balance:0-30:31-60:61-90:91-120
Mr A:15000:3000:7000:5000:0
Ms B:250:250:0:0:0

I will be using crystal reports 8.5 and it will be linking to my database via odbc. I have very good knowledge of Reporting in Access, but it is not proving to be much use in crystal :-(

Thanks

 

[mswilson16]

Also, there is a twist to this which I forgot to say... I didn't design the database, so not quite sure why it is stored like this. But the due date field in the accounts table is a text field and stores dates like 20050818 instead of 08/18/2005... is there a way to get around this????

Again, I got around it in access but dont have a clue how to in crystal.

 

[dgillz]

Numbertodate(val({YourField}))

Numbertodate() is a UDF that you may need to download from the Business Objects website.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[synapsevampire]

Or try:

stringvar MyDate := totext({table.date},0,"");
cdate(val(left(MyDate,4)),val(mid(MyDate,5,2)),val(mid(MyDate,7,2)))

I suggest avoiding UDF's.

-k

 

[mswilson16]

Any chance of an example SQL statement???

 

[smcnulty2000]

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.

 

[mswilson16]

WOW! thanks smcnulty2000, I am not in work at the minute, but I will try this as soon as I get there...

Thanks again!