Using Microsoft Dynamics GP Business Intelligence deployment utility to deploy custom SSRS reports

Using Microsoft Dynamics GP Business Intelligence deployment utility to deploy custom SSRS reports - Part 1

---

I was in Johannesburg, South Africa this past week visiting a customer with the distinct challenge of working with an extensive number of company databases - one for each company division. While I was there to provide Microsoft Dexterity training, undoubtedly other topics of conversations tend to surface at various points.

In this occasion, I was the one doing some learning as the customer pointed me to a method they devised to deploy SQL Reporting Services Reports (SSRS) to multiple companies by using the standard Microsoft Dynamics GP Reporting Tools Setup window. The company have over 50 custom reports of all kind which can take more than a day to deploy if they were to do so manually, once a new division database is rolled out.

Reporting Tools Setup window

Under their current situation, the customer simply want to click the Deploy Reports button on the setup window and watch their custom reports added to any new company, along with the standard SSRS reports bundled with the Microsoft Dynamics GP application.


BI Deployment Progress window

I was very intrigued as to how they accomplished this and in doing some research, I launched Microsoft SQL Server Profiler to perform a trace while the reports deployment was in progress. The trace constantly showed the deployment process reading report information from the sySrsReports and sySrsReportDataSources tables located in the system database.

SQL Profiler trace (click to expand)

Neither of these two tables, nor the Business Intelligence Deployment Progress window seem to be a standard part of the Microsoft Dynamics GP dictionary (Dynamics.dic), but rather Visual Studio Tools add-ins to the Microsoft Dynamics GP application, therefore it was necessary to run a couple T-SQL SELECT statements to determine the content of each table mentioned above, given they could not be determined via standard tools like the Support Debugging Tool's Resource Information or the Microsoft Dynamics GP's Resource Description window.

Upon inspecting the sySrsReports, the table description is as follows:

sySrsReports description

There's also the sySrsReportDataSources table, which points to each individual report:

sySrsReportDataSources description

Remember, they may be more than one data source being used on a report, in which case you will need to have more than one entry from the same Object ID.

These two tables serve as repository of all base reports needed for deployment across companies. The Dexterity portion of the code (the Reporting Tools Setup window) takes care of retrieving the missing reports from each company.

In the next installment, I will provide the queries required to fill these two tables with custom SRS information that can be used to deploy the reports across various companies using the standard deployment window.

Until next post!

MG.-
Mariano Gomez, MVP
Intelligent Partnerships, LLC
http://www.intelligentpartnerships.com/