on 07-30-2016 10:43 AM
Dear Experts,
I am making a simple Customer Aging report in which I need sum of total open invoices also I want to make groups of total like Sum of doctotal between 0 to 30 days of docdate.
suppose customer A has open invoices for March, April, May, June and July month then I wanted doctotal for 0 to 30, 31 to 60 days just like system Againg Report.
I tried putting subreport with condition of DateDiff >=0 and <=30 but no luck.
Thanks in advance,
Ravi
Hi Ravi,
To do this report, create group on Customer first then write below formulas :
0 - 30 days
if {docdate}>= dateadd('d',-30, currentdate) and {docdate} <= Currentdate Then {DocAmt}
31 - 60 days
if {docdate}>= dateadd('d',-60, currentdate) and {docdate} <= dateadd('d',-30, currentdate) Then {DocAmt}
61 - 90
if {docdate}>= dateadd('d',-90, currentdate) and {docdate} <= dateadd('d',-60, currentdate) Then {DocAmt}
above 90
if {docdate}> dateadd('d',-90, currentdate) Then {DocAmt}
Now place these formulas in your columns (Note : if you want to create more age buckets then write formulas accordingly) and insert totals on {DocAmt} at group level.
There is no need for creating sub reports.
Thanks,
Sastry
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
One option is to create a formula that returns an age bucket label for each invoice.
Insert a CrossTab where Customer is row, Age Bucket is column, and value field is the invoice unpaid amount.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.