Home » Developer & Programmer » Reports & Discoverer » Aging report logic
Aging report logic [message #288156] Sat, 15 December 2007 03:00 Go to next message
rafi_aamiri
Messages: 34
Registered: September 2005
Member
Hi,

There is an requirement where i need to generate report on aging..

ie.. amount unpaid by the customer to be identified on different aging

0-15 days 16-30 days 31-45 days 56-90 days

so the output of the report show be like above on different aging period.

this logic need to be implemented using decode function in a single select statement

if not do let me know the other ways to implement in oracle reports.

pls help me in this regard

Re: Aging report logic [message #288188 is a reply to message #288156] Sat, 15 December 2007 10:41 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Indeed, decode is the way to do bucketing/aging reports. And what is your question?
Re: Aging report logic [message #288196 is a reply to message #288188] Sat, 15 December 2007 13:27 Go to previous messageGo to next message
rafi_aamiri
Messages: 34
Registered: September 2005
Member
pls do let me know how to implement the decode in select statement

thanks...
Re: Aging report logic [message #288206 is a reply to message #288196] Sat, 15 December 2007 20:15 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Hi

I have tried to find the solution of your requirement.
Interestingly i have not used DECODE in the solution as REPORTS has its own capability in matrix report.

But if you are interested to use DECODE then you have to go SQL forum.

Here is solution for you
===========================
As you have not provided any table and rows, so i have decided to use emp table in a way that

sal = amount due
hiredate = readjusted so that the hiredate should be for year 2007


select ename , sal, 
to_date(to_char(hiredate, 'DD-MON') ||'-07', 'DD-MON-YY') due, trunc(sysdate)-to_date(to_char(hiredate, 'DD-MON') ||'-07', 

'DD-MON-YY') age
from emp
/

ENAME             SAL DUE              AGE
---------- ---------- --------- ----------
SMITH             800 17-DEC-07         -1
ALLEN            1600 20-FEB-07        299
WARD             1250 22-FEB-07        297
JONES            2975 02-APR-07        258
MARTIN           1250 28-SEP-07         79
BLAKE            2850 01-MAY-07        229
CLARK            2450 09-JUN-07        190
SCOTT            3000 19-APR-07        241
KING             5000 17-NOV-07         29
TURNER           1500 08-SEP-07         99
ADAMS            1100 23-MAY-07        207
JAMES             950 03-DEC-07         13
FORD             3000 03-DEC-07         13
MILLER           1300 23-JAN-07        327


The negative age shown for Smith so i am using ABS function.



You have made group over range of 15. I will use over range of 50.

Now see the attached file

  • Attachment: test4.RDF
    (Size: 56.00KB, Downloaded 217 times)
Re: Aging report logic [message #288207 is a reply to message #288156] Sat, 15 December 2007 20:25 Go to previous message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
I have worked a little bit more in order to have logical headings. So here is modified version.


By the way if you want to use decode n SQL then you can do it with following pseducode
1) Calculate age
2) ceil(age / 15) -- as 15 was your original range
3) use deocde on ceil(age / 15) i.e.

decode( ceil(age / 15),
1, '0-15 days',
2, '16-30 days',
3, '31-45 days',
4, '46-60 days',
5, '61-75 days',
'75+ days')


Good luck!






  • Attachment: test5.RDF
    (Size: 52.00KB, Downloaded 218 times)
Previous Topic: Adding LOV to a parameter
Next Topic: Problem during Character Mode Report Generation
Goto Forum:
  


Current Time: Wed Dec 07 04:59:35 CST 2016

Total time taken to generate the page: 0.10732 seconds