Global Reports

We are looking to deploy SmartList Builder + SmartView as an enhanced reporting solution for our internal customers. One of the things I have seen floating around the net is that you can’t use SmartList Builder with a SQL Linked Server. In fact, a number of Dynamics MVP’s alluded to this in a support thread at http://www.ms-news.net/f1610/smartlist-builder-odbc-error-8692687.html. The actual error message will look something like this:

GPS Error 58

SQL Error 7405 ODBC SQL Server Driver

Heterogeneous queries require the ANSI_NULL and ANSI_Warnings options to be set for the  connection.
This ensures consistent query semantics. Enable these options and
then reissue your query.

ODBC error 37000

I’ll show a little further into the article that yes, you can work around this, and pretty easily too – it just takes a little sleight-of-hand with your SmartLists.

A little background – we have 150+ Dynamics GP companies, all on one database server. We also frequently have requests to produce reports which consolidate data from all of these entities onto one SmartList so that, in one report, users can quickly see data from every entity. Up until now, we have been using the technique I outlined here to create reports which use a linked server to execute a stored procedure inside a view using OPENQUERY. In order to make this work, I had to come up with a workaround to the very ANSI_NULLs issue other people have reported as stopping them in their tracks.

The secret boils down to this – you have to trick AnyView, or SmartList Builder into accepting the view the first time you add it. Once you do that, and build your SmartList, you can change what the view is actually doing after the fact. I know that it works with AnyView, and have done a test with SmartList builder and it works there as well.

For the steps below, assume the final query you want to use looks something like the code below. Column1 is varchar(5) and Column2 is TINYINT:

select Column1, Column2 from LINKEDSERVER.SAMPLEDB.dbo.SAMPLETABLE

Here are the steps you need to follow:

Create a SQL view with the columns and field lengths that match the “final” report you want to produce:

CREATE VIEW SampleView
 
AS
 
SELECT convert(varchar(5),'') as Column1, convert(tinyint,0) as Column2
 
GO
 
GRANT SELECT ON SampleView TO DYNGRP

Add that view to SmartList Builder / AnyView and build your SmartList off that view.

Once you have successfully built the object, ALTER the view to call the code from the linked server:

ALTER VIEW SampleView
 
AS
 
--SELECT convert(varchar(5),'') as Column1, convert(tinyint,0) as Column2
select Column1, Column2 from LINKEDSERVER.SAMPLEDB.dbo.SAMPLETABLE

Voila! Your SmartList will run the linked server code, with no ANSI_NULL warnings!

I have used this approach for most of the SmartLists we have in Production today, with great success. Of course, using a linked server is not, in the end, an ideal solution, but for cases when you have to, the method above will allow you to use SmartLists with SQL Linked Servers successfully.

Note: I have updated the sample code above to include the “GRANT SELECT” permissions to “DYNGRP” on the view. If you don’t do this, SmartView will spin forever on the “Loading…” screen when you try to run a SmartList.

Last post I explained how using the OPENQUERY SQL syntax can help skirt the 260 tables in a view limit of SQL Server. Today I’ll show how to combine that tip with additional logic to automatically add new Dynamics GP company databases to your custom global reports.

If you have a large number of global reports, or even if you only have a few, maintenance can be a hassle whenever a new Dynamics GP company is added to your system. I have devised a solution to this maintenance headache by combining a custom table which holds the name and code for my global reports, with a nightly procedure which will rebuild all of the global objects automatically. The items that make up the solution are:

1. A custom table, stored in DYNAMICS, with the following structure:

• Report ID – a unique identifier for the report
• Report Name – the name of the SQL object
• Report Code – the report code I want to run against every Dynamics GP database
• Report Type – whether the SQL object to build is a stored procedure or a view

An example of what a record in the table would look like is:

Note the “REPLACEME” line in the “Report Code” field. This value will be replaced with the database name when the item below is run.

2. A stored procedure, also stored in DYNAMICS, which is called every night from a SQL Job. This procedure does the following for each entry in the table above:

• Gets a list of database from the SY01500 table in DYNAMICS. The field it returns is INTERID.
• Dynamically builds a SQL statement which will DROP the existing SQL object, based on the Report Name and Report Type values from the table.
• Replaces the value REPLACEME in the Report Code string with the INTERID value.
• Dynamically builds a SQL statement to CREATE the SQL object, based on the Report name and Report Type values from the table.
• Grants EXECUTE permissions to DYNGRP to the SQL Object.

As an example, thejob above would dynamically generate, then execute, the SQL String below:

USE DYNAMICS

GO

IF EXISTS (SELECT name from sysobjects where name  = ‘usp_NonVoidedSOPDocs’)

BEGIN

DROP PROCEDURE usp_NonVoidedSOPDocs

END

GO

CREATE PROCEDURE usp_NonVoidedSOPDocs as

SELECT * FROM COMPANY1..SOP30200 WHERE VOIDSTTS = 0

UNION ALL

SELECT * FROM COMPANY2..SOP30200 WHERE VOIDSTTS = 0

UNION ALL

SELECT * FROM COMPANY3..SOP30200 WHERE VOIDSTTS = 0

GO

GRANT EXEC ON usp_NonVoidedSOPDocs to DYNGRP

By using this technique, new Dynamics GP company databases can be automatically added to your custom reports without any intervention on your part – freeing you up to work on other tasks.

Next week, I plan to talk about why I am such a fan of the Field Service module in Dynamics GP and how the way it has been designed is such a boon to analysts and developers who need to customize it’s behaviour.