Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Wednesday, November 9, 2016

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



No comments:

Post a Comment