suggestions on creating an aging report?

[epelz]

Yes I am new to Alpha software and I am trying to write my first desktop application in version 10.5 to create an aging report for open invoices. In searching thru the date fuctions that are currently availble I am not finding anything already written to accomplish this task. I was hoping to get suggestions or guidance on how to separate the "current" from the 30, 60, 90 and 120 day totals. Is there an easy way or should I just work on writing my own function to handle this? Also I have heard that there is a mentors program but I have not been able to find out any details or info about the program. If anyone can shed any light on that as well it would be greatly appreciated.

A struggling newbie
Ed

[Keith Hubert]

Re: suggestions on creating an aging report?

Hi Ed,

I was hoping to get suggestions or guidance on how to separate the "current" from the 30, 60, 90 and 120 day totals. Is there an easy way or should I just work on writing my own function to handle this?

There is no easy way that I know of. I did work on such a report in C Basic many moons ago so would be pleased to give you a hand.

Let me where you are up to so far.

[Bob Whitaker]

Re: suggestions on creating an aging report?

It's a relatively simple thing to achieve. You need calculated field for each age category - for example for invoices in the 30-60 day range the calculation would be if(date()-invoice_date>30 .and. date()-Invoice_date<60,value_of_invoice,0). On the report set the format for each calculate field to be blank if zero. Arrange your calculated fields in neat columns and summarize the totals at the foot of the report.
Bob

[jacobr]

Re: suggestions on creating an aging report?

Bob,
This need to be done through xbasic code using loop or there is a one line summarize operation. I use dbf table of Alpha.

[Bob Whitaker]

Re: suggestions on creating an aging report?

No it can all be done in the report designer by creating calculated fields.

Supposing your dbf table contains a field with the invoice date - lets call it Invdate and a field with the invoice amount lets call that Invamount then if you want an ageing report that shows the following age categories
Less than 30 days
30 - 60 days
60 - 90 days
Over 90 days

You need to create 4 calculated fields thus

lessthan30 = if((date()-invdate)<=30,invamount,0) in other words if the invoice date is less than or equal to30 days from the current date the value of this calculated field would be the invoice amount if not then the value is zero
days30to60 = if((date()-invdate)>30 .and. (date()-Invdate)<=60,invamount,0)
days60to90 = if((date()-invdate)>60 .and. (date()-Invdate)<=90,invamount,0)
over90 = if((date()-invdate)>90,invamount,0)

Then each line of the report would have the following

Customer Invoice Number Invdate calc->lessthan30 calc->days30to60 calc->days60to90 calc->over90

In the properties for each of the calculate fields set the format to be "blank if zero"

At the foot of the report you can drag each of the calculate fields and set the format to show the total of each field

Hope that helps - if you are still struggling send me a private message if you wish.

Bob

[jacobr]

Re: suggestions on creating an aging report?

Bob,

Thankyou verymuch for that detail reply.

What if I need to have only a summary report, against each customer one row & 5 columns as below
Ac_code <=30 31-60 61-90 >90 Total
--------- ------- ------ ------ ------ ------
A1 100 200 300
A2 200 150 100 450

[Tom Cone Jr]

Re: suggestions on creating an aging report?

An easy way to do the summary report is to base the report on a summary table.

You can easily build the summary table using a batch summarize operation that sums the invoice amounts over a range of dates that are specified by a condition expression. You'd sum the same field (invoice amount) several times, using different date range conditions each time. This would all be in the same summarize operation, and would not have to be run separately for each date range.

[Bob Whitaker]

Re: suggestions on creating an aging report?

Tom's suggestion would work fine but a quicker and simpler method would be to put a group break based on the account number in the report.
Summarize the totals for each group in the group footer and once you are happy with the accuracy of the results switch off the detail section of the report and just show the summary totals for each group / account number.
Bob

[Al Buchholz]

Re: suggestions on creating an aging report?

Tom's suggestion would work fine but a quicker and simpler method would be to put a group break based on the account number in the report.
Summarize the totals for each group in the group footer and once you are happy with the accuracy of the results switch off the detail section of the report and just show the summary totals for each group / account number.
Bob

Bob and Tom's options are both good options.

I would add to Bob's in that a conditional object in the detail section could hold all of the fields in the detail section and be turned on and off with a variable .

This allows the same report to produce a detail or summary report with the flick of a switch...

Another option is to pre-summarize the data to the levels you would drill to and use a report drill down or browse drill down to see more detail of data...

There are certainly a world of options..

[jacobr]

Re: suggestions on creating an aging report?

Tom,
I tried this method , but struck in doing for different period as I could not do summarize with multiple condition (filter).!
Pls help

[JetLi]

Re: suggestions on creating an aging report?

Attached is a structure of the aged_inv and invoiceflow dbf table
Renny, it's in the book xbasic 4 every 1 by Peter Wayne,
have modified some and came out with this code
'Date Created: 07-Apr-2012 08:35:51 PM
'Last Updated: 23-Apr-2012 04:22:53 PM
'Created By : ALPHA
'Updated By : ALPHA
option strict
dim invoice as p
dim aged as p
dim age as n
dim inv_date as d
dim query.order as c
dim query.description as c
dim query.options as c
dim query.filter as c

aged = table.open("aged_inv",FILE_RW_EXCLUSIVE)
aged.zap(.t.)
aged.close()
aged = table.open("aged_inv",FILE_RW_SHARED)
invoice = table.open("invoiceflow",FILE_RO_SHARED)
query.description="Balances greater than 0"
query.order=""
query.options=""
query.filter="Inv_Bal > 0.01"
invoice.query_create()

invoice.fetch_first()
while .not. invoice.fetch_eof()
aged.enter_begin()
age = date() - invoice.due_date
select
case age >=90
aged.ninety = invoice.inv_bal
case age >=60
aged.sixty = invoice.inv_bal
case age >=30
aged.thirty = invoice.inv_bal
case else
aged.current = invoice.inv_bal
end select
aged.inv_no = invoice.refnum
aged.custid = invoice.custid
aged.due_date=invoice.due_date
aged.enter_end(.t.)
invoice.fetch_next()
end while
invoice.close()
aged.close()
preview_report("aging_report")
end

[Tom Cone Jr]

Re: suggestions on creating an aging report?

Renny, I took a closer look at this after your post, and find, much to my embarrassment, that you're correct. The summarize operation doesn't support multiple condition expressions based on the same field. Mea Culpa.

-- tom

[jacobr]

Re: suggestions on creating an aging report?

Thankyou Bob,
I was really breaking my head for a day. With your suggestions I could almost achieve my target. And as Alan noted correctly, I can have a drill down with the same report.
Thank you all.
Renny

[jacobr]

Re: suggestions on creating an aging report?

What is the xbasic command to print only the group total?

[Al Buchholz]

Re: suggestions on creating an aging report?

I would add to Bob's in that a conditional object in the detail section could hold all of the fields in the detail section and be turned on and off with a variable .

This allows the same report to produce a detail or summary report with the flick of a switch...

What is the xbasic command to print only the group total?

as previously mentioned....