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 2

---

In the previous installment, I outlined a technique to deploy your custom SQL Reporting Services reports using the standard Business Intelligence deployment utility provided by Microsoft Dynamics GP (Administration | Setup | System | Reporting Tools Setup). In this installment, I will provide the scripts needed to update the Microsoft Dynamics GP system database tables, sySrsReports and sySrsReportDataSources, respectively.

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Generic license.
declare @objectId uniqueidentifier;
declare @rptContent varbinary;
declare @reportName varchar(255);

-- custom report name information
set @reportName = 'Contract Deferred Revenue Detail Report';
select @rptContent = Content from ReportServer.dbo.Catalog where Name = @reportName;

-- setup a new GUID for the custom object
set @objectId = NEWID();

-- adds record for custom SRS report
insert into sySrsReports(ObjectID, LanguageID, DictionaryID, ObjectType, ObjectDescription, FolderName, DisplayName, TableName, CurrentVersion, MinSrsVersion, IsKpi, IsIsv, IsMultiCompany, IsCRM, IsConfigurationFile, BinaryBlob)
values(@objectId, 0, 0, 2, '', 'Sales', @reportName, 'MainTableNameHere', '12.00.1295', '10.5.1600', 0, 0, 0, 0, 0,  @rptContent)

-- add data source information with the following DataSourceType and DataSourceID values
-- DataSourceType DataSourceID
---------------------------------------------
-- 1   DataSourceGPSystem
-- 2   DataSourceGPCompany
-- 3   Custom data source name
-- 4   DataSourceCRMOrganization

insert into sySrsReportDataSources(ObjectID, DataSourceType, DataSourceID, ModelID)
values (@objectId, 3, 'YourDataSourceName', 'Model_ID_Value');

A bit about the script...

The first step is to declare a couple variables that will host the report name and actual report definition language (rdl) content for the report. This information can be found in the dbo.Catalog table in the ReportServer database.

Next, we need to create a unique identifier (GUID) value for the report that we need to deploy. With this information, we can proceed to add the SRS report information to the sySrsReports table.

We can then add the data source information to the sySrsReportDataSources table. Keep in mind that there needs to be an entry for each data source listed.

That's it!

Until next post!

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