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
 

Use ADO to Display Created Date on Customer Maintenance

Here's some weirdness and how to solve it.
If you modify the Customer Maintenance window by dragging out the Created Date field to display on the window, and then create a new customer, the Created Date field will not update, no kidding. I don't know why this happens, it doesn't work this way on the other windows I've checked, but on the Customer Maintenance window, you don't get a new date.
In order to display the Created Date AND have the system update the field when a new record is added is a job for VBA. First, you need to created a local 'Date' field and drag it out onto the window. Create and Link a prompt to the new field. I used 'Date Created' for my prompt.

Add the Customer Maintenance window, the Customer ID and the local Date Created field to your VBA project. Next you will write some VBA code to connect to the database table, look up the value of the Created Date field, and write that value to the date field you created in Modifier. The date will both display to your user and be updated when new records are added.

The scripts I used in my VBA project are below:

-----------------------------------------------------------------------------------------------------------------------------

Option Explicit

Dim cn As New ADODB.Connection

------------------------------------------------------

Private Sub Window_BeforeOpen(OpenVisible As Boolean)

 ' ADO Connection

    Set cn = UserInfoGet.CreateADOConnection

    'set the database to the currently logged in db

    cn.DefaultDatabase = UserInfoGet.IntercompanyID

End Sub

------------------------------------------------------

Private Sub CustomerID_Changed()

    Dim rst As New ADODB.Recordset

    Dim cmd As New ADODB.Command

    Dim sqlstring As String

    If Me.CustomerID <> "" Then

    sqlstring = "select creatddt from rm00101 where CUSTNMBR ='" & Me.CustomerID & "'"

        ' ADO Command

        cmd.ActiveConnection = cn

        ' adCmdText

        cmd.CommandType = 1

        ' Command

        cmd.CommandText = sqlstring

        ' Pass through SQL

        Set rst = cmd.Execute

        If Not (rst.EOF And rst.BOF) Then

          DateCreated = rst!CREATDDT

        End If

        rst.Close

    End If

End Sub

------------------------------------------------------

Private Sub Window_BeforeClose(AbortClose As Boolean)

    ' Close ADO Connection

    cn.Close

    Set cn = Nothing

End Sub

Until next time!
Leslie

Problems with Forms 1099 Names and Addresses

Problems with Forms 1099 Names and Addresses

There is an issue as to which name and tax ID number to use on the Form 1099. If the vendor is a sole proprietor, the IRS states that the individual’s social security number and actual name shall be used.  Their employer ID number (which is what’s used on Forms 941, W-2, 940, etc.) should not be used; neither should their DBA name. The problem is, when printing the Form 1099, GP always uses the Vendor Check Name field from the Vendor Maintenance window as the 1099 name. Often, the vendor check name is a DBA name and not the name of the proprietor. For instance, it’s ‘Joe’s Construction Co.’ and not ‘Joe Smith’. It’s ‘Joe Smith’ that should be used on the Form 1099.

Also, the address that the vendor wants the 1099 to be mailed to is sometimes an altogether different address from the mailing address used for checks. Starting with version 2015 (I think), a new Address ID was added to the Vendor Maintenance window labeled ‘1099’. Unfortunately, the Vendor Check Name is not a field associated with a  Vendor Address ID. Consequently, while the addition of a 1099 address ID specification does solve the address problem, it does not solve the name problem.

One suggestion on how to resolve the name problem, is to use an address ID named ‘1099’ along with a modified report. For those vendors where an alternate name is desired, create a special Address ID named ‘1099’ and place the name you would like to print on the Form 1099 in the Contact field. You would then need to modify the report used to print the 1099s.

To modify the 1099 Laser Miscellaneous report, follow the steps below:

1.     Open the Report Writer application using the menu or by selecting Alt+9.

2.     Click on the Reports toolbar button.

3.     Insert the 1099 Laser Miscellaneous report into the Modified Reports List.

4.     Click on the 1099 Laser Miscellaneous item in the Modified Reports list and select the Open button to open the Report Definition window.

5.     Click on the Tables button to open the Report Table Relationships window.

6.     Click on the 1099s To Print Temporary File table and then click the New button.

7.     Select the PM Vendor Master File table and click OK to add it to the report.

8.     Click on the PM Vendor Master File table and then click the New button.

9.     Slect the PM Address MSTR* table and click OK to add it to the report.

10.   Your Report Table Relationships window should then look like this upon completion:

 

11.   Click the Close button to close the Report Table Relationships window.

12.   Back on the Report Definition window, click on the Layout button and the Report Layout Window will open.

13.   In the Toolbox window, select Calculated Fields and then click the New button to open the Calculated Field Definition window.

14.   Fill in the window using the following settings.

a.     Name: Alt Check Name.

b.     Result Type: String.

c.     Expression Type: Conditional.

d.     Click the Fields Tab.

                                       i.    Fields tab Resources: PMVendor Master File.

                                      ii.    Fields tab Field: Vendor Address Code – 1099.

                                     iii.    Click the Add button.

                                      iv.    Click the equals (=) button in the Operators section.

e.     Click the Constants tab.

                                       i.    Constants tab Type: String.

                                      ii.    Cnstants tab Constant: 1099.

                                     iii.    Click the Add button.

f.     Click into the True Case section.

                                       i.    Select the Fields tab.

1.     Fields tab Resources: PM Address MSTR.

2.     Fields Tab Field: Vendor Contact.

3.     Click the Add button.

g.     Click into the False Case section.

                                       i.    Select the Fields tab.

1.     Fields tab Resources: 1099s To Print Temporary File.

2.     Fields tab Field: Vendor Check Name.

3.     Click the Add button.

4.     Your completed calculated field will look substantially similar to this:

 

h.     Click the OK button.

15.   On the Report Layout, remove the existing Vendor Check Name field from the report and place the new Alt Check Name calculated field in its place. Upon completion, your Report Layout window will look substantially similar to this:

 

16.   Close the Report Layout window and click the Save button on the dialog box presented.

17.   Click OK on the Report Definition window and then return to Microsoft Dynamics GP using the File menu item.

18.   Grant security to the Modified Report using the Alternate/Modified Forms and Reports window.

Now when you print your Forms 1099 the name field will display the Alt Check Name field instead of the Vendor Check Name field if your 1099 Address Code is equal to ‘1099’. One drawback from this approach is that you’d need to be sure to modify the report each year to allow for changes that may be made as part of the year-end update. The year-end update does not update the layout of modified reports.

 

Please Select a Company - But I already have!!

This is a quick ‘when things go wrong’ article that solves an annoying issue. If you launch GP and everything is fine EXCEPT the dialog asking you to select a company never goes away, you have this problem.

 

If you click the OK button, the dialog closes and all is well with the world. If you are experiencing this issue, you need to do a little file surgery in SQL. The problem is in the SY01402 table within the DYNAMICS database. What’s happening is that the system is trying to launch a ‘remembered’ company that doesn’t exist in your current installation. This could come up if you move your system to a different server and do not bring all of the companies over. 

To solve the problem, you need to edit the SY01402 table. Filter the table so that it shows you records where the syDefaultType field is equal to 70. This is the Remembered Company entry. The value in the SYUSERDFSTR field refers to the COMPANYID of the ‘remembered’ company. The only thing you need to do is either delete the record or change the value in the SYUSERDFSTR field to a company ID that exists. You can find valid company IDs in the SY01500 table in the DYNAMICS database.

Until next time, Live the Dream!
Leslie

Technorati Tags: installation,please select a company