Month: August 2018

When the Reporting Tools are deployed for Microsoft Dynamics GP a fact pane is enabled in the navigation lists, which gives access to the reports. However, this also causes the navigation lists to be much slower. In some cases, pretty much unusable.

While users can switch off the fact pane on navigation lists, they need to be disabled by each user on each list. This means people don’t actually use them.

It is possible, however, to disable the lists using some SQL against the system database (by default called DYNAMICS.

When a user opens a navigation list for the first time, an entry is made to the List View Options (SY07225) table. The first script inserts a trigger after update to switch off the fact pane:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
IF EXISTS (SELECT * FROM sys.triggers WHERE OBJECT_ID = OBJECT_ID(N'utr_AZRCRV_UpdateSY07225SetFactBoxVisibleOff'))
	DROP TRIGGER utr_AZRCRV_UpdateSY07225SetFactBoxVisibleOff
GO

CREATE TRIGGER
	utr_AZRCRV_UpdateSY07225SetFactBoxVisibleOff ON SY07225
AFTER INSERT AS
	UPDATE
		['List View Options']
	SET
		FactBoxVisible = 0
	FROM
		SY07225 AS ['List View Options']
	INNER JOIN
		inserted
			ON
				inserted.ListDictID = ['List View Options'].ListDictID
			AND
				inserted.ListID = ['List View Options'].ListID
			AND
				inserted.ViewID = ['List View Options'].ViewID
			AND
				inserted.USERID = ['List View Options'].USERID
GO

The second script, is a run once script after the trigger is created; it sets all existing entries in the table to off so that existing users don’t see the fact pane:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
UPDATE SY07225 SET FactBoxVisible = 0
GO

This SQL script was written for a client with a large number of databases and which took a long time to update and they needed to upgrade through several versions of Microsoft Dynamics GP. The script checks for start and end times in the DB_UPGRADE table; when they match for all rows, an error is raised.

The script was scheduled using SQL Server Agent which was configured to send an email when the error was raised; this allowed him to know when part of the upgrade had completed and the next stage needed to be started.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @UpgradeComplete AS INT

SELECT
	@UpgradeComplete = CASE WHEN COUNT(PRODID) = 0 THEN 1 ELSE 0 END
FROM
	DB_UPGRADE
WHERE
	start_time = stop_time

IF (@UpgradeComplete = 1)
	/*
	throw error because all companies upgraded
	if <> 0, that means upgrade still running and nothing needs to occur
	*/
	RAISERROR ('GP 2016 upgrade complete; next stage needs to be started',11,1)