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
)
AS
— Assign null values
IF (@StartDate IS NULL) SET @StartDate = ‘1900/01/01’;
IF (@EndDate IS NULL) SET @EndDate = GETDATE();
A temporary table was needed to insert the data from each database
–Create temp table
CREATE TABLE #fmt_Vendors (
[DBName] VARCHAR(161)
,[Vendor ID] VARCHAR(15)
,[Vendor Name] VARCHAR(65)
,[Address 1] VARCHAR(61)
,[Address 2] VARCHAR(61)
,[City] VARCHAR(35)
,[State] VARCHAR(29)
,[Country] VARCHAR(61)
,[Zip Code] VARCHAR(11)
,[Phone Number 1] VARCHAR(21)
,[1099 Type] VARCHAR(100)
,[Hold] VARCHAR(100)
,[Last Check Amount] NUMERIC(19,5)
,[Last Check Date] DATETIME
,[Last Check Number] VARCHAR(21)
);
The variables needed to loop. I used a while loop and a counter in this case.
DECLARE @dbName VARCHAR(15);
DECLARE @Count INT;
DECLARE @Query VARCHAR(MAX);
— Total number of companies
SET @Count = (SELECT COUNT(INTERID) FROM DYNAMICS..SY01500);
And a dynamically created query to select from each database and insert on the temporary table.
— Loop trough all the companies
WHILE @Count > 0
BEGIN
WITH CTE_DB AS (SELECT INTERID,ROW_NUMBER() OVER(ORDER BY INTERID DESC) AS RowNo
FROM DYNAMICS..SY01500)
SELECT @dbName = (SELECT INTERID FROM CTE_DB WHERE RowNo = @Count);
SET @Query =
‘INSERT INTO DYNAMICS..#fmt_Vendors
(
[DBName]
,[Vendor ID]
,[Vendor Name]
,[Address 1]
,[Address 2]
,[City]
,[State]
,[Country]
,[Zip Code]
,[Phone Number 1]
,[1099 Type]
,[Hold]
,[Last Check Amount]
,[Last Check Date]
,[Last Check Number]
)
SELECT
”’+@dbName+”’ AS [DBName]
,[Vendor ID]
,[Vendor Name]
,[Address 1]
,[Address 2]
,[City]
,[State]
,[Country]
,[Zip Code]
,[Phone Number 1]
,[1099 Type]
,[Hold]
,[Last Check Amount]
,[Last Check Date]
,[Last Check Number]
FROM ‘+@dbName+‘..Vendors
WHERE [Last Check Date] BETWEEN CAST(”’+ CAST(@StartDate AS VARCHAR)
+”’ AS DATETIME) AND CAST(”’+ CAST(@EndDate AS VARCHAR)+”’ AS DATETIME);’;
EXEC (@query);
SET @count = @count –1;
END
The final select from the temporary table and drop.
— Get data for report
SELECT * FROM #fmt_Vendors;
— Drop temp table
DROP TABLE #fmt_Vendors;
GO
The stored procedure is ready for you to create an SSRS report.
You can tweak the views or tables that the information is being pulled from and the columns. This is just a starting point to help you get the data from all the databases dynamically. For more information on how to create an SSRS report, please feel free to reach out to us by submitting a contact form below.