I'm building a Drupal 7 View in which I want to aggregate contribution records for the year, grouping by month. But View's native aggregation option happens after the query is generated, so it's not possible to group by MONTH(receive_date). I tried using the Views Date Format SQL module, which in theory should let me apply the group by on the query, but it doesn't seem to handle non-core-Drupal date fields.

I also explored using Views Aggregator Plus module, which does let me render a table with correct grouping. But my end goal is to display a chart (using the Charts module), so I need to be able to build the aggregate query without an alternate display format (you can't use VAP and Charts together -- they are mutually exclusive display format options).

I feel like the ability to use Views to generate aggregate stats on Civi data is probably something people are doing... but I'm getting stuck.

  • from memory grouping by eg month involved some Contextual filters - i have found a d7 view doing this based on Content. I suspect the equivalent filters do not exist for civi but haven't looked. i can send the view over if it is worth your time to look at how it works
    – petednz - fuzion
    Mar 3 at 18:17
  • I have not tried this, but I think it can be done. My theory is: Create your own sql query with aggregate built into the query, Then use this query for your view that uses the chart module. My initial Google search, looks like you need SQL Views drupal.org/project/sql_views to use your own query. Then you can use hook_views_data to get it in your View with Charts module. drupal.org/docs/7/api/database-api/sql-views
    – Iowa Boy
    Mar 3 at 21:29
  • If you are green at writing a sql query, create the view as you did with Aggregator Plus module. In Views > Settings under Live Preview you can check the box for "Show SQL Query" Then you can copy the query from the preview.
    – Iowa Boy
    Mar 3 at 21:29
  • I ended up creating a MySQL view and registering it using the views_data hook, which allowed it to be exposed to Drupal Views. Mar 3 at 22:28
  • Cool, Glad it worked
    – Iowa Boy
    Mar 4 at 1:08

Your Answer

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

Browse other questions tagged or ask your own question.