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

Unknown   Juni 10, 2014   Comment  

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

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

Unknown   Juni 02, 2014   Comment  

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/

KB Article 878449 Capture Login script gets a much needed rehaul

KB Article 878449 Capture Login script gets a much needed rehaul

Unknown   Mei 02, 2014   Comment  

We live by it, we die by it. KB article 878449, How to transfer an existing Microsoft Dynamics GP, Microsoft Small Business Financials, or Microsoft Small Business Manager installation to a new server that is running Microsoft SQL Server (KB 878449), is perhaps one of the most visited articles in the Microsoft Dynamics GP world, especially around upgrades. The article contains a script that, under normal circumstances, retrieves all the security principals and encrypted passwords on an existing Microsoft SQL Server. The result is a T-SQL script that allows you to restore those security principals on another Microsoft SQL Server.

Months aback I had seen numerous posts on the partner forum and the Microsoft Dynamics GP community where folks expressed having issues with the Capture Logins script on SQL Server 2012. Now David Musgrave over at the Developing for Dynamics GP blog has a definitive and answer and a solution to the problem and explains in great detail the issue experienced by many in his article Capture Logins Script from KB 878449 generates Invalid object name error.

Don't miss out on this article.

Until next post!

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