SQL Script for Budgets vs. Actuals in Amounts in Analytical Accounting – By Dimension Code

| 06/16/2011 | 7 Comments

Hi all

Today, I am posting a script for analyzing Budgets vs. Actuals (using Amounts) in Analytical Accounting. However, there are two methods of analyzing the budget information in Analytical Accounting. They are:

  • Node Level
  • Dimension Code Level

This script is for viewing Budgets vs. Actuals at Dimension Code level. I will be posting the script for analyzing budgets at Node level soon.

/******************************************************************
Created June 16, 2011 by Sivakumar Venkataraman - Interdyn AKA
This script is to return budget vs. actual values from AA module
 
Tables used: 
 - AAG00905 - AA Budget Tree Account Balance
 - AAG00903 - AA Budget Master
 - AAG00902 - AA Budget Tree Trx Dim Code Master
 - AAG00900 - AA Budget Tree Master
 - AAG00401 - aaTrxDimCodeSetp
 - AAG30000 - aaGLHdr
 - AAG30001 - aaGLDist
 - AAG30002 - aaGLAssign
 - AAG30003 - aaGLCode
 - GL00100 - Account Master
 - GL00102 - Account Category Master
 - GL00105 - Account Index Master
 - SY40100 - Period Setup 
******************************************************************/
SELECT  BvA.aaBudget ,
        BvA.glFiscalYear ,
        BvA.glFiscalPeriod ,
        Bva.glAccountCategory ,
        BvA.glAccountNo ,
        BvA.glAccountDesc ,
        BvA.aaTrxDim ,
        BvA.aaTrxDimCode ,
        BvA.aaTrxDimCodeDescr ,
        BvA.aaActualAmount AS aaActualAmount ,
        BvA.aaBudgetAmount AS aaBudgetAmount ,
        ( Bva.aaBudgetAmount - BvA.aaActualAmount ) AS aaVariance ,
        CASE WHEN BvA.aaBudgetAmount <> 0
             THEN CONVERT(NUMERIC(5, 2), ROUND(( ( ( Bva.aaBudgetAmount
                                                     - BvA.aaActualAmount )
                                                   / BvA.aaBudgetAmount )
                                                 * 100 ), 2))
             ELSE CONVERT(NUMERIC(19, 2), BvA.aaBudgetAmount)
        END AS aaVariancePercent
FROM    ( SELECT  DISTINCT
                    B.aaBudget ,
                    RTRIM(B.aaBudgetDescr) AS aaBudgetDescr ,
                    J.YEAR1 AS glFiscalYear ,
                    CASE WHEN J.PERIODID <> 0 THEN {fn MONTHNAME(J.PERIODDT)}
                         ELSE 'BBF'
                    END AS glPeriodName ,
                    CASE WHEN J.PERIODID <> 0
                         THEN REPLICATE('0', 2 - LEN(J.PERIODID))
                              + ( LTRIM(RTRIM(STR(J.PERIODID))) + ':'
                                  + ( LEFT({fn MONTHNAME(J.PERIODDT)}, 3) )
                                  + '''' + SUBSTRING(LTRIM(RTRIM(STR(J.YEAR1))),
                                                     3, 2) )
                         ELSE '00:BBF''' + SUBSTRING(LTRIM(RTRIM(STR(J.YEAR1
                                                              - 1))), 3, 2)
                    END AS glFiscalPeriod ,
                    RTRIM(H.ACTNUMST) AS glAccountNo ,
                    RTRIM(G.ACTDESCR) AS glAccountDesc ,
                    RTRIM(I.ACCATDSC) AS glAccountCategory ,
                    RTRIM(E1.aaTrxDim) AS aaTrxDim ,
                    RTRIM(E.aaTrxDimCode) AS aaTrxDimCode ,
                    RTRIM(E.aaTrxDimCodeDescr) AS aaTrxDimCodeDescr ,
                    A.Balance AS aaBudgetAmount ,
                    ISNULL(( SELECT SUM(C1.DEBITAMT - C1.CRDTAMNT)
                             FROM   dbo.AAG30000 A1
                                    INNER JOIN dbo.AAG30001 B1 ON A1.aaGLHdrID = B1.aaGLHdrID
                                    INNER JOIN dbo.AAG30002 C1 ON B1.aaGLHdrID = C1.aaGLHdrID
                                                              AND B1.aaGLDistID = C1.aaGLDistID
                                    INNER JOIN dbo.AAG30003 D1 ON C1.aaGLHdrID = D1.aaGLHdrID
                                                              AND C1.aaGLDistID = D1.aaGLDistID
                                                              AND C1.aaGLAssignID = D1.aaGLAssignID
                                    INNER JOIN dbo.SY40100 E1 ON A1.GLPOSTDT >= E1.PERIODDT
                                                              AND A1.GLPOSTDT <= E1.PERDENDT
                             WHERE  E1.SERIES = 0
                                    AND E1.PERIODDT = A.PERIODDT
                                    AND B1.ACTINDX = A.ACTINDX
                                    AND D1.aaTrxCodeID = D.aaTrxDimCodeID
                           ), 0) AS aaActualAmount
          FROM      dbo.AAG00905 A
                    INNER JOIN dbo.AAG00903 B ON A.aaBudgetID = B.aaBudgetID
                    INNER JOIN dbo.AAG00902 D ON B.aaBudgetTreeID = D.aaBudgetTreeID
                                                 AND A.aaCodeSequence = D.aaCodeSequence
                    INNER JOIN dbo.AAG00401 E ON E.aaTrxDimCodeID = D.aaTrxDimCodeID
                    INNER JOIN dbo.AAG00400 E1 ON E.aaTrxDimID = E1.aaTrxDimID
                    INNER JOIN dbo.GL00100 G ON A.ACTINDX = G.ACTINDX
                    INNER JOIN dbo.GL00105 H ON A.ACTINDX = H.ACTINDX
                    INNER JOIN dbo.GL00102 I ON G.ACCATNUM = I.ACCATNUM
                    INNER JOIN dbo.SY40100 J ON A.PERIODDT = J.PERIODDT
          WHERE     J.SERIES = 0
        ) BvA

Hope this script helps the community…

Until next post!

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Tags: , ,

Category: Analytical Accounting, Dynamics, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008

About the Author ()

  • Linda Brock

    Hi, This script is a lovely idea and would be a great alternative to a Management Reporter report. I’ve created a budget and posted actuals against the codes in my budget. However, when I run this script it returns no rows. No errors – just no data. Do you have any suggestions? Thanks!

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • Sivakumar Venkataraman

      Linda – Can I remote into your environment to troubleshoot this issue? Might be much easier than a couple of responses. :) I am available in the mornings (EST). Let me know and send me an email to veeyeskayATgmailDOTcom and I will set up a webex and send u the link to join. :)

      VA:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      VA:F [1.9.22_1171]
      Rating: 0 (from 0 votes)