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