Last post I explained how using the OPENQUERY SQL syntax can help skirt the 260 tables in a view limit of SQL Server. Today I’ll show how to combine that tip with additional logic to automatically add new Dynamics GP company databases to your custom global reports.
If you have a large number of global reports, or even if you only have a few, maintenance can be a hassle whenever a new Dynamics GP company is added to your system. I have devised a solution to this maintenance headache by combining a custom table which holds the name and code for my global reports, with a nightly procedure which will rebuild all of the global objects automatically. The items that make up the solution are:
1. A custom table, stored in DYNAMICS, with the following structure:
- Report ID – a unique identifier for the report
- Report Name – the name of the SQL object
- Report Code – the report code I want to run against every Dynamics GP database
- Report Type – whether the SQL object to build is a stored procedure or a view
An example of what a record in the table would look like is:
| Report ID | Report Name | Report Code | Report Type |
| 1 | usp_NonVoidedSOPDocs | Select * from REPLACEME..SOP30200 where VOIDSTTS = 0 | Stored Procedure |
Note the “REPLACEME” line in the “Report Code” field. This value will be replaced with the database name when the item below is run.
2. A stored procedure, also stored in DYNAMICS, which is called every night from a SQL Job. This procedure does the following for each entry in the table above:
- Gets a list of database from the SY01500 table in DYNAMICS. The field it returns is INTERID.
- Dynamically builds a SQL statement which will DROP the existing SQL object, based on the Report Name and Report Type values from the table.
- Replaces the value REPLACEME in the Report Code string with the INTERID value.
- Dynamically builds a SQL statement to CREATE the SQL object, based on the Report name and Report Type values from the table.
- Grants EXECUTE permissions to DYNGRP to the SQL Object.
As an example, thejob above would dynamically generate, then execute, the SQL String below:
USE DYNAMICS
GO
IF EXISTS (SELECT name from sysobjects where name = ‘usp_NonVoidedSOPDocs’)
BEGIN
DROP PROCEDURE usp_NonVoidedSOPDocs
END
GO
CREATE PROCEDURE usp_NonVoidedSOPDocs as
SELECT * FROM COMPANY1..SOP30200 WHERE VOIDSTTS = 0
UNION ALL
SELECT * FROM COMPANY2..SOP30200 WHERE VOIDSTTS = 0
UNION ALL
SELECT * FROM COMPANY3..SOP30200 WHERE VOIDSTTS = 0
GO
GRANT EXEC ON usp_NonVoidedSOPDocs to DYNGRP
By using this technique, new Dynamics GP company databases can be automatically added to your custom reports without any intervention on your part – freeing you up to work on other tasks.
Next week, I plan to talk about why I am such a fan of the Field Service module in Dynamics GP and how the way it has been designed is such a boon to analysts and developers who need to customize it’s behaviour.
