T-SQL View to create Dynamics GP GL Summary Trial Balance

Do you want to see your trial balance in excel? Do you want the accounts on the left and the years going in columns across the TOP? Of course you do. Now, let's say you do not have access to FRx or SSRS or Management Reporter or JetReports or any other number of products that make this feat easy. You tried to create a SmartList, but the view just isn't set up to make this look like you want it. If you are looking for a straightforward set of views that will give you a simple summary trial balance with as many years as you want, you have come to the right place!
Today, I uploaded a file to BOX that includes the code needed to create the views. The final view is the trail balance that looks like the Excel worksheet below. I linked the view to the spreadsheet so I can have easy access to it whenever someone asks me for such a thing. If you like it, you can get a copy for yourself by downloading the SQL scripts here.

I got a message that some folks weren't allowed to download things at their work, so I'm pasting the statements below my signature.

Live the dream!
Leslie




/* Leslie Vail 3/17/2017------------------------------------------------
This is a series of views that culminate in a final view showing a summary trial balance by fiscal year.
1. The first view creates a unioned summary balance list by account by period by year.
2. The second view consolidates the periods so you have a list of account balances by year.
3. The third view creates the PIVOT showing the account balance by year with the years as columns. The view provides columns for years [1990] through [2020], you'll need to edit it if you have years that fall outside of that range, or if you want to remove some years.
NULLs appear where the account did not exist for that year. If you do not want the NULLs, simply use COALESCE statement.

The only tables included are:
   GL00100 Account Master
   GL00105 Account Index Master
   GL1011O Open Year Summary Master
   GL10111 History Year Summary Master
--------------------------------------------------------------*/ 



/*

FIRST VIEW TO CREATE A UNIONED SUMMARY BALANCE LIST BY

ACCOUNT BY PERIOD BY YEAR --

*/
 ALTER VIEW [dbo].[vw1LV_AccountSummaryYears]
AS SELECT rtrim(['Account Current Summary Master'].[YEAR1]) AS 'FiscalYear'

, ['Account Current Summary Master'].[PERIODID] AS 'PeriodID'

, (

SELECT rtrim([ACTNUMST])

FROM [GL00105] AS ['Account Index Master']

WHERE ['Account Index Master'].[ACTINDX] = ['Account Master'].[ACTINDX]

) AS 'AccountNumber'

, rtrim(['Account Master'].[ACTDESCR]) AS 'AccountDescription'

, ['Account Current Summary Master'].[DEBITAMT] AS 'DebitAmount'

, ['Account Current Summary Master'].[CRDTAMNT] AS 'CreditAmount'

, ['Account Current Summary Master'].[PERDBLNC] AS 'PeriodBalance'

, rtrim(['Account Master'].[ACTALIAS]) AS 'AccountAlias'

, 'Account Category Number' = dbo.DYN_FUNC_Account_Category_Number(['Account Current Summary Master'].[ACCATNUM])

, ['Account Current Summary Master'].[ACTINDX] AS 'Account Index'

, 'Account Type' = dbo.DYN_FUNC_Account_Type(['Account Master'].[ACCTTYPE])

, 'Active' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ACTIVE])

, 'Adjust for Inflation' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ADJINFL])

, 'Balance For Calculation' = dbo.DYN_FUNC_Balance_For_Calculation(['Account Master'].[BALFRCLC])

, 'Conversion Method' = dbo.DYN_FUNC_Conversion_Method(['Account Master'].[CNVRMTHD])

, ['Account Master'].[CREATDDT] AS 'Created Date'

, 'Decimal Places' = dbo.DYN_FUNC_Decimal_Places_QTYS(['Account Master'].[DECPLACS])

, 'Document Status' = dbo.DYN_FUNC_Document_Status_GL_Sum(1)

, 'Fixed Or Variable' = dbo.DYN_FUNC_Fixed_Or_Variable(['Account Master'].[FXDORVAR])

, ['Account Master'].[HSTRCLRT] AS 'Historical Rate'

, ['Account Master'].[INFLAEQU] AS 'Inflation Equity Account Index'

, ['Account Master'].[INFLAREV] AS 'Inflation Revenue Account Index'

, rtrim(['Account Master'].[MNACSGMT]) AS 'Main Account Segment'

, ['Account Master'].[MODIFDT] AS 'Modified Date'

, ['Account Master'].[NOTEINDX] AS 'Note Index'

, 'Post Inventory In' = dbo.DYN_FUNC_Post_Inventory_In(['Account Master'].[PostIvIn])

, 'Post Payroll In' = dbo.DYN_FUNC_Post_Payroll_In(['Account Master'].[PostPRIn])

, 'Post Purchasing In' = dbo.DYN_FUNC_Post_Purchasing_In(['Account Master'].[PostPurchIn])

, 'Post Sales In' = dbo.DYN_FUNC_Post_Sales_In(['Account Master'].[PostSlsIn])

, 'Posting Type' = dbo.DYN_FUNC_Posting_Type(['Account Master'].[PSTNGTYP])

, ['Account Master'].[ACTNUMBR_1] AS 'Segment1'

, ['Account Master'].[ACTNUMBR_2] AS 'Segment2'

, ['Account Master'].[ACTNUMBR_3] AS 'Segment3'

, ['Account Master'].[ACTNUMBR_4] AS 'Segment4'

, 'Typical Balance' = dbo.DYN_FUNC_Typical_Balance(['Account Master'].[TPCLBLNC])

, rtrim(['Account Master'].[USERDEF1]) AS 'User Defined 1'

, rtrim(['Account Master'].[USERDEF2]) AS 'User Defined 2'

, '4' AS 'Segments'

, 'Ledger Name' = dbo.DYN_FUNC_GL_Ledger_Name(['Account Current Summary Master'].[Ledger_ID])

, 'Ledger Description' = dbo.DYN_FUNC_GL_Ledger_Description(['Account Current Summary Master'].[Ledger_ID])

, 'Account Index For Drillback' = 'dgpp://DGPB/?Db=&Srv=DALAP06&Cmp=MPI&Prod=0' + dbo.dgppAccountIndex(1, ['Account Current Summary Master'].[ACTINDX])

FROM [GL10110] AS ['Account Current Summary Master'] WITH (NOLOCK)

LEFT JOIN [GL00100] AS ['Account Master'] WITH (NOLOCK)

ON ['Account Current Summary Master'].[ACTINDX] = ['Account Master'].[ACTINDX]

UNION ALL

SELECT rtrim(['Account Summary History'].[YEAR1]) AS 'FiscalYear'

, ['Account Summary History'].[PERIODID] AS 'PeriodID'

, (

SELECT rtrim([ACTNUMST])

FROM [GL00105] AS ['Account Index Master']

WHERE ['Account Index Master'].[ACTINDX] = ['Account Master'].[ACTINDX]

) AS 'AccountNumber'

, rtrim(['Account Master'].[ACTDESCR]) AS 'AccountDescription'

, ['Account Summary History'].[DEBITAMT] AS 'DebitAmount'

, ['Account Summary History'].[CRDTAMNT] AS 'CreditAmount'

, ['Account Summary History'].[PERDBLNC] AS 'Period Balance'

, rtrim(['Account Master'].[ACTALIAS]) AS 'AccountAlias'

, 'Account Category Number' = dbo.DYN_FUNC_Account_Category_Number(['Account Summary History'].[ACCATNUM])

, ['Account Summary History'].[ACTINDX] AS 'Account Index'

, 'Account Type' = dbo.DYN_FUNC_Account_Type(['Account Master'].[ACCTTYPE])

, 'Active' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ACTIVE])

, 'Adjust for Inflation' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ADJINFL])

, 'Balance For Calculation' = dbo.DYN_FUNC_Balance_For_Calculation(['Account Master'].[BALFRCLC])

, 'Conversion Method' = dbo.DYN_FUNC_Conversion_Method(['Account Master'].[CNVRMTHD])

, ['Account Master'].[CREATDDT] AS 'Created Date'

, 'Decimal Places' = dbo.DYN_FUNC_Decimal_Places_QTYS(['Account Master'].[DECPLACS])

, 'Document Status' = dbo.DYN_FUNC_Document_Status_GL_Sum(2)

, 'Fixed Or Variable' = dbo.DYN_FUNC_Fixed_Or_Variable(['Account Master'].[FXDORVAR])

, ['Account Master'].[HSTRCLRT] AS 'Historical Rate'

, ['Account Master'].[INFLAEQU] AS 'Inflation Equity Account Index'

, ['Account Master'].[INFLAREV] AS 'Inflation Revenue Account Index'

, rtrim(['Account Master'].[MNACSGMT]) AS 'Main Account Segment'

, ['Account Master'].[MODIFDT] AS 'Modified Date'

, ['Account Master'].[NOTEINDX] AS 'Note Index'

, 'Post Inventory In' = dbo.DYN_FUNC_Post_Inventory_In(['Account Master'].[PostIvIn])

, 'Post Payroll In' = dbo.DYN_FUNC_Post_Payroll_In(['Account Master'].[PostPRIn])

, 'Post Purchasing In' = dbo.DYN_FUNC_Post_Purchasing_In(['Account Master'].[PostPurchIn])

, 'Post Sales In' = dbo.DYN_FUNC_Post_Sales_In(['Account Master'].[PostSlsIn])

, 'Posting Type' = dbo.DYN_FUNC_Posting_Type(['Account Master'].[PSTNGTYP])

, ['Account Master'].[ACTNUMBR_1] AS 'Segment1'

, ['Account Master'].[ACTNUMBR_2] AS 'Segment2'

, ['Account Master'].[ACTNUMBR_3] AS 'Segment3'

, ['Account Master'].[ACTNUMBR_4] AS 'Segment4'

, 'Typical Balance' = dbo.DYN_FUNC_Typical_Balance(['Account Master'].[TPCLBLNC])

, rtrim(['Account Master'].[USERDEF1]) AS 'User Defined 1'

, rtrim(['Account Master'].[USERDEF2]) AS 'User Defined 2'

, '4' AS 'Segments'

, 'Ledger Name' = dbo.DYN_FUNC_GL_Ledger_Name(['Account Summary History'].[Ledger_ID])

, 'Ledger Description' = dbo.DYN_FUNC_GL_Ledger_Description(['Account Summary History'].[Ledger_ID])

, 'Account Index For Drillback' = 'dgpp://DGPB/?Db=&Srv=DALAP06&Cmp=MPI&Prod=0' + dbo.dgppAccountIndex(1, ['Account Summary History'].[ACTINDX])

FROM [GL10111] AS ['Account Summary History'] WITH (NOLOCK)

LEFT JOIN [GL00100] AS ['Account Master'] WITH (NOLOCK)

ON ['Account Summary History'].[ACTINDX] = ['Account Master'].[ACTINDX]



GO
 
GRANT SELECT ON [vw1LV_AccountSummaryYears] TO DYNGRP



GO
 


/*

SECOND VIEW TO CREATE A SUMMARY BALANCE LIST BY

ACCOUNT BY YEAR

*/
 
ALTER VIEW [dbo].[vw2LV_ToBuildPivot]



AS
 SELECT AccountNumber

, AccountDescription

, FiscalYear

, SUM(PeriodBalance) AS AnnualBalance

FROM vw1LV_AccountSummaryYears

GROUP BY AccountNumber

, AccountDescription

, FiscalYear



GO
 
GRANT SELECT ON [vw2LV_ToBuildPivot] TO DYNGRP



GO
 


/*

THIRD VIEW CREATES A PIVOT SHOWING ACCOUNT BALANCE BY YEAR

WITH THE YEARS AS COLUMNS. VIEW INCLUDES COLUMNS FROM 1990 TO 2020.

YOU NEED TO EDIT THE COLUMNS TO INCLUDE ONLY THE ONE'S YOU WANT. YOU CAN ALSO ADD

NEW ONES IF YOU HAVE TRANSACTIONS IN FISCAL YEARS BEYOD THESE DATES.

*/
 
ALTER VIEW [dbo].[vw3LV_PivotAnnualBalance]



AS
 SELECT *

FROM vw2LV_ToBuildPivot

PIVOT(SUM(AnnualBalance) FOR FiscalYear IN ([1990], [1991], [1992], [1993], [1994], [1995]

, [1996], [1997], [1998], [1999], [2000], [2001], [2002], [2003], [2004], [2005], [2006]

, [2007], [2008], [2009], [2010], [2011], [2012], [2013], [2014], [2015], [2016], [2017]

, [2018], [2019], [2020]))

AS PIVOT_BAL;



GO
 
GRANT SELECT ON [vw1LV_AccountSummaryYears] TO DYNGRP

GO