How to build reports or Views based on who the Manager is

Question

We need some summary reports of Count of open Cases by Case Manager.

Maybe overlooking something but not spotting these.

Just getting to grips with how Case Manager is identified. Seems to initially be the person who 'creates' the case, until that gets reset at which point an Activity of Type "Assign Case Role" is created and the Manager is the assignee, status Completed.

But if the Manager is changed, it just adds another one. So trying to report eg in Views, by grabbing the Activity of that type/status gives multiple results.

Wondering which way to go around this.

Keen to hear ideas for flexible approaches that might be useful to others.

Seems I had forgotten about using the Case Manager Relationship as an option!

Answer 1

As for how to do aggregation on custom Views fields, I can speak to that. This answer doesn't provide how to decide what is an open case or having a case manager...I'll leave you to that, but here's how you can aggregate it once you get there!

This can be done with a custom Views field handler + Views Aggregator Plus module.

The Views custom field handler must be specially constructed. Below is an example from one of my projects. I've removed some logic from this example...The key is that you calculate and set the values to the rows in the post_execute() method, with a property name that starts with "field_", with a ['raw'] array index value. If you do that then the Views Aggregator Plus module will "see" the data and be able to manipulate it.

In this example I calculate the number of enrollments to courses for each user, and set that value. The View lists users, and includes this field, and is then summed via the php aggregation. You can do something similar, and then use get a count. In your case you would probably return 1 if the row (case) is open with a manager, and not set it or set it null if it is closed or not having a manager.

Views Aggregator Plus enables PHP based aggregation to views as opposed to SQL based aggregation which is default with Views.

class tincan_course_product_handler_user_total_enrollments_field  extends views_handler_field {
  function construct() {
    parent::construct();
  }

  function query() {
    $this->ensure_my_table();
    $this->add_additional_fields();
  }

  function post_execute(&$values)
  {
    foreach($values as $index => $value) {
      if(!empty($value->uid)) {
        $uid = $value->uid;
      }
      elseif (!empty($value->users_civicrm_uf_match_uid)) {
        $uid = $value->users_civicrm_uf_match_uid;
      }
      $total_courses = NULL;
      if(!empty($uid)) {
        $user = user_load($uid);

        $total_courses = tincan_course_product_get_users_get_total_default_product_count($user);    

      }
      $values[$index]->field_total_enrolled_courses['raw'] = $total_courses;
    }
  }

  function render($values) {
    return !empty($values->field_total_enrolled_courses['raw']) ? $values->field_total_enrolled_courses['raw'] : 0;
  }
}

Views Aggregator Plus works similarly to standard Aggregation, but you must use the provided Format, "Table with aggregation options" provided by the module.

Answer 2

One thought would be to add a field to CiviCase and use post_hook and triggers (?) to update this any time a change of Manager is done.