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

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