1

I've written a custom report, lots of trial and mostly error. One of the columns is group. Since we have almost 300 groups, I'd like to limit the groups the user can include in the report to a subset. (Only certain groups make sense with this report and no one wants to wade through 300 groups to find the several they want.)

Clearly using filters in the _columns is the way to go but I'm not sure just what to put where. I've found examples of hard coded values but I'd like a bit of sql. Maybe there are some examples that my grep didn't find of sql statements.

Here is the basic sql I'd like to use. Maybe it only needs to return the id instead of the title.

select g.title from civicrm_group g, civicrm_group_nesting n where g.id = n.child_group_id and parent_group_id in (124, 125)

The report already limits the groups to these but users may only want one or two groups and not the full 20 or so groups the defaults return.

4
  • Is this a custom CiviReport or is it made with SearchKit?
    – Coleman
    Mar 10, 2023 at 19:02
  • Custom CiviReport. I have a custom search and want to automatically email the results on a weekly bases. I don't think that is possible with queries and I as far as I can tell maybe not with SearchKit. Plus since I had the sql already (46 lines of code) it seemed far easier to just make it a custom civireport using civix generate:report than to recreate it all in SearchKit. Plus I don't see how to have a derived field in SearchKit and I'm using a couple of views I created which I don't think are available through SearchKit
    – Paul-Tahoe
    Mar 11, 2023 at 1:01
  • In that case you might be able to achieve it by overriding that field in the buildForm function.
    – Coleman
    Mar 14, 2023 at 13:39
  • The filters of _columns seem like one should be able to do this. There is the options parameter of filters that the civi reports reference docs talk about fixed values, e.g. array('1'=>'Low', '2'=>'Medium', '3'=>'High'). Everything I've tried to populate an array hasn't worked.
    – Paul-Tahoe
    Mar 23, 2023 at 0:17

1 Answer 1

1

It turns out the filters option of the _columns is what I wanted, but civicrm_group is bit of a special case. The problem I finally stumbled upon is at the end of the __construct function is a call to parent::__construct(). (I have a lot of columns defined, so for me it is a ways down so I didn't see it right away.) That ends up overwriting what I had in the filter for civicrm_group. Right above this call, two variables get set to TRUE, _groupFilter and _tagFilter. Setting the groupFilter to false prevents the parent construct from overwriting it.

So for completeness in the answer I'll give what code I ended up with for what it's worth.

So at the top of the function __construct I have this. There are probably better ways to do this:

  $sql = "select id, title
          from civicrm_group g
          where g.id in (
              select n.child_group_id
              from   civicrm_group_nesting n
              where  n.parent_group_id in (124,125))          
          order by title";

$dao = CRM_Core_DAO::executeQuery($sql);

while ($dao->fetch()) {
  $MyGroups[$dao->id] = $dao->title;
}

then in the filter section of _columns for group I have this

     'filters' => array(
        'gid' => array(
        'name' => 'group_id',
        'title' => E::ts('My Groups'),
        'operatorType' => CRM_Report_Form::OP_MULTISELECT,
        'type' => CRM_Utils_Type::T_INT,
        'options' => $MyGroups,
        ),
      ),

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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