Share
ReportHow can i import aging report in Excel in Dynamics Gp-2013
*This post is locked for comments
You’re offline. This is a read only version of the page.
6,825
How can i import aging report in Excel in Dynamics Gp-2013
*This post is locked for comments
In order to get an acceptable format you will need to make some changes in report writer. Simply changing the output to a .csv or tab delimited file will not be enough. You will need to take out the page header and footer information, underlines and other symbols that would not yield a 'clean' file for Excel. Alternatively, you could create a SmartList. I use the following SQL statement for my detail RM trial balance based on document date. You can modify this statement to create any aging buckets you need.
/*
Leslie Vail 02/05/2011
This SQL statement creates a detailed RM trial balance aged by document date
*/
SELECT 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, -31, 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, -46, 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, -61, 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, -76, 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
WHEN Dateadd (d, -91, 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 [91 +]
, 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]
, T1.[docnumbr]
, T1.[rmdtypal]
, T1.[duedate]
, T1.[docdate]
, T1.[cspornbr]
, T1.[slprsnid]
, T1.[pymtrmid]
, T1.[shipmthd]
, Isnull(T2.[custname], '') AS CustomerName
, T3.[soptype]
, Isnull(T3.[sopnumbe], '') AS SOPNumber
, T3.[origtype]
, Isnull(T3.[orignumb], '') AS OriginatingSOPNumber
, Isnull(T3.[docid], '') AS DocumentID
, Isnull(T3.[glpostdt], '1900-01-01') AS glpostdt
, Isnull(T3.[quotedat], '1900-01-01') AS quotedat
, Isnull(T3.[quoexpda], '1900-01-01') AS quoexpda
, Isnull(T3.[ordrdate], '1900-01-01') AS ordrdate
, Isnull(T3.[invodate], '1900-01-01') AS invodate
, Isnull(T3.[backdate], '1900-01-01') AS backdate
, Isnull(T3.[retudate], '1900-01-01') AS [retudate]
, Isnull(T3.[reqshipdate], '1900-01-01') AS [reqshipdate]
, Isnull(T3.[fufildat], '1900-01-01') AS [fufildat]
, Isnull(T3.[actlship], '1900-01-01') AS [actlship]
, Isnull(T3.[discdate], '1900-01-01') AS [discdate]
FROM [TWO]..rm20101 T1 WITH (nolock)
LEFT JOIN [TWO]..rm00101 T2 WITH (nolock)
ON T2.[custnmbr] = T1.[custnmbr]
LEFT JOIN [TWO]..sop30200 T3 WITH (nolock)
ON T3.[sopnumbe] = T1.[docnumbr]
LEFT JOIN [TWO]..sop10106 T4 WITH (nolock)
ON T4.[soptype] = T3.[soptype]
AND T4.[sopnumbe] = T3.[sopnumbe]
WHERE T1.[curtrxam] <> '0'
Kind regards,
Leslie
Hi Zulfi,
The Aging report it does not come exactly as it is defined in the GP report writer when it is exported as tab/comma delimited file. You have to modify and align your report to get the desired format in the excel.
Similar kind of requirement has already been discussed in a thread, where Leslie has given the steps to perform. Refer the given link.
community.dynamics.com/.../81889.aspx
The below given link is a sample, you have to modify your report accordingly.
reese-consulting.com/export-the-dynamics-gp-receivables-historical-aged-trial-balance-to-excel
Go to Destination window and set File format as Comma delimited file. Give the file name and then export. Open the exported csv file through excel.
zulfi, you can choose file type as "comma delimited file" in report destination window while printing the report from GP.
Hi,
Can you please explain in detail about your question ? You requirement is to import/export ?
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,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (