web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Dynamics GP Essentials / General Ledger Journals wit...

General Ledger Journals with Analytical Accounting Details (with Drill Down Capabilities from Excel) –SQL Script

Views (1346)
Mahmoud Saadi Profile Picture Mahmoud Saadi 32,738
In a previous post and series (SQL Scripts for Dynamics GP), I have provided several useful scripts that was amazingly downloaded by the GP community. It is is such a pleasure to give back to the community from whom we all learn. 

Today, I am adding a new SQL script to the library to provide an added value for the report, which is represented with the drill down capabilities. The previous SQL script is General Ledger Journals with AA Details. Thew new one adds further enhancement (drill down link to Dynamics GP, from Excel)


--Tables Included:

--AAG30000 | Analytical Accounting GL Header
--AAG30001 | Analytical Accounting GL Distribution
--AAG30002 | Analytical Accounting GL Assignment
--AAG30003 | Analytical Accounting GL Code
--AAG00401 | Analytical Accounting Transaction Dimension
--GL00100  | Account Master
--GL00105  | Account Index MASTER

SELECT  YEAR1 ,
        JRNENTRY ,
        B.ACTINDX ,
        H.ACTNUMST ,
        G.ACTDESCR ,
        A.aaTRXType ,
        aaGLTRXSource ,
        aaTRXSource ,
        GLPOSTDT ,
        B.DEBITAMT AS TotalDebit ,
        B.CRDTAMNT AS TotalCredit ,
        C.DEBITAMT AS aaDebit ,
        C.CRDTAMNT AS aaCredit ,
        aaTrxDim ,
        aaTrxDimDescr ,
        aaTrxDimDescr2 ,
        aaTrxDimCode ,
        aaTrxDimCodeDescr ,
        aaTrxDimCodeDescr2 ,
        CONCAT('=HYPERLINK("dgpp://DGPB/?Db=&Srv=DESKTOP-APSQHSU&Cmp=GP16R&Prod=0&Act=OPEN&Func=OpenJournalInq&JRNENTRY=',
               RTRIM(LTRIM(JRNENTRY)), '&RCTRXSEQ=0&YEAR1=', YEAR(GLPOSTDT),
               '&TRXDATE=',CONVERT(VARCHAR(10), GLPOSTDT, 101), '"',
               ',"Link")') AS DrillDownLink
FROM    [AAG30000] AS A
        LEFT OUTER JOIN [AAG30001] AS B ON A.[aaGLHdrID] = B.[aaGLHdrID]
        LEFT OUTER JOIN [AAG30002] AS C ON B.[aaGLHdrID] = C.[aaGLHdrID]
                                           AND B.[aaGLDistID] = C.[aaGLDistID]
        INNER JOIN [AAG30003] AS D ON C.[aaGLAssignID] = D.[aaGLAssignID]
                                      AND C.[aaGLDistID] = D.[aaGLDistID]
                                      AND C.[aaGLHdrID] = D.[aaGLHdrID]
        INNER JOIN [AAG00401] AS E ON D.[aaTrxDimID] = E.[aaTrxDimID]
                                      AND D.[aaTrxCodeID] = E.[aaTrxDimCodeID]
        INNER JOIN [AAG00400] AS F ON E.[aaTrxDimID] = F.[aaTrxDimID]
        LEFT OUTER JOIN dbo.GL00100 AS G ON B.ACTINDX = G.ACTINDX
        LEFT OUTER JOIN dbo.GL00105 AS H ON G.ACTINDX = H.ACTINDX


The drill down link to Dynamics GP requires the following parameters (for the Journal Entry specifically)
  • SQL Server name
  • Database name
  • Journal Entry
  • Transaction Date
Here is the output:


 






Further interesting details are presented in MVP Mark Polino book, Drilling Back to Source Data in Dynamics GP using Dashboard.


Best Regards,
Mahmoud M. AlSaadi




This was originally posted here.

Comments

*This post is locked for comments