CiviCRM Community Forums (archive)

A client of mine has a need for mildly sophisticated reporting. We worked up an interface for the search parameters screen and it allows for searches such as:

Show all contacts with no 'owner' (where 'owner' is a subset of CiviCRM tags[1]) assigned who attended at least one event of type X between dates A and B.

or

Show all contacts with one of the following 'owner' tags (here is a list of checkboxes, one for each relevant tag) who have listed an activity of type X between dates A and B.

More complexity is planned for Phase II to add filtering by custom fields such as:

Show all contacts with one of the following 'owner' tags (here is a list of checkboxes, one for each relevant tag) who have listed an activity of type X where custom field Y was set to Z (where the custom field is part of the activity) between dates A and B.

The results will need to display a table of each found contact with columns like:

• Owner
• Number of Events of type X attended
• Number of Activities of type Y recorded
• etc...

We will also need various totals and also relative totals shown at the bottom, such as:

• Total number of Events of type X attended
• Total Number of Activities of type Y recorded
• Number of Contacts who attended an Event of type Z who later have an Activity of type A recorded
• etc...

Also we want, eventually, graphs and also historical comparisons, to show the differences in various totals between various date ranges. By this I mean, for example, to compare the "Number of Contacts who attended an Event of type Z who later have an Activity of type A recorded" in 2009 to that of 2008.

As we finalize the Search UI for Phase I, I realized that even the UI became a bit more complex than initially planned and so I began to wonder if using a third party reporting solution might make more sense. I have no experience with BIRT and my knowledge of it in fact is limited to a somewhat brief review of some of their online documentation.

Based on that, however, it is not entirely clear to me if there would be a long-term advantage in learning BIRT now as opposed to proceeding with custom Drupal code. My guess is that the custom Drupal code, even though it is mostly not very re-usable, will provide the required flexibility, including features such as a mouse rollover on the "Number of Activities of type X recorded" table cell can show a popup listing the details of those activities, such as date and subject.

I am not sure if BIRT can do all that we will need or not. I am told that the high-end reporting tools, such as Crystal Reports, as supposed to be very full-featured and flexible and robust, but I have no personal knowledge of these tools to know precisely to what extent this is true.

Can anyone provide any input to assist with this issue?

Thank you,
Hershel

[1] For the record, the definition of inclusion in this subset is by virtue of having the string 'primary' or 'secondary' in the name of the tag.

CiviCRM custom search framework is a feature I have looked at but never yet tried. I may try it, but seeing as how anyway the heart of such an approach is hand-coded SQL, I don't entirely see the advantage of that over running the SQL directly against the DB myself. In this situation in particular, where the result columns we need custom defined and also the nature of the search paramaters are somewhat custom defined.

Regarding Views2, this is a site which has plans and hopes one day to upgrade to Drupal 6, but that path is entirely non-trivial at this point so that will take some time and some work. I think that that upgrade is farther off than these reports.

Thanks.

Thank you. I also took that approach.