Saved Search to Re-create A/R Aging Summary Report
On the Criteria tab Standard subtab,
Filter Description Formula Account Type>> is Accounts Receivable Posting >> is true
On the Results tab >> Columns subtab, kindly add the following:
Field Summary Type Function Formula When Ordered By Field Custom Label Summary Label Date Type Document Number Name Group Days Overdue Amount Formula (Currency) Sum nvl(case when trunc({today})-nvl({duedate},{trandate}) <=0 then case when {debitamount}>0 then {amountremaining} else -{amountremaining} end end,0) Current Formula (Currency) Sum nvl(case when trunc({today})-nvl({duedate},{trandate}) between 1 and 30 then case when {debitamount}>0 then {amountremaining} else -{amountremaining} end end,0) 1-30 days Formula (Currency) Sum nvl(case when trunc({today})-nvl({duedate},{trandate}) between 31 and 60 then case when {debitamount}>0 then {amountremaining} else -{amountremaining} end end,0) 31-60 days Formula (Currency) Sum nvl(case when trunc({today})-nvl({duedate},{trandate}) between 61 and 90 then case when {debitamount}>0 then {amountremaining} else -{amountremaining} end end,0) 61-90 days Formula (Currency) Sum nvl(case when trunc({today})-nvl({duedate},{trandate}) > 90 then case when {debitamount}>0 then {amountremaining} else -{amountremaining} end end,0) Over 90 days Formula (Currency) Sum nvl(case when {debitamount}>0 then {amountremaining} else -{amountremaining} end,0) Total
Note: The Search Totals would only match totals as of today's date. Saved Searches does not have a field similar to the As Of Filter of the Aging Report.
I will however be confirming if this works on your end.
On the Criteria tab Standard subtab,
Filter
Description
Formula
Account Type>> is Accounts Receivable
Posting >> is true
On the Results tab >> Columns subtab, kindly add the following:
Field
Summary Type
Function
Formula
When Ordered By Field
Custom Label
Summary Label
Date
Type
Document Number
Name Group
Days Overdue
Amount
Formula (Currency) Sum nvl(case when trunc({today})-nvl({duedate},{trandate}) <=0 then case when {debitamount}>0 then {amountremaining} else -{amountremaining} end end,0) Current
Formula (Currency) Sum nvl(case when trunc({today})-nvl({duedate},{trandate}) between 1 and 30 then case when {debitamount}>0 then {amountremaining} else -{amountremaining} end end,0) 1-30 days
Formula (Currency) Sum nvl(case when trunc({today})-nvl({duedate},{trandate}) between 31 and 60 then case when {debitamount}>0 then {amountremaining} else -{amountremaining} end end,0) 31-60 days
Formula (Currency) Sum nvl(case when trunc({today})-nvl({duedate},{trandate}) between 61 and 90 then case when {debitamount}>0 then {amountremaining} else -{amountremaining} end end,0) 61-90 days
Formula (Currency) Sum nvl(case when trunc({today})-nvl({duedate},{trandate}) > 90 then case when {debitamount}>0 then {amountremaining} else -{amountremaining} end end,0) Over 90 days
Formula (Currency) Sum nvl(case when {debitamount}>0 then {amountremaining} else -{amountremaining} end,0) Total
Note: The Search Totals would only match totals as of today's date. Saved Searches does not have a field similar to the As Of Filter of the Aging Report.
I will however be confirming if this works on your end.
Please let me know how you go.
Kind regards,
Sumanna Tandukar
Have you visited our Support Portal lately? http://support.jcurve.com.au