FMTFMT

Karelia Sotelo, Author at FMT

Search Archive
Multi-Database Dynamic View for SSRS
If you are one of those companies that have over 50 Microsoft Dynamics GP databases and would like to have an SSRS report that will dynamically loop into each, then this is the blog for you. I had a request from a client to create an SSRS report for all vendors on all Dynamics GP databases (over a 100) and for it to be dynamically created because more companies will be added in the future. With that many databases and the possibility to add more, creating a view with unions was not an option, so a loop needed to be created instead. Since all databases for Dynamics GP are registered on Dynamics database that was the logical option for the stored procedure to reside. USE [DYNAMICS] GO I used 2 parameters to filter on the check date. CREATE PROCEDURE [dbo].[spFMT_VendorsAllCompanies] ( @StartDate DATETIME = NULL, @EndDate DATETIME = NULL ) […]
How to Create Yearly Comparative Reports in Microsoft Dynamics GP
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] +' […]
Tracking Changes in On-Premise CRM 2011 Audit View using SQL
Have you ever found yourself needing to track the changes you make to records in CRM, but feeling uncertain how to do so? If you are using the On-Premise version of CRM 2011, you can leverage the Audit view in SQL Management Studio to easily track your changes. For example, let’s say you needed to track the changes for customers in CRM 2011 On-Premise to show one instance where the data had changed. CRM 2011 on premise Audit view contains all the attributes of a transaction in a string. Changes to a transaction are separated by “,” and the previous values of a transaction are separated by “~”.  Unfortunately, using the string alone we can’t query individual attributes to track changes. In order to more easily accomplish this, we would split the values to be able to join to other views or tables. In the following example, we will be […]
©
2021
 FMT Consultants, LLC.
|
Privacy Policy
X
FMT

Contact Us

X
FMT

Newsletter Sign-up

menu linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram