Hi,
I would like to respond to your questions -
Victoria has done a wonderful work and have created the following SQL view for the aging report. If you would like you can run this report for any company. If there is a requirement to add multiple companies, please add an additional column (1st column) saying DB and hard code the name of the company there like TWO as "Database_Name". Once done you might have to change the tables to something like TWO..RM20101 to read data from many databases. Then union them all. Once done, you may apply filters in SSRS for a company or a range of companies to generate this report.
The query written by Victoria is listed below or you may refer to http://victoriayudin.com/2012/01/25/sql-view-for-current-receivables-aging-in-dynamics-gp/
create view view_Current_Receivables_Aging_Summary
as
select
CM.CUSTNMBR Customer_ID, CM.CUSTNAME Customer_Name,
CM.PYMTRMID Customer_Terms, CM.CUSTCLAS Customer_Class,
CM.PRCLEVEL Price_Level,
sum(case
when RM.RMDTYPAL < 7 then RM.CURTRXAM
else RM.CURTRXAM * -1
end) Total_Due,
sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) < 31
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) < 31
and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end) [Current],
sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 31 and 60
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 31 and 60
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end) [31_to_60_Days],
sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 61 and 90
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 61 and 90
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end) [61_to_90_Days],
sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) > 90
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) > 90
and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end) [91_and_Over],
CS.LASTPYDT Last_Payment_Date,
CS.LPYMTAMT Last_Payment_Amount
from RM20101 RM
inner join RM00101 CM
on RM.CUSTNMBR = CM.CUSTNMBR
inner join RM00103 CS
on RM.CUSTNMBR = CS.CUSTNMBR
where RM.VOIDSTTS = 0 and RM.CURTRXAM <> 0
group by CM.CUSTNMBR, CM.CUSTNAME, CM.PYMTRMID, CM.CUSTCLAS,
CM.PRCLEVEL, CS.LASTPYDT,CS.LPYMTAMT
Share
Report
All responses (
Answers (
22,647
