mechanism

SQL view for Dynamics GP open year GL trial balance


General Ledger trial balances have been a popular request lately. The view below will return a General Ledger trial balance showing the monthly net change for the first open year in your Dynamics GP. I’ve hard-coded month names to be the calendar year, if you are on a different fiscal year, you will need to update these.

It’s been suggested to me that it would be helpful to see a sample of the results when I post code for views. If you click on the picture below, you’ll see a bigger version:

GL Trial Balance results

Some additional resources:

create view view_Open_Yr_GL_Trial_Balance
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Created March 17, 2014 by Victoria Yudin
--     Flexible Solutions, Inc.
-- For updates please see http://wp.me/pkuMh-1hx
-- Only returns the first open year in GP
-- Month names in columns use calendar fiscal year
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
g.YEAR1 [Year],
a.ACTNUMST Account,
g.ACTDESCR [Description],
c.ACCATDSC Category,
sum(case g.PERIODID when 0
	then g.PERDBLNC else 0 end) Beginning_Balance,
sum(case g.PERIODID when 1
	then g.DEBITAMT-g.CRDTAMNT else 0 end) January,
sum(case g.PERIODID when 2
	then g.DEBITAMT-g.CRDTAMNT else 0 end) February,
sum(case g.PERIODID when 3
	then g.DEBITAMT-g.CRDTAMNT else 0 end) March,
sum(case g.PERIODID when 4
	then g.DEBITAMT-g.CRDTAMNT else 0 end) April,
sum(case g.PERIODID when 5
	then g.DEBITAMT-g.CRDTAMNT else 0 end) May,
sum(case g.PERIODID when 6
	then g.DEBITAMT-g.CRDTAMNT else 0 end) June,
sum(case g.PERIODID when 7
	then g.DEBITAMT-g.CRDTAMNT else 0 end) July,
sum(case g.PERIODID when 8
	then g.DEBITAMT-g.CRDTAMNT else 0 end) August,
sum(case g.PERIODID when 9
	then g.DEBITAMT-g.CRDTAMNT else 0 end) September,
sum(case g.PERIODID when 10
	then g.DEBITAMT-g.CRDTAMNT else 0 end) October,
sum(case g.PERIODID when 11
	then g.DEBITAMT-g.CRDTAMNT else 0 end) November,
sum(case g.PERIODID when 12
	then g.DEBITAMT-g.CRDTAMNT else 0 end) December,
sum(g.PERDBLNC) Ending_Balance

from GL11110 g  --GL

inner join GL00102 c  --categories
	on g.ACCATNUM = c.ACCATNUM

inner join GL00105 a  --for account number
	on g.ACTINDX = a.ACTINDX

where g.ACCTTYPE = 1
	  and g.YEAR1 = (select min(YEAR1) from GL11110)

group by g.YEAR1, g.ACTDESCR, a.ACTNUMST, c.ACCATDSC

go
grant select on view_Open_Yr_GL_Trial_Balance to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

25 Responses to “SQL view for Dynamics GP open year GL trial balance”

  1. Victoria,
    I’m attempting to build a similar report from the dbo.AccountSummary view rather than the G11110 or G11111 table. This view pulls the account activity from all years. My boss is asking for a trailing 12 month report. However, I can’t figure out how to build the report with months across the columns for multiple years. Here is the sql view of the table.
    Thanks,
    Aaron

    SELECT [Year]
    ,[Period ID]
    ,[Account Number]
    ,[Account Description]
    ,[Credit Amount]
    ,[Debit Amount]
    ,[Account Alias]
    ,[Account Category Number]
    ,[Account Index]
    ,[Account Type]
    ,[Active]
    ,[Adjust for Inflation]
    ,[Balance For Calculation]
    ,[Conversion Method]
    ,[Created Date]
    ,[Decimal Places]
    ,[Document Status]
    ,[Fixed Or Variable]
    ,[Historical Rate]
    ,[Inflation Equity Account Index]
    ,[Inflation Revenue Account Index]
    ,[Main Account Segment]
    ,[Modified Date]
    ,[Note Index]
    ,[Period Balance]
    ,[Post Inventory In]
    ,[Post Payroll In]
    ,[Post Purchasing In]
    ,[Post Sales In]
    ,[Posting Type]
    ,[Segment1]
    ,[Segment2]
    ,[Segment3]
    ,[Typical Balance]
    ,[User Defined 1]
    ,[User Defined 2]
    ,[Segments]
    ,[Ledger Name]
    ,[Ledger Description]
    ,[Account Index For Drillback]
    FROM [dbo].[AccountSummary]

    Like

    • Hi Aaron,

      A few thoughts, not necessarily in order of importance:

      • It’s usually very inefficient to build a view based on another view. I understand it’s sometimes easier because it seems like a lot of the work is done already, but report performance typically suffers when you do this. So instead I would create a subquery of GL11110 and GL11111 data that only pulls in the years/periods you want, then pull in the amounts into columns the way I am doing it in this blog post.
      • When you’re building a view with changing columns (ie today the first column may be Aug 2015 but as soon as you’re in September the first column will be Sep 2015) there is no way to change the column names dynamically, so you have to do something like Month-12, Month-11, etc. in your code. Some users are ok with this, but for this type of report, I suspect that’s not ok, so you would need to use a reporting tool other than SmartList or Excel for the report where you can add some additional logic to change the column names based on a formula.
      • To accomplish a trailing/rolling 12 months report based on this data, since there are no actual dates in it, I would probably convert the year and period ID columns into an actual date. Then you can use those dates to determine the last 12 months. This should be pretty straightforward if your fiscal periods are calendar months. If they are not, this will be more complicated and might be better to come up with a different formula to determine what 12 periods to include.

      So to sum up – this is certainly doable, and I have created many similar reports in the past, but it’s not a piece of cake and the logic will change depending on your fiscal year/period setup as well as what reporting tool you will be using.

      Hope that helps give you some ideas.
      -Victoria

      Like

  2. Hi Victoria,

    I am trying to add unit accounts to my trial balance. I have tried changing:

    where g.ACCTTYPE = 1
    and g.YEAR1 = (select min(YEAR1) from GL11110)

    to

    where g.ACCTTYPE in (1,2) — =2
    and g.YEAR1 = (select min(YEAR1) from GL11110)

    but nothing I change seems to pull items listed as ACCTTYPE = 2 from GL11110.

    What’s inhibiting unit accounting from getting pulled in this report if it isn’t the line above?

    Thank you

    Like

  3. I started my career in Dynamics GP following your books. I am very thankful for your posts/blogs. I believe you are the best. Thanks.

    Like

  4. Hi Victoria, this is great. One request. Could you add the summary of all the lines at the end somehow? I would like it to come out similar to the trial balance in GP so we can run this before and after an upgrade to make sure numbers didn’t change.

    Like

  5. Hi Victoria,

    Great view!
    I’ve been using SQL for around 12 years now but I’ve only recently started using GP so this is very useful.

    Is it possible to add budget periods (presumably by adding a budget table with a Union All) so that I can see 12 months of actuals with 12 months of budget to the right of the actuals?

    Like

  6. Victoria,

    Had the staff accountant in today with an issue on the Trial Balance report from within GP. 2 days ago worked fine, today no data is returned. Ran this code and data is present as are the journal entries. Where is a good starting point to find what has changed in last 2 days?

    Like

    • Hi Steven,

      Do you mean the General Ledger Trial Balance report? If so, I often find two things that can cause the report to not print any data:

      1. Not selecting the ‘Posting Accounts’ option in the Include list on the left side of the Options window.
      2. Not selecting a valid combination under Year. For example, if the option was originally set up with 2014 and Current year and 2014 has since been closed, the report will not print anything since 2014 and current year will not result in any data.

      There might be some other settings causing a problem, but those are the two that I see most often. If you still need help, email me a screenshot of the options window you’re using and I will try to help further.

      -Victoria

      Like

  7. Hi Victoria,
    This is a great report, but my boss wants the actual period balance by month instead of the net change. I tried using the perdblnc field but it really isn’t the period balance, it is actually the net change field. Then I tried using the perdblance (net change) and add that back to the previous month in my own tweaked version and it created the view, but when I try and select from it I get ‘error converting data type varchar to numeric’. I can’t seem to figure out why that’s happening. Here’s how I changed what you wrote. I’m obviously doing something wrong. I’d appreciate it if you could help me figure out how to get period balances by month.

    Thanks,Laura

    CREATE view [law_Open_Yr_GL_nc_pb]
    as

    — ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
    — Created March 17, 2014 by Victoria Yudin
    — Flexible Solutions, Inc.
    — For updates please see http://wp.me/pkuMh-1hx
    — Only returns the first open year in GP
    — Month names in columns use calendar fiscal year
    — modified to try and get real period balance – LAW 7/7/2014
    — ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

    select
    g.YEAR1 [Year],
    a.ACTNUMST Account,
    g.ACTDESCR [Description],
    c.ACCATDSC Category,
    sum(case g.PERIODID when 0
    then g.PERDBLNC else 0 end) Beginning_nc,
    sum(case g.PERIODID when 1
    then g.PERDBLNC else 0 end)January_nc,
    sum(case g.PERIODID when 2
    then g.PERDBLNc else 0 end) February_nc,
    sum(case g.PERIODID when 3
    then g.PERDBLNC else 0 end) March_nc,
    sum(case g.PERIODID when 4
    then g.PERDBLNC else 0 end) April_nc,
    sum(case g.PERIODID when 5
    then g.PERDBLNC else 0 end) May_nc,
    sum(case g.PERIODID when 6
    then g.PERDBLNC else 0 end) June_nc,
    sum(case g.PERIODID when 7
    then g.PERDBLNC else 0 end) July_nc,
    sum(case g.PERIODID when 8
    then g.PERDBLNC else 0 end) August_nc,
    sum(case g.PERIODID when 9
    then g.PERDBLNC else 0 end) September_nc,
    sum(case g.PERIODID when 10
    then g.PERDBLNC else 0 end) October_nc,
    sum(case g.PERIODID when 11
    then g.PERDBLNC else 0 end) November_nc,
    sum(case g.PERIODID when 12
    then g.PERDBLNC else 0 end) December_nc,
    sum(g.PERDBLNC) Ending_nc,

    sum(case g.PERIODID when 1
    then (g.PERDBLNC + ‘Beginning_nc’) else 0 end)January,
    sum(case g.PERIODID when 2
    then (g.PERDBLNC + ‘January_nc’) else 0 end) February,
    sum(case g.PERIODID when 3
    then (g.PERDBLNC + ‘February_nc’) else 0 end) March,
    sum(case g.PERIODID when 4
    then (g.PERDBLNC + ‘March_nc’) else 0 end) April,
    sum(case g.PERIODID when 5
    then (g.PERDBLNC + ‘April_nc’) else 0 end) May,
    sum(case g.PERIODID when 6
    then (g.PERDBLNC + ‘May_nc’) else 0 end) June,
    sum(case g.PERIODID when 7
    then (g.PERDBLNC + ‘June_nc’) else 0 end) July,
    sum(case g.PERIODID when 8
    then (g.PERDBLNC + ‘July_nc’) else 0 end) August,
    sum(case g.PERIODID when 9
    then (g.PERDBLNC + ‘August_nc’) else 0 end) September,
    sum(case g.PERIODID when 10
    then (g.PERDBLNC + ‘September_nc’) else 0 end) October,
    sum(case g.PERIODID when 11
    then (g.PERDBLNC + ‘October_nc’) else 0 end) November,
    sum(case g.PERIODID when 12
    then (g.PERDBLNC + ‘Nobember_nc’) else 0 end) December
    from GL11110 g –GL

    inner join GL00102 c –categories
    on g.ACCATNUM = c.ACCATNUM

    inner join GL00105 a –for account number
    on g.ACTINDX = a.ACTINDX

    where g.ACCTTYPE = 1
    and g.YEAR1 = (select min(YEAR1) from GL11110)

    group by g.YEAR1, g.ACTDESCR, a.ACTNUMST, c.ACCATDSC

    GO


    It creates the view, then when I say
    select * from [law_Open_Yr_GL_nc_pb]

    I get:

    Msg 8114, Level 16, State 5, Line 1
    Error converting data type varchar to numeric.

    Like

  8. I tried running this in Smartlist builder to see if would return the results I want and when I preview it says Invalid script for both options, I’m not a sql person, so I don’t know how to fix.

    Like

  9. As usual, nice stuff, thanks! After looking at it, I thought, “This sure looks like something for a PIVOT query”. I haven’t done many of them so I thought it would be fun to give it a whirl. Here ya go! It produces the same output as your, just differently. IMHO, I think the monthly totals are calculated easier by using the PIVOT, but the ending balances are worse in my version.

    Oh yeah, our fiscal year starts on July 1, so my months are labeled differently than yours.

    Enjoy

    John

    SELECT Year,
    Account,
    Description,
    Category,
    [0] AS Previous,
    [1] AS Jul,
    [2] AS Aug,
    [3] AS Sep,
    [4] AS Oct,
    [5] AS Nov,
    [6] AS Dec,
    [7] AS Jan,
    [8] AS Feb,
    [9] AS Mar,
    [10] AS Apr,
    [11] AS May,
    [12] AS Jun,
    Ending.Ending_Balance
    FROM (
    SELECT GL11110.YEAR1 AS Year,
    GL11110.PERIODID,
    GL11110.ACTINDX,
    GL00105.ACTNUMST AS Account,
    GL11110.ACTDESCR AS Description,
    GL00102.ACCATDSC AS Category,
    DEBITAMT – CRDTAMNT AS Diff
    FROM GL11110 –GL
    JOIN GL00102 ON GL11110.ACCATNUM = GL00102.ACCATNUM –categories
    JOIN GL00105 ON GL11110.ACTINDX = GL00105.ACTINDX –for account number
    WHERE GL11110.ACCTTYPE = 1
    AND GL11110.YEAR1 = (SELECT MIN(YEAR1) FROM GL11110)
    ) AS p
    PIVOT
    (
    SUM(Diff)
    FOR PERIODID IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
    ) AS PivotCreditsAndDebits
    LEFT JOIN (
    SELECT YEAR1,
    ACTINDX,
    ISNULL(SUM(PERDBLNC),0) AS Ending_Balance
    FROM GL11110
    GROUP BY YEAR1,
    ACTINDX
    ) AS Ending ON Ending.YEAR1 = PivotCreditsAndDebits.Year
    AND Ending.ACTINDX = PivotCreditsAndDebits.ACTINDX
    ORDER BY Year,
    Description,
    Account,
    Category

    Liked by 1 person

  10. lonnie.nelson@watchguard.com Reply March 19, 2014 at 12:44 pm

    I like this view but think I may have some bad data in my GL10110 table. Should there be anything from closed years in there? I have records from year 0, year 2008 – 2012 and then the ones I expected which are years 2014 – 2018. Should there be any records for historical years in this table? Of the 9046 rows in the table, 496 are historical years. I changed the script to hardcode 2014 as if I run the script as is it tries to use the records from year 0 in my data.

    Thanks!

    Like

    • Hi Lonnie,

      There should not be anything from closed years or with year 0 in the GL10110 table. Have you tried running check links and reconcile to see if that clears it up? If not, you may need to talk to your GP Partner or Dynamics GP Support for some more specific help with this.

      In the meantime, it sounds like you already implemented what my suggestion would be, which is to hardcode the year you want. The only issue with doing that will be that your beginning and ending balances for Balance Sheet accounts will not be correct – but all the monthly net change columns should be fine.

      -Victoria

      Like

  11. Works like a charm! Thanks for sharing!

    Like

Trackbacks/Pingbacks

  1. SQL view for Dynamics GP open year GL trial balance with month end balances | Victoria Yudin - July 9, 2014

    […] on a reader’s request, I have created a variation on my SQL view for Dynamics GP open year GL trial balance. This version will show you the month end balances instead of the net change for each […]

    Like

  2. Some things that I ran across today - Microsoft Dynamics GP DBA - Microsoft Dynamics GP - Microsoft Dynamics Community - April 21, 2014

    […] SQL view for Dynamics GP open year GL trial balance […]

    Like

  3. SQL view for Dynamics GP open year GL trial balance | Victoria Yudin | DynamicAccounting.net - March 19, 2014

    […] Victoria shows off her SQL view for Dynamics GP open year GL trial balance. […]

    Like

  4. SQL view for Dynamics GP open year GL trial balance – 3/17, Victoria Yudin | - March 17, 2014

    […] Continue reading on Source Blog […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,310 other followers

%d bloggers like this: