We receive a lot of requests from our clients to create yearly comparative reports and there aren’t any readily available in GP. I have created a custom SQL query that will look over your account balances for account history or auditing purposes.  It uses Microsoft SQL Server’s pivot query which is a really nice feature available in SQL 2005 or later.   The query can be used in Microsoft Dynamics GP SmartLists or custom SSRS reports. 

 

The following script will return the total net amount per account per year using the Account Summary view in Dynamic’s GP and will pivot each year into a column.

 

CREATE VIEW dbo.vFMT_TotalNetAccountYearly

——————————————————————————-

— FMT Consultants LLC, Karelia Sotelo

— Created date <2016/12/14>

— Yearly Total Net By Account

——————————————————————————-

AS

SELECT        Account, ISNULL([2013],0) AS [2013],

              ISNULL([2014],0) AS [2014],

              ISNULL([2015],0) AS [2015],

              ISNULL([2016],0) AS [2016],

              ISNULL([2017],0) AS [2017],

              ISNULL([2018],0) AS [2018]

FROM          (SELECT [Year],

                             [Account Number] +’ ‘+ [Account Description] AS [Account],

                             ([Debit Amount] – [Credit Amount]) AS [Net Amount]

              FROM          AccountSummary

              GROUP BY      [Year], [Account Number] + ‘ ‘ + [Account Description],

                             [Debit Amount] – [Credit Amount]

              ) AccSum

PIVOT         (SUM([Net Amount]) FOR [Year] IN

                      ([2013],[2014],[2015],[2016],[2017],[2018])) piv;

GO

GRANT SELECT ON vFMT_TotalNetAccountYearly TO DYNGRP

 

 

The results obtained by the query are below:

 

 

This was tested on the Fabrikam Sample Company, TWO database.  This example provides the years hardcoded in the SQL view, if you need obtain different year information you’ll need to adjust the query accordingly.  This view can then be added to SmartList using SmartList Designer or SmartList Builder and viewed by all users.