March 17, 2014 
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.
Victoria Yudin 
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
Hi Aaron,
A few thoughts, not necessarily in order of importance:
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
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
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
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
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
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
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
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
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
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
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
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:
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
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
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
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
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
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
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
Thanks John,
Now people have two choices for this. 🙂
-Victoria
LikeLike
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
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
Works like a charm! Thanks for sharing!
LikeLike