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|
|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)|
Upon inspecting the sySrsReports, the table description is as follows:
There's also the sySrsReportDataSources table, which points to each individual report:
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!
Mariano Gomez, MVP
Intelligent Partnerships, LLC