Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

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



1 comment:

  1. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you can be to get the new PROGRAMMED blank ATM card that is capable of
    hacking into any ATM machine,anywhere in the world. I got to know about 
    this BLANK ATM CARD when I was searching for job online about a month 
    ago..It has really changed my life for good and now I can say I'm rich and 
    I can never be poor again. The least money I get in a day with it is about 
    $50,000.(fifty thousand USD) Every now and then I keeping pumping money 
    into my account. Though is illegal,there is no risk of being caught 
    ,because it has been programmed in such a way that it is not traceable,it 
    also has a technique that makes it impossible for the CCTVs to detect 
    you..For details on how to get yours today, email the hackers on : (
    atmmachinehackers1@gmail.com ). Tell your 
    loved once too, and start to live large. That's the simple testimony of how 
    my life changed for good...Love you all ...the email address again is ;
    atmmachinehackers1@gmail.com

    ReplyDelete