Wednesday, June 11, 2014

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!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Monday, June 2, 2014

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!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Sunday, May 25, 2014

The Dynamics GP Blogster in Johannesburg, South Africa

My travels will take me to the beautiful and cosmopolitan city of Johannesburg, South Africa to conduct Microsoft Dexterity training for customers in the area. I will be there from Sunday May 25, 2014 through Saturday, May 31, 2014 and I am looking forward to meet a number of you.

I will be available in the afternoons after 6:00 PM local time and as time permits. For those of you who decide to tag along for dinner or otherwise, table topics are fairly open. If you are a prospective customer considering Microsoft Dynamics GP and would like some insight, please feel free to drop by as well. Please feel free to contact me by adding your comment to this blog and I will get in touch with you or click the email button on the right.
Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Tuesday, May 20, 2014

reIMAGINE 2014: You are the difference!

No, my post title it's not an official slogan for the conference, but the reIMAGINE 2014 conference in Fargo promises to be just what Microsoft Dynamics GP partners had been expecting for years: and all out sales, technical consulting, and software developer extravaganza that combines the likes of the former Great Plains Software's Stampede to Fargo and the Microsoft Dynamics GP Technical Airlift.

But there's a caveat: as a partner, YOU get to provide feedback on the content and topics you want to see and learn about. So here is where I ask for your support: David Musgrave and I would like to team up once more to bring you some of the best development content you could ask for and why not, even to convince you (again!) as to why you should be using and deploying the Support Debugging Tool with all your implementations - yes, David Musgrave is coming!!

If you would like to see us both at the conference, presenting in a fast paced, but really fun environment, we encourage you to provide feedback. Heck, I'm going out on a limb and say even if you don't want to see us, go and provide feedback nonetheless.

reIMAGINE 2014 Microsoft Dynamics GP, Hosted by Dynamic Partner Connections

Fargo, North Dakota
The conference will take place from November 10 - 13, 2014 in the lovely city of Fargo, North Dakota, with training classes being conducted on or around the same time of the event (please check the schedule for updates).

Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Monday, May 19, 2014

"Invalid Object Name ..SY01500" error after restoring GP 2013 company database in a different environment

Just recently I was working with a partner to determine the root cause of an issue that stemmed from restoring a company database from a customer's production environment, to the partner's development environment.

After restoring the database, we could see the following error messages attempting to access the database:

Invalid object name SY01500 error

smCleanupFilesBeforeLogin stored procedure error

Login failed error

Enabling a DEXSQL.LOG gave us more insight into the issue. We could see the following prepared statement being executed prior to the ODBC error message:

/*  Date: 05/18/2014  Time: 19:55:37
BEGIN DECLARE @stored_proc_name char(34) DECLARE @retstat int DECLARE @param5 tinyint DECLARE @param6 smallint DECLARE @param7 tinyint DECLARE @param8 tinyint DECLARE @param9 tinyint DECLARE @param10 tinyint DECLARE @param11 tinyint DECLARE @param12 int set nocount on SELECT @param5 = 0 SELECT @stored_proc_name = 'ZBPI.dbo.smCleanupFilesBeforeLogin' EXEC @retstat = @stored_proc_name 5, 'sa', 'Company_Name', 0, @param5 OUT, @param6 OUT, @param7 OUT, @param8 OUT, @param9 OUT, @param10 OUT, @param11 OUT, @param12 OUT SELECT @retstat, @param5, @param6, @param7, @param8, @param9, @param10, @param11, @param12 set nocount on END
/*  Date: 05/18/2014  Time: 19:55:37
SQLSTATE:(S0002) Native Err:(208) stmt(148745784):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DYNGP2013..SY01500'.*/

The prepared statement calls the dbo.smCleanupFilesBeforeLoging stored procedure, which has (68) hardcoded references to the former system database in the old environment. A look at the SY00100 table, confirmed the backup had been taken from an environment with a different system database name.

We had to formulate a plan of attack to address the issue, so we decided for a two step approach which involves:

a) Updating the SY00100 table to reflect the current system database, and

b) Execute the Database Maintenance utility to rebuild all stored procedures and functions, triggers, and views for all products installed in the environment - more about Database Maintenance utility here. After all, they certainly would be other objects referencing the former system database.

This approach resolved the issue completely and the partner could now access the company database in their development environment without having to restore the customer's system database as well.

Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Tuesday, May 13, 2014

Working with the Dex.ini Settings in Microsoft Dynamics GP 2013

Dex.ini settings have always existed to provide developers and end-users with ways to set a number of preferences for their Microsoft Dynamics GP application without the need of additional development or customizations. With the release of Microsoft Dynamics GP 2013 and the web client, the Dex.ini now plays a key role in enabling (or disabling) certain elements rooted in the system's architecture itself. Today, I will address 3 new Dex.ini keys that will assist every day system administrators, developers, and hosting partners in managing their Microsoft Dynamics GP 2013 environment.

Per-User Dex.ini

With the introduction of the web client in Microsoft Dynamics GP 2013, the development team needed to make a few changes to how the Dex.ini file is handled. The result is a launch Dex.ini file (or global Dex.ini) that contains the settings necessary to get the user connected to Microsoft Dynamics GP; and a user-specific Dex.ini file that contains the user settings used after a successful connection has been established. The settings in the user Dex.ini are persisted in the Microsoft Dynamics GP system database (SY01405 - syUserDexIniSettings) so the next time they log into the system, the user Dex.ini file can be created with “their” settings. This functionality also works well in a Remote Desktop Services (aka Terminal Server) configuration where multiple users are sharing a single GP client installation.

NOTE: Traditionally, the Dex.ini file would be copied to the user profile folder to have user-specific Dex.ini settings. With Microsoft Dynamics GP 2013, this is no longer necessary.

This functionality is enabled via a Dex.ini switch in the launch Dex.ini files. (i.e. The Dex.ini used to launch GP, by default the one in the installation directory’s Data folder.)


The switch is set to TRUE by default when installing the Web Client Runtime components during the Microsoft Dynamics GP 2013 installation. It can be enabled in a Remote Desktop Services (RDS) environment by editing the launch Dex.ini file and setting the switch to TRUE,

From an Independent Software Vendor (ISV) perspective, if your Dexterity application uses the Dex.ini file, you will now have a new optional parameter to choose what file you want to access via the Defaults_Read() and Defaults_Write() functions. You can then read or define if whether a setting is a global (launch Dex.ini) or per-user setting. The default is per-user if not defined, with a fallback to read or write from/to the global Dex.ini.

NOTE: If you are a Visual Studio Tools developer, you can access the Default_Read() and Default_Write() functions through the DexDefaultsRead() and DexDefaultsWrite() helper functions provided, respectively. You can read more about the helper functions library here on MSDN.

Suppressing the Server Data Source Drop-Down

In addition to the above changes released with the RTM version of Microsoft Dynamics GP 2013, a couple of other Dex.ini changes were added in Microsoft Dynamics GP 2013 SP2 (12.00.1482) to better support shared environments - a shared environment being one in which a single instance of the Microsoft Dynamics GP client is being used by multiple users or one in which there are multiple tenants/customers sharing a server.

The first change was added primarily for service providers that have multiple customers sharing Windows servers. In this situation the server could have data sources for multiple customers and you won't want user's from customer A seeing the data sources for customer B. In this case you can add the following switch to the launch Dex.ini that will default the data source field to the value from the Dex.ini and disable it.


Suppressing the Last User in the System

The second change was added for RDS environments where multiple users are sharing the same Microsoft Dynamics GP instance and you don't want the user name of the last user to successfully login to default into the field. In this case you can add the following switch to the launch dex.ini that will not default in the last user name.


If you are interested in reading more about Dex.ini settings, please visit MVP Leslie Vail's blog and read her article Dex.ini switches – my complete list!. Leslie has worked hard to build this list over the years.

Special thanks to Daryl Anderson, Sr. Program Manager in Microsoft in Fargo for providing the base content for this article. Daryl is heavily vested with web client deployments on Windows Azure and have written most of the materials and "how to" topics for deploying web client on the Azure platform.

Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Monday, May 12, 2014

Preventing Visual Studio Tools customization processes from being terminated by Microsoft Dynamics GP

Just recently I ran into a case on the Microsoft Dynamics GP partner forum where the ISV developer was dealing with a potentially long standing process to be executed from within their Visual Studio Tools (VST) add-in customization. As a result of this long standing process, the developer wanted to prevent Microsoft Dynamics GP from closing while his process was executing.

The Theory Part

Typically, while a process is executing in Microsoft Dynamics GP a user attempting to close the application would receive the following message:

Process are being run message

However, Microsoft Dynamics GP can cause a Visual Studio Tools customization to abruptly shut-down if a user closes the application deliberately or accidentally. Microsoft Dexterity developers have never had to worry about this since Dexterity processes are managed by the call stack.

A call stack is an internal Dexterity mechanism that manages the processing of scripts. Dexterity has eight call stacks; seven are used for foreground processing while the other is used for background processing. When the scripts on a particular call stack have finished processing, the call stack is available to be used again. Scripts in the call stack can be viewed in Process Monitor whether they are foreground, background, or remote processes.

Process(es) in call stack while running a Trial Balance report

In some instances and depending on the type of process, a Dexterity developer have the ability to mark such a process as removable from the call stack, in which case the end-user can take advantage of the Process Monitor window to delete the process from the call stack.

The Solution

In order for a Visual Studio Tools add-in to protect itself from termination by the Microsoft Dynamics GP application while processes are being executed, you can add the following lines of code before and after the process to be executed.

C# Implementation
//tell Dexterity's runtime engine that we are running background.

// Execute your process(es) here
// tell Dexterity runtime engine we're done

Hopefully you have found this article useful.

Special thanks to Patrick Roth with the Escalation Engineering team in Fargo for posting the response on the partner forum.

Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Thursday, May 8, 2014

Visualizing Long Calculated Expressions in Report Writer

Granted, MVP Mark Polino hates Report Writer (RW), but even he will admit he loves a good RW nugget when he sees one.

Several times I've been asked "how do I visualize long Report Writer calculated field expressions?". At simple glance, it would seem that the horizontal list box control is all we have to visualize the expression, which could be far too complicated to deal with when working with large computations or even when invoking Report Writer functions.

RW horizontal list box expression field

As it turns out, there's a little known feature in this window that allows you to visualize long expressions. If you click on the "Calculated:" or "Conditional:" prompt - depending on the expression type - next to the horizontal list box, it will display the full calculated or conditional expression in the Calculated Expression window.

Calculated Expression window
It would be nice if we can get the zoom property for the prompt field and the zoom button set to true. This would display the classic blue hyperlink as it is now customary on Microsoft Dynamics GP windows where zoom buttons are prevalent.

Until next post!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Friday, May 2, 2014

KB Article 878449 Capture Login script gets a much needed rehaul

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!

Mariano Gomez, MVP
Intelligent Partnerships, LLC

Monday, March 31, 2014

Microsoft Dynamics GP 2013 Virtual Machine image from the Windows Azure Gallery

Part 2/2 - The Experience

In my previous article, Part 1 of the series, I talked about provisioning a Microsoft Dynamics GP 2013 development environment from the Windows Azure gallery. If you are already an MSDN subscriber and are already taking advantage of Azure, there isn't much new to you as far as provisioning a VM from the gallery goes.

Microsoft Dynamics GP 2013 VM image

One of the first things you will notice on the desktop are shortcuts to Microsoft Dexterity, Microsoft Dynamics, Visual Studio Base Image Icons, a link to the Getting Started with the Microsoft Dynamics GP 2013 Developer Image, and Visual Studio 2013 Premium Edition.

I started out by loading the Getting Started help file, which contains tons of information on all the development tools installed and links to resources. I then proceeded to open Visual Studio 2013, which of course, went through the traditional and lengthy initialization process after entering my Microsoft Account information. At the end, however, I was able to validate that both the Microsoft Dynamics GP Add-In and Microsoft Dynamics GP Web Client Add-In projects were available to begin a Visual Studio Tools project if needed.

Next, I launched Microsoft Dynamics GP 2013 (12.00.1482) and was quickly faced with the login window. I figured the much needed sa password could be find in the Machine Configuration section of the help file provided and sure was. However, I was also disappointed to know that this image is delivered with Microsoft SQL Server Express 2012. Nonetheless, I reminded myself this is a development environment. Unfortunately, the SQL Server Express provisioning script did not incorporate the Advanced Services tools which leaves you without SQL Server Management Studio Express.

If you need to add other Microsoft Dynamics GP components or even the SQL Server Express tools (like Management Studio), you can find the media under the C:\ConfigureDeveloperDesktop\Software folder. There's media for Microsoft Dynamics GP 2013 with SP2, Microsoft SQL Server Express 2012, and Workflow for Microsoft Dynamics GP 2013.

There C:\ConfigureDeveloperDesktop folder also contains a Script folder. In this folder you can find some batch files and PowerShells scripts to deploy the web client and configure additional environment components. Be sure to follow the instructions provided in the Getting Started help file before you attempt to run any of these.

Batch Files and PowerShell scritps

NOTE: you can always add the local SQL Server Database Engine to Visual Studio and build queries using the Visual Studio editor.

To develop eConnect or Web Services for Microsoft Dynamics GP-based integrating applications, you will need to run the ConfigureGPWebServices script in the ConfigureDeveloperDesktop\Scripts folder on the desktop. This script will install and configure the eConnect for Microsoft Dynamics GP and Web Services for Microsoft Dynamics GP components as these are dependent on machine and domain information.

The script for provisioning Microsoft Dynamics GP Web Client is in the C:\ConfigureDeveloperDesktop\Scripts folder. Running the ConfigureGPWebClient script will complete the installation of the web client to the point where you need to run the Microsoft Dynamics GP Web Client Configuration Wizard to compete the configuration. The Microsoft Dynamics GP Web Client Configuration Wizard can be launched from a shortcut on the start screen. The configuration wizard will ask for connection information to the SQL Express instance and then create the databases.

The web client script will create a self-signed security certificate for configuring the web client for SSL communication. To access the web client…

1.      Launch internet explorer and type the following URL, replacing with the name you provided for the virtual machine. (https:///GP) 

2.      You will be asked for credentials to log in. Type in the name and password of the machine administrator account you were logged in with when running the ConfigureGPWebClient script.

To be frank, the Developer's VM image is well stacked and provides all the goodies you will ever need for development purposes. The most important thing to keep in mind is, this is hosted on Windows Azure and as such your disaster/recovery redundancy is unparalleled by anything you have on premise. Think about moving your demo and dev environments to the Windows Azure platform as this will probably be the replacement of the famous Hyper-V images previously delivered via PartnerSource.

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC