You’re offline. This is a read only version of the page.
Hey everyone - does anyone have a sql view tying in MDA tables to give a view of GL distributions by projects? If you have something close i can make changes and post it here to this forum. Thanks!
*This post is locked for comments
Hi Mahmoud - yes thank you very much. I'm currently still working on using your view to build a report in report builder.
You are most welcome Mark
Let me know if you need any further assistance,
Thank You Mahmoud. This is fantastic. I will be working this week using your view as a starter and will post any changes that I may have made if needed.
Furthermore, here is the DTA tables
Hello Mark
Here is a script that provides the journal entry details for Multi-Dimensional Anlaysis
Share
Report
All responses (
Answers (
SELECT CASE A.DTASERIES
WHEN 2 THEN 'Financial'
WHEN 3 THEN 'Sales'
WHEN 4 THEN 'Purchasing'
WHEN 5 THEN 'Inventory'
WHEN 6 THEN 'Payroll – USA'
WHEN 7 THEN 'Project'
ELSE ''
END AS DTA_Series ,
JRNENTRY AS JournalEntry,
D.ACTNUMST AS AccountNumber,
C.ACTDESCR AS AccountDescription,
A.GROUPID AS DTA_GroupID,
CODEID AS DTA_CodeID,
GROUPAMT ,
CASE
WHEN CODEAMT > 0 THEN CODEAMT
ELSE 0
END AS Debit,
CASE
WHEN CODEAMT < 0 THEN CODEAMT
ELSE 0
END AS Credit,
A.TRXDATE AS TransactionDate,
A.DTAREF AS DTA_Reference,
DTA_GL_Reference ,
A.DOCNUMBR AS OriginalDocumentNumber ,
A.RMDTYPAL ,
CASE PSTGSTUS
WHEN 1 THEN 'Unposted'
WHEN 2 THEN 'Posted'
ELSE ''
END AS DTA_PostingStatus ,
B.DOCNUMBR ,
B.RMDTYPAL ,
POSTDESC AS PostingDescription,
DTAQNTY
FROM dbo.DTA10100 AS A
LEFT OUTER JOIN dbo.DTA10200 AS B ON A.ACTINDX = B.ACTINDX
AND A.DOCNUMBR = B.DOCNUMBR
AND A.DTAREF = B.DTAREF
AND A.DTASERIES = B.DTASERIES
AND A.GROUPID = B.GROUPID
AND A.SEQNUMBR = B.SEQNUMBR
LEFT OUTER JOIN dbo.GL00100 AS C ON A.ACTINDX = C.ACTINDX
LEFT OUTER JOIN dbo.GL00105 AS D ON C.ACTINDX = D.ACTINDX
Here is the data set view
Let me know if this is what you are looking for,
Your feedback is highly appreciated,
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,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156