x Want to participate? Join the group to interact
Sage CRM
Related
Recommended

Restricting Access to a Report to Specific Times of Day

1 minute read time.

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

user_per_admin=3

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.

  • Jeff,

    I agree, a like button would be great. As always your articles provide great ways to do things within CRM that I would have never thought of. This is an excellent case in point. I can't tell you how many times I've taken an idea of yours and ran with the concept. Kudos are more than due!

  • Stacy

    I wish we had like buttons on article comments as I do like your suggestion - you spotted the weakness in my suggestion. But to be fair to me I did try and come up with something that was minimal code.

  • Very nice, but there is one big issue... that the users wouldn't even know that category existed (it would be hidden to them prior to 6pm).

    You could also try:

    1. Create a report group called "Hidden". For now leave the SQL clause empty (we will modify it once we're done creating our report).

    2. Create your new long running report under the Hidden category.

    3. Create an .asp page in the reports directory under the category folder you want the report to be in (something other than Hidden). Inside the .asp page test if the time is after 6pm, and if so redirect to the CRM.URL of the report action ( discussed here: community.sagecrm.com/.../some-thoughts-on-sage-crm-fop-pdf-and-reports.aspx) passing in the right parameters, and if not write a message to the user, stating that the report can only be run after 6pm.

    4. Change the Hidden report category sql clause to always be hidden ..put in 1=2 in the SQL clause.

Generic Content
Generic Content

*Community Hub is the new name for Sage City