9

I am working with an organisation that uses CiviCRM to record information about their clients. They need to generate reports on a quarterly basis that summarise their work with clients. The reports need to segment the data by factors such as age range, gender, ethnicity, postcode etc.

None of the standard reports generate the information that they need so they use advanced search to obtain the figures for the reports. This gives them the information they need however it can be quite tricky to set up the appropriate searches. They also need to run the search several times for each age/gender/ethnicity etc.

Some examples of the types of reports they need (all figures are for the last quarter):

  • number of clients referred - split by ward
  • number of clients supported - split by ethnicity and gender
  • number of clients supported - split by age range (e.g. 16-17, 18-19, 20-25, etc.)
  • number of hours of support provided
  • number of sessions delivered per ward

How could reporting be made easier for them?

  • 1
    What CMS are they using? If they are using Drupal 7 then the Views module may provide a solution. – JoAnne Mar 25 '15 at 10:54
  • 1
    Can you give a specific example of a need that the canned reports don't provide? What kinds of fields, groupings, filters? Perhaps they're used to working with pivots and you could set up a set of saved export fields and a saved Excel/LibreOffice pivot template. – Nicholai Mar 25 '15 at 13:47
  • I've added some examples. Yes, they could export the data to a spreadsheet, but this would require some work to manipulate the data into the format they need. It'd be better if they could view it within CiviCRM. – William Mortada Mar 26 '15 at 11:22
  • 1
    I know this thread has been quiet for awhile but...We use views to generate the reports that you listed and more. 95% of our case managers don't see Civi at all. – pmoz Sep 28 '15 at 12:34
  • Thanks Paul, I have been looking at views as an option for creating reports for a different project. – William Mortada Sep 28 '15 at 17:38
5

Another alternative is writing a custom report. It's not all that difficult, though it is writing PHP code. Even if you need to hire out for it, you'll have exactly what you need.

The details on how to create a report template extension are here: http://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Report-Template+Extension

Most likely, you'll just need to take an existing report and make changes such as:

  • adding an order_by option in the __construct() function for the fields that you want subheadings for
  • adding a new table in the __construct() function and creating the proper joins in the from() function
  • doing some changes to how things appear by doing something in the alter_display() function
  • adding statistics for fields in the __construct() function and defining them in the select() function
  • Could you provide more details (or a link) of how to get started with this? – William Mortada Mar 25 '15 at 11:42
  • 1
    Just elaborated a bit on how it works. – Andrew Hunt Mar 25 '15 at 13:55
  • Thanks for your help. I've managed to create a custom report template but found the code pretty hard to fathom. I've also found it a bit of a pain if you need to include any custom fields in the report and do calculations like count or sum. It all ends up rather messy. – William Mortada Sep 22 '15 at 15:17
6

We have had great success using Jasper Reporting with CiviCRM.

It does require knowledge of the underlying CiviCRM database, and writing SQL against it, but the formatting capabilities are powerful and fairly easy to use.

Once a report is created, it can easily be added to the CiviCRM menu.

More details here: https://civicrm.org/blogs/lcarter/civicrm-and-jasper-reports

  • What are the requirements for this? It sounds like you would need Java on your server to support this. – William Mortada Mar 31 '15 at 11:43
  • 1
    Yes, Jasper Reporting does require Java. However, it is super easy to setup an Amazon EC2 instance along with Jasper using Bitnami to do the installation. Quite inexpensive to make happen (I have seen generally $20-$50 per month). Bitnami also can install to Google Cloud and MS Azure. As well has downloadables for your own platform. – DaveD - BackOffice Thinking Mar 31 '15 at 12:05
5

Since they're using Drupal, you may want to check out Forena Reports. There will be a session on using Forena Reports with CiviCRM at CiviCon Denver in three weeks.

4

If they are doing it all by using variations on advanced searches you could make a a custom searchand then (if you are in Joomla, but you can do the same kind of thing in Drupal or WP) make a module with the direct links to each one or give them a drop down.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.