Is there a way in a custom report to only give the user certain choices in filters, e.g. only show them groups that belong to a parent group?

Question

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.

Answer 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,
        ),
      ),