Deactivate All Reports (Except PM EFT Payment Register) In All Companies

Microsoft Dynamics GPMicrosoft Dynamics GP includes a large number of reports which will automatically print off when a transaction or batch is posted; and some postings produce many different reports.

When I implement a new client I typically leave them on during training with an instruction to identify which ones they want to use after go-live as I will switch off any I’m not told to leave on.

I used to do it the other way and tell people to let me know which ones they wanted switched off; however, this lead to all reports being left on and then it being mentioned a year or so down the line that they get all these reports printing that they don’t want.

So now, all get switched off unless I am specifically told to leave them on. As I’ve dealt with clients with many companies (I think the largest is 180 companies) this is not something I want to do manually.

Fortunately, the settings for whether the reports should print is stored in SQL table which means a SQL script can be written to switch them off in bulk.

The following script will use the Company MSTR table to get all companies in the sysem database and loop through all of them and switch off all reports except for the PM EFT Payment Register in all companies:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://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 @SQL_Statement VARCHAR(1000) DECLARE    cursor_InterID Cursor FOR    SELECT INTERID FROM DYNAMICS..SY01500        Open cursor_InterID    DECLARE @INTERID VARCHAR(100)    FETCH NEXT FROM       cursor_InterID    INTO       @INTERID    While (@@FETCH_STATUS <> -1)       BEGIN       IF (@@FETCH_STATUS <> -2)             SET @SQL_Statement = 'UPDATE ' + RTRIM(@INTERID) + '.dbo.SY02200 SET PRNTJRNL = 0 WHERE PRNTJRNL <> 0 AND PTGRPTNM <> ''EFT Payment Register'''             exec (@SQL_Statement)          FETCH NEXT FROM             cursor_InterID          INTO             @INTERID       END    CLOSE cursor_InterID DEALLOCATE cursor_InterID

As always when running an SQL script, check to make sure you’re happy with what the script is going to do and ensure you have a good backup before you run the script in case of problems.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

2 thoughts on “Deactivate All Reports (Except PM EFT Payment Register) In All Companies

Leave a Reply

Your email address will not be published. Required fields are marked *