SQL Script to Disable Business Analyzer Fact Pane

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