You’re offline. This is a read only version of the page.
3,015
I'm not sure if this is the right forum to be asking this question but I thought that I would give it a shot.
I am trying to create an aging report for a particular customer class, with GP data, using the report builder that comes with report manager.
I am using the ReceivablesManagement database and the receivables transaction table. The fields I need are customer name, customer PO number and aging buckets 30 days apart (up to 120 days).
The part I am struggling with is getting the aging buckets to be thirty days apart.
I am very grateful for any help that is offered.
*This post is locked for comments
If anyone cares to know, I figured this out.
Current
=IIF(DateDiff("d",Fields!Document_Date.Value, Now())<=30, Fields!Current_Trx_Amount.Value, "")
31-60 days
=iif(DateDiff("d",Fields!Document_Date.Value,now())>=31 and DateDiff("d",Fields!Document_Date.Value,Now())<=60,Fields!Current_Trx_Amount.Value,"")
61-90 days
=iif(DateDiff("d",Fields!Document_Date.Value,now())>=61 and DateDiff("d",Fields!Document_Date.Value,Now())<=90,Fields!Current_Trx_Amount.Value,"")
91-120 days
=iif(DateDiff("d",Fields!Document_Date.Value,now())>=91 and DateDiff("d",Fields!Document_Date.Value,Now())<=120,Fields!Current_Trx_Amount.Value,"")
121-150 days
=iif(DateDiff("d",Fields!Document_Date.Value,now())>=121 and DateDiff("d",Fields!Document_Date.Value,Now())<=150,Fields!Current_Trx_Amount.Value,"")
151+ days
=IIF(DateDiff("d",Fields!Document_Date.Value, Now())>=151, Fields!Current_Trx_Amount.Value, "")
If you correlate this with the screenshots it will make sense on how to apply it to your situation.
So I THINK I found where it needs to be put; however, it is giving me an error.
Am I doing this right?
Please excuse my ignorance but where and how do I put in this information. I understand that it is SQL and that it is code to pull different data in different ways back to create a consolidated report. What I don't understand is how to actually implement what you are suggesting.
This is a learning process for me so about 50% of your acronyms don't mean anything to me. Thank you for your patience and assistance.
Hi again,
Here's what I use to produce my summary report.
Kind regards,
Leslie:
select
CASE WHEN
DATEADD ( d , -30, GETDATE() ) > T1.DOCDATE AND
DATEADD ( d , -45, GETDATE() ) <= T1.DOCDATE
THEN
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END
ELSE 0 END
as [31 - 45],
CASE WHEN
DATEADD ( d , -60, GETDATE() ) > T1.DOCDATE AND
DATEADD ( d , -75, GETDATE() ) <= T1.DOCDATE
THEN
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END
ELSE 0 END
as [61 - 75],
CASE WHEN
DATEADD ( d , -90, GETDATE() ) > T1.DOCDATE
THEN
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END
ELSE 0 END
as [Over 90],
CASE WHEN
DATEADD ( d , -30, GETDATE() ) <= T1.DOCDATE
THEN
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END
ELSE 0 END
as [0 - 30],
CASE WHEN
DATEADD ( d , -45, GETDATE() ) > T1.DOCDATE AND
DATEADD ( d , -60, GETDATE() ) <= T1.DOCDATE
THEN
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END
ELSE 0 END
as [46 - 60],
CASE WHEN
DATEADD ( d , -75, GETDATE() ) > T1.DOCDATE AND
DATEADD ( d , -90, GETDATE() ) <= T1.DOCDATE
THEN
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END
ELSE 0 END
as [76 - 90],
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END as [Current_Bal],
CASE T1.RMDTYPAL
WHEN 7 THEN -1*T1.CURTRXAM
WHEN 8 THEN -1*T1.CURTRXAM
WHEN 9 THEN -1*T1.CURTRXAM
ELSE T1.CURTRXAM
END as [Original Amt],
T1.[CUSTNMBR],
T2.[CUSTNAME]
from [TWO]..RM20101 T1 with (nolock)
INNER JOIN [TWO]..RM00101 T2 with (nolock)
on
T2.[CUSTNMBR] = T1.[CUSTNMBR]
where
T1.[CURTRXAM] <> '0'
Hi,
I've also done this one in SmartList Builder - which is easy to switch to Excel. I don't know so much about MR. Victoria may be your best bet there.
I have SLB objects in both summary and detail. I'll be happy to send it to anyone who will send me an e-mail at leslievail@earthlink.net and ask for it.
Kind regards,
Leslie
Victoria Yudin has written the code for you. You can use the view for a SmartList or SRS report.
victoriayudin.com/.../sql-view-for-current-receivables-aging-in-dynamics-gp
-Trevor
I have scanned through the "out of the box" reports and none of them serve the purpose for what we need.
The aging report detail is too much. We don't want prompts.
The aging by customer is by one customer at a time so it is too little.
We just need it to look like this:
Are there other aging reports that I am not aware of that can do this?
I watched this youtube video on how to do it in excel and this doesn't seem too hard. I don't know how to apply this to RB though.
http://www.youtube.com/watch?v=oXLD1CaG30E
Thank you for the help thus far!
I agree with others that using an existing report would be easier. Otherwise you will need to do some serious calculations
You need to create several date starting with the ageing date as date A, then B is A minus 30 days, the C is B minus 30 days, the D is C minus 30 days for as many buckets as you need.
Then for each open item, you have to do a series of If statements
If the date is between A and B, then add to C1 and print in Column 1
if the date is between B and C, then add to C2 and print in column 2
continue until the last one reads
If the date is before (last date) add to column C(last) and print in Column (last)
print your totals out when the customer changes
Add the c totals to the R totals
Repeat above until the list is complete
Print the R(eport) totals
Yes, this is a simple explanation, the if statements really need to say date<=A and Greater than B etc
Have you deployed the SSRS version of reports for this company? If so, you can link those reports to BP.
Because we cannot display that report on Business Portal.
The out of the box aging report in report manager requires the use of prompts to pull the data you want. We don't want our managers to have to use the prompts. We want it to simply display.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,188
Super User 2024 Season 2
#2
Martin Dráb
230,030
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
