This article has been prompted by a question I was asked at a recent Developer training course in Johannesburg, South Africa.
During the training course we discussed how when a user runs a large report it can place a load on the customer's server which in turn can create problems for other users of the Sage CRM implementation.
Even for a system with a well tuned database some reports that fetch large amounts of data, or are run without using criteria that limit amount of data returned, can create a noticeable drag.
So the question was asked, can a system administrator limit certain reports so that they can only be run outside core office hours? For example, could the report be restricted to be available to run only after 6pm?
We can take advantage of the fact that reports are grouped into categories.
Report Categories are manged by the System Administrator as a System Menu
Administration -> Advanced Customisation -> System Menus
The menu is called ReportsTabGroup.
The tab options in the tabgroup have a SQL clause that controls access. For example the Administrator Reports category (System Usage) has the SQL clause
This limits the access to users with the correct rights.
Reference has to be made to the user fields. But we can restrict access to a report category after a certain time by referencing the SQL date functions e.g. DATEPART().
To stop a user accessing the Sales report category until after 6pm the following SQL clause can be used.
user_userid is not null and DATEPART(HH,GETDATE())>18
You can find other articles about the Tab SQL clause here.