Aloha, everyone! As you all know, CiviCRM is shipped with a lot of useful report templates for each CiviCRM entities. You can use these templates to create difference report instances to interrogate your data.

However, if you are report geeks like us, you might sometimes find that the existing templates don’t quite give you the flexibility you need. There is the excellent extended reports extension which you should definitely check out here; but if you are trying to do something a bit more complex; or looking for a graphical output then your may still be exporting your data into Excel or Google sheets to do more analysis…

The CiviCRM Pivot Report extension intends to change all that by providing a powerful pivot table functionality, directly within the CiviCRM interface!

You can download it now from the CiviCRM extension directory and it is also available for instant install by your usual methods within the extension “Add New” tab in your CiviCRM admin menu.

*For those who are a little bit more numbers oriented, you may be familiar with the concept of “Pivot tables” from spreadsheet applications like Microsoft Excel, Libre Office or Google Sheets.

Overview

For those who are familiar with the Activity Pivot Report extension we released in early 2016, this extension has everything Activity Pivot Report extension has and much more.

CiviCRM Pivot Report uses a d3 based pivot table library to provide users a highly flexible drag'n'drop UI which enables them to structure the x and y dimensions of a report for most (we’re still waiting on a few!) major CiviCRM entities. Report data can be filtered via any data field and can be counted/ calculated in different ways.

Whats more, the pivot reports can be transformed to different charts or exported as CSV/ TSV files.

Supported entities and data

CiviCRM Pivot Report supports all following entities and they can be found from the top CiviCRM menu Reports -> Pivot Report.

• Activity

• Case

• Contribution

• Membership

• Prospect (if the CiviCRM Prospect Extension is installed - more details on that shortly!)

CiviCRM Pivot Report will work with any of the main data fields for an entity (including any custom fields that you have added!).

Building a report

Let's take the example of building a report based on contribution data.

To do so you navigate to Reports -> Pivot Report -> Contribution to open the contribution pivot report.

You might want to analyse the contributions broken down by “Financial Type” and “Payment Method”. In this case, you can simply find the “Financial Type” field from the axes pool on the left hand side and drag it into the X axes area. Then drag the “Payment Method” field into the Y axes area.

You can now see the a table showing:

• the number of contribution you received per “Financial Type” per “Payment Method”

• the total number of contributions per “Financial Type” regardless of “Payment Method” in the last column

• the total number of contributions per “Payment Method” regardless of “Financial Type” in the last row

Filter and calculation

Now since you have a table of all your contributions broken down by “Financial Type” and “Payment Method”, you might want to filter it to only see the contributions made in 2017.

In order to do so, you can simply find the “Received Date” field in the axis pool and click on the filter button. Because the field we are filtering is a date field, you will be able to narrow down the records by specifying the date range in the filter popup. Once confirmed, the result in the table will only include the contributions whose “Received Date” is within 2017.

Also instead of seeing the counts of the contributions, you may want to see the sums of “Total Amount” instead. You can achieve that by changing the calculation from “Count” to “Sum” and select the field “Total Amount” to sum with from the second dropdown list revealed.

Save and Load Report Configurations

Now you have created a report with a few axes, you also added some filters and changed the calculations. You might look at the nice report and wonder “great, now do I need to do this again next time I want to see the same report?”

Don’t you worry, you can also save report configurations!

On your report, click on "Save As New" button and type in a name for your report in the popup then click on “OK”. Your report configuration is saved at this point. When you come back to the report next time, by simply selecting the configuration from the dropdown list, you will be able to reproduce any complicated report in no time.

Export and Charts

Once you have built your perfect report, there are many ways you can interrogate your report result further. In CiviCRM Pivot Report, you can always export your report as a CSV/ TSV file to perform more spreadsheet magic. While if you want to see a more graphic view of your data, the extension is shipped with multiple chart and graph views which you can transform your report to.

Large Dataset Handling

The previous iteration of the extension struggled to work quickly with large (>100k) datasets. We've worked hard to add a solution to this and now have a "caching" solution to help speed up data loading time.

A "Pivot Report Cache Build (chunk)" scheduled job is now available once the extension is installed. Each time the job is executed, it will build cache for all data records or a part of the data records if necessary. It might take a few executions to complete the cache building for your entire dataset depending on your dataset size but this will allow the entire cache building process to be handled in the background without bringing any performance impact to the normal usage of the system.

Also, with "CiviCRM Reports: Admin Pivot Report" permission, admins will be able to view the last cache refresh time via "Administer -> Pivot Report Configuration" and manually refresh the entire cache when needed.

Future developments

We have big plans for this extension and hope to reach a level of feature parity with the existing reporting solution within CiviCRM in the not too distant future (including ability to add as dashlets and also email reports). We’ll also be making it all “Shoreditch” in time for the release of the new theme!

Download and Install

Enough talking and it’s time to try it out yourself.

The CiviCRM Pivot Report releases can be found in CiviCRM extension directory and also in Compucorp Github.

Please note that you need the latest stable CiviCRM 4.7 to use this extension.