Published
Monday, December 19, 2011 - 11:07
Written by
A client of mine at NS Web Solutions is looking to solve a problem involving the way their users need to handle reports.  I think this might be common enough to be relevant for others, so here's a bit about what we're planning to do.
 
Note: This is based on a short forum thread here: http://forum.civicrm.org/index.php/topic,22307.msg93500.html
 
This group has a large number of users who all rely on a variety of existing reports to do their work. When a report isn't just exactly right, users with "access Report Criteria" permission have the option to change the report criteria to modify the report, but of course that's only for a one-time use; if they want to use those same modified criteria the next day, they have to configure them all over again.  To get that that configuration saved for later use, they need someone with the "administer Reports" permission to re-create the report just as they want it, and save it as a permanently available report.
 
The problem here is the bottleneck in new report creation.  The more users a system has, and the more dynamic their business policies are, the more this bottleneck is felt.
 
A bad solution would be to give the "administer Reports" permission to everyone who wants it.  This is bad because then you have lots of people modifying system-wide reports, so that reports are no longer predictable and reliable.  If Bob changes a report's filter criteria in the morning, Alice will easily be confused by the report in the afternoon, because it doesn't contain what she expects.
 
A better solution is something I think will work for this client, and hopefully for others who may be feeling this particular kind of pain.  The basic idea is to give users some space to play with reports without polluting the site-wide report space.  We create a new permission called "administer own CiviCRM reports", and users with that permission can get a little freedom:
 
Starting with an existing system-wide report, the user can modify the report criteria and then save this configuration as a new report. Let's call this a "user-private report" for now, and debate a better name later. A user-private report is only visible to the user who created it, and can be modified and saved, or copied again, by that user only.
 
Any user with CiviCRM's "administer Reports" permission could then, at his or her discretion, promote any such user-private report to be a system-wide report, which would then behave exactly as any other system-wide report, meaning it would be visible to other users, can't be edited by the user who created it, etc.
 
At this point I'm planning to build this out as a freestanding Drupal module and release it under GPL at Drupal.org. It will be good to see if this is at all useful for other CiviCRM users and installations.
 
A short list of requirements for this module looks something like this:
  • Each user has a private space for reports visible and editable by that user alone.
  • Users can copy any report that they can access into their own private report space.
  • Report listings do not show user-private reports, except those belonging to the current user. (This includes civicrm/report/list; I'm thinking it will be hard to block these reports from the CiviCRM navigation menu, and the simplest thing might just be to disallow private reports from being added to the navigation menu.)
  • Any user-private report must refuse access by users other than the owner.
  • Users must have CiviCRM's "edit report criteria" and this module's "administer own CiviCRM reports" permissions to use these features.
 
I'm looking forward to any feedback from the community on this concept.

 

 

 

 

 

 

 

Comments

I like the idea, and have experienced similar needs. One other possible solution to consider --

We could create a reserved flag for reports, which has it's own permission. Similar to how reserved tags and other reserved elements work, such objects are locked down unless the user has the reserved permission. For reports, I would envision that flag preventing people from updating reports. If they have the permission to alter report criteria, they can still "tweak" an existing report on a temporary basis, or save as a new report instance, but may not update the report they are working working with.

The end result is similar, but it approaches the problem from a different perspective which might be easier to implement. The challenge I see with having "user-owned" reports is that you're not just dealing with vertical permissioning (user can't promote a report to be a site-wide report) -- but also peer-permissioning (Jane can't modify Jim's report, but can she see it? Can Jane see it but Fred not see it? Can Mary edit it but Fred not see it? Can Alice the admin modify and save it, as well as promote it?). That would be a useful feature, no doubt, but definitely more work, especially as you work out the implications on navigation, report listing, dashlets, etc. 

That sounds like a nice option, the reserved one....I would probably prefer that one.

Adding a "reserved" flag to reports seems like a good way to layer some additional benefit on top of user-private report management.  I don't think it would replace it entirely, since it adds only one more level of access: currently we have "administer reports" as a boolean permission, and this adds only one more boolean permission "administer reserved reports."  

 

Peer-permissioning of user-private reports is a great thing; although it multiplies the complexity of the work, it might be worth it.  

It may be time to start considering a page-and-folder oriented design, as the number of reports is starting to outgrow the menu. Proprietary systems often boast over 100 standard reports. The permissions thing could use a bit more granularity too. Bob the Development Director runs 8 different versions of a contribution summary report each month. Susie the Membership Director needs to run the same reports, and occasionally make one-off changes for her needs. Bob would have options for each report like 'visible to everyone', 'visible to me only', 'editable by everyone', 'editable by me only'. This means Bob can make his report visible, but not save-able. Now Susie can leverage Bob's work, and even make changes to criteria as needed, but she can't overwrite his reports. Instead, Susie can start with Bob's report instance, change the criteria, and save it as her own if needed.

I realize talk is cheap and code is gold, but my skills are in SQL and Crystal (I'm actually a report developer by day). I worked several years in tech support for a proprietary donor CRM that is considered the industry 'gold standard'. I'd be glad to talk design (or even create a detailed functional spec) and share things I've seen work well (and not so well) if that's useful. Side note, I'm willing to write the SQL for new reports if stored procs are allowed and there are some requirements in Jira.

 

the fact that you are willing to work on the design / spec makes the code aspect a lot easier. I think code/design/documentation/tests are the equivalent to code in terms of gold status :)

 

Wanna do a detailed post on the forums with a design proposal or two. Might be good to think about "report templates" vs "report instances" (including using different / better terminology), permissions and structure / hierarchy that will scale for us going forward. We have approx 30-40 reports now, so i suspect we'll get up to 100 in the next few releases

 

Once the above is moving, we can potentially think about making report creation even easier

 

lobo

 

 

Sure, I'll work on a spec proposal over the Christmas holiday and post on the forums. Is it better suited for the Feature Requests or Developer Discussion forum?

 

since you are going quite a bit beyond a feature request :)

 

we can iterate on forum and then if someone wants to develop it, we'll move it over to wiki as a more formal spec

 

lobo

 

I haven't forgotten about this. I have a lot of projects at my day job and run a nonprofit at night (which uses CiviCRM). In between, I've been writing up a spec of how CiviReports could behave and creating mock screenshots of the user experience. I'm continuing to make progress and will post on Developer Discussion when I'm close.

Allen, I appreciate your blogging and creative solution and agree that creating the right set of reports for a variety of larger organizations is generally not a solvable problem.

From my experience most larger commercial CRMs or association management software either (1) build their own reporting solution (2) integrate in a reporting solution such as business objects or Crystal. I think long term having a good integration with Jasper, the largest open source reporting tool makes sense for CiviCRM.

We've take this approach with our larger clients either by installing Jasper or allowing them to integrate Crystal or other reporting solutions that they already own. With larger organizations they generally have some report building skill sets and with a little data model training they will be able create most all reports themselves.

We like Jasper (and our larger clients like Jasper) because it has a very large community, the largest install base, excellent tools, seems seamless to the report users (they think it is part of CiviCRM)and we find it easy to use for most clients with "access skills".