1

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!

  • 1
    It doesn't change your question but just mentioning it's only the creator initially if you have the creator checkbox checked on the case roles definition screen when setting up the case type. If not then there is no manager to start. – Demerit Jun 26 '17 at 6:12
  • cool thx for reminding me of this. am doing some digging on a site we didn't initially set up. – petednz - fuzion Jun 26 '17 at 7:05
2

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.

0

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.

  • To do it in Views, I find maintaining an extra field that duplicates information that's already there can lead to the two getting out of sync (it seems already awkward with case type and case status etc - any code needs to be aware that there's two places). The views handler could be updated to present a field who's value is the latest manager (or any other desired value). If you're willing to go outside of views, I like doing an export to a reporting warehouse, not just for CiviCRM data but pretty much everything, since then you can do anything. – Demerit Jun 26 '17 at 6:10

Your Answer

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

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