7 Replies Latest reply: Aug 4, 2014 1:26 PM by Nicole Adamczyk RSS

    Creating Trial Balance by Account and Period

      I'm trying to create a trial balance (from Microsoft Dynamics GP), which shows the opening balance, debits, credits, net activity, and ending balance by account by period.

       

      Ultimately, I need the structure to look like this:

      YearAccountPeriod IDDebitsCreditsNet ActivityPeriod Balance

      2014

      ####0-BEG5,900,000.006,700,000.00-800,000.00-800,000.00
      2014####

      1-JAN

      8,500,000.00

      9,800,000.00

      -1,300,000.00-2,100,000.00
      2014####2-FEB0.001,200,000.001,200,000.00-900,000.00
      2014####3-MAR0.001,000,000.001,000,000.00100,000.00
      2014####4-APR-100,000.00600,000.00500,000.00600,000.00


      I do know: how to pull in the account details, debits, credits, and calculate the net activity.

       

      I do not know: 1) if I should calculate the net activity and period balance in the script or if I should create an expression in an object, 2) how to calculate a cumulative period balance by account (beginning with period 0 -the beginning balance- and adding the net activity by period, and then displaying that total per period).

       

      Here's the script I've been playing with so far, but the last line of the LOAD statement is giving me issues:

           GP_Activity:

      LOAD

        [Open Year] as [Year],

        [Period ID] as [PeriodID],

        [Account Index] as [Index],

        [Account Number] as [GP Acct],

        Left([Account Number],4) as [GP CC],

        Right([Account Number],4) as [GP Class],

        [Account Description] as [Description],

        [Debit Amount] as [Debits],

        [Credit Amount] as [Credits],

        [Debit Amount]-[Credit Amount] as [Net Activity],

        IF([Period ID]=0,(Sum([Debit Amount])-Sum([Credit Amount])),PEEK([Period Balance],-1)+(Sum([Debit Amount])-Sum([Credit Amount]))) as [Period Balance];

       

      SELECT

        [Open Year],

        [Period ID],

        [Account Index],

        [Account Number],

        Left([Account Number],4) as [GP CC],

        Right([Account Number],4) as [GP Class],

        [Account Description],

        [Debit Amount],

        [Credit Amount],

      FROM dbo."AccountTransactions"

      ORDER BY [Account Number],[Period ID];

       

      Thank you in advance for your time