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

)

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.