SQL view for Dynamics GP open year GL trial balance

March 17, 2014 by Victoria Yudin 29 Comments

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:

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.

Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts

General Ledger, GP Reports code, GP SQL view, SQL code

Identifying reporting needs in a Microsoft Dynamics GP 2013 implementation

SQL script for comparing voids between RM and SOP in Dynamics GP

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

Mark David Reply August 10, 2023 at 4:48 pm

This works great but is there a way to run it for a certain year rather than current year? I would like to be able to run the exact same report but for different years.

LikeLike
- Victoria Yudin Reply August 11, 2023 at 6:28 am
  
  Hi Mark, I have a stored procedure here to get the trial balance for a historical year:
  
  Refreshable Excel report for historical GL trial balance in Dynamics GP
  
  -Victoria
  
  LikeLike
Aaron Allen Reply August 22, 2016 at 5:26 pm

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]

LikeLike
- Victoria Yudin Reply August 24, 2016 at 7:41 am
  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
  
  LikeLike
- Philip Grooms Reply June 23, 2017 at 8:32 am
  
  Works great Victoria However we have several company’s and we use Fiscal Periods and they are mostly different. One company could be July – June and others could be Oct – Sept. Is there any way to read the Pername field from the SY40100 table? Every time I try I return way too many results.
  Thanks,
  Philip
  
  LikeLike
  - Victoria Yudin Reply July 3, 2017 at 7:27 am
    
    Hi Philip,
    
    My understanding is that changing column names ‘dynamically’ is extremely difficult and also not typically recommended in SQL. If you have a need to do that you might be better off using a reporting tool (like SSRS or Crystal Reports) and coding the column names there based on some additional formulas.
    
    -Victoria
    
    LikeLike
Michael Veno Reply January 18, 2016 at 2:21 pm

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

LikeLike
- Victoria Yudin Reply January 18, 2016 at 2:28 pm
  
  Hi Michael,
  
  I don’t think that Unit Accounts have categories. Try changing the inner join to the GL00102 table to a left outer join instead.
  
  -Victoria
  
  LikeLike
  - Michael Veno Reply January 18, 2016 at 2:52 pm
    
    Thank you! I have been trying to self teach myself how to do this since our IT person left, your blog and information has been my go to resource.
    
    LikeLike
Satyendra Singh Reply October 26, 2015 at 4:24 pm

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.

LikeLike
Rob Klaproth Reply October 25, 2015 at 5:09 pm

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.

LikeLiked by 1 person
- Victoria Yudin Reply October 26, 2015 at 10:54 am
  
  Rob,
  
  Just want to make sure – are you looking for the totals of each row in another column or the totals of each row in another line? (If the latter, these should all be 0’s.)
  
  -Victoria
  
  LikeLike
dazzathedrummer Reply August 3, 2015 at 7:49 am

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?

LikeLike
Steven Reply May 13, 2015 at 8:52 am

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?

LikeLike
- Victoria Yudin Reply May 13, 2015 at 12:24 pm
  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:
  
  Not selecting the ‘Posting Accounts’ option in the Include list on the left side of the Options window.
  
  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
  
  LikeLike
laura westley Reply July 8, 2014 at 2:37 pm

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.

LikeLike
- Victoria Yudin Reply July 9, 2014 at 6:46 am
  
  Hi Laura,
  
  Hope you are well!
  
  Check out the view I posted up this morning – I think this is what you’re looking for.
  
  -Victoria
  
  LikeLike
  - laura westley Reply July 9, 2014 at 1:59 pm
    
    Hi Victoria,
    I’m doing great! You’re awesome – and so smart! Duh! What a clever way to get the results I needed! Thanks so much!!
    
    -Laura
    
    LikeLike
AM Reply June 18, 2014 at 3:19 pm

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.

LikeLike
- Victoria Yudin Reply June 19, 2014 at 4:45 am
  
  Hi AM,
  
  Not sure what you mean by ‘both options’…are you setting this up as a SQL view then setting up a SmartList to point to that view? If not, that may be the problem. I have a blog post detailing how to do this that may help: How to use a SQL view in SmartList Builder
  
  -Victoria
  
  LikeLike
John Arnold Reply April 9, 2014 at 5:40 pm

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

LikeLiked by 1 person
- Victoria Yudin Reply April 10, 2014 at 3:49 am
  
  Thanks John,
  
  Now people have two choices for this. 🙂
  
  -Victoria
  
  LikeLike
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!

LikeLike
- Victoria Yudin Reply March 19, 2014 at 12:55 pm
  
  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
  
  LikeLike
Dave Reply March 17, 2014 at 8:54 am

Works like a charm! Thanks for sharing!

LikeLike