Get a report of person attributes for everyone who has attended a group

  • By Michael Garrison

This will be a quick article. I developed this page for someone asking a question in Slack, and ended up finding it so useful I figured I'd share it.

The end result is going to be a page on which you can select any check-in group and a person attribute. Once you click the Go button, you'll get a report of people who have attended that group, and their attribute value for the attribute you selected.

For instance, you could use this to get a report of everyone's allergies who have checked in to your Nursery group.

I've heavily edited this article. The original article used SQL and wasn't something to make available to all staff. This version uses Entity Commands and is "safe" to provide for staff. If you'd like the original article though, scroll to the bottom.

Start by creating a new page, wherever you'd like it in the menu hierarchy. Perhaps under Tools -> Reporting, but I leave that up to you. On that page, add a single HTML block and enable RockEntity on its Block Settings menu.

Add the following contents to your HTML block:

v7.5 and below v8.3 and above

        <td style="padding-right:2em;">
            <b>Attribute:</b><br />
            <select id="attribute" name="attribute" style="width:100%;max-width:50vw;">
                {% unless PageParameter.AttributeId %}<option></option>{% endunless %}
                {% attribute where:'EntityTypeId == 15' %}{% comment %}                                                                              --EntityTypeId=15 is Person {% endcomment %}
                    {% for att in attributeItems %}
{% comment %} (you can add an optional check for certain attributes here, but remember that your users can override attributeid in the url {% endcomment %}
<option value="{{ att.Id }}"{% if att.Id == PageParameter.AttributeId %} selected{% endif %}>{{ att.Name }}</option> {% endfor %} {% endattribute %} </select> </td> <td style="padding-right:2em;"> <b>Group:</b><br /> <select id="group" name="group"> {% unless PageParameter.GroupId %}<option></option>{% endunless %} {% group where:'GroupTypeId != "10"' %} {% for g in groupItems %} {% if g.GroupType.InheritedGroupTypeId == 15 or g.GroupType.InheritedGroupTypeId == 16 or g.GroupType.InheritedGroupTypeId == 17 %}{% comment %} -- GroupTypeId=15,16,17 are Check in by Ability, Age and Grade {% endcomment %} <option value="{{ g.Id }}"{% if g.Id == PageParameter.GroupId %} selected{% endif %}>{{ g.Name }}</option> {% endif %} {% endfor %} {% endgroup %} </select> </td> <td style="padding-right:2em;"> <b>Attendance after:</b> (yyyy-mm-dd)<br /> <input id="date" name="date" value="{{ PageParameter.Date }}"></input> </td> <td> <button onclick="javascript:window.location.href = '{{ 'Global' | Page:'Scheme' }}://{{ 'Global' | Page:'Host' }}{{ 'Global' | Page:'Path' }}?AttributeId=' + $('#attribute').val() + '&GroupId=' + $('#group').val() + '&Date=' + $('#date').val();">Go!</button> </td> </tr> </table> {% if PageParameter.AttributeId and PageParameter.GroupId and PageParameter.Date %} <div class="clearfix"></div> <table class="grid-table table table-bordered table-striped table-hover"> <thead> <tr> <th>Person</th> <th>Attribute Value</th> </tr> </thead> <tbody> {% capture AttKey %}{% attribute id:'{{ PageParameter.AttributeId }}' %}{{ attribute.Key }}{% endattribute %}{% endcapture %} {% assign lastPerson = 0 %} {% attendance where:'StartDateTime > "{{ PageParameter.Date }}"' sort:'PersonAliasId asc' %} {% for a in attendanceItems %} {% if a.Occurrence.GroupId == PageParameter.GroupId %} {% if lastPerson != a.PersonAliasId %}{% comment %} -- we are looping attendance records, so we use this to eliminate people we've already shown. Do this by sorting by PersonAliasId and then skip any row with the same PersonAliasId as we just showed {% endcomment %} {% assign lastPerson = a.PersonAliasId %} <tr> <td>{{ a.PersonAliasId | PersonByAliasId | Property:'FullName' }}</td> <td>{{ a.PersonAliasId | PersonByAliasId | Attribute:AttKey }}</td> </tr> {% endif %} {% endif %} {% endfor %} {% endattendance %} </tbody> </table> {% endif %}

Save, and ... that's it! (You will need to press Save twice because the HTML parser for some reason thinks we're using incorrect syntax).

I've added a few comments you can look for in case you want to further filter the results shown, and also to explain a few of the constants I used, so look for those longer lines. You can, of course, also edit the entity commands to look for other grouptypes instead of looking for checkin groups, etc. Jump into Slack's #Lava channel and let us know if you need help making those changes. I'm usually available there

Click to toggle the original sql-driven article



Spark Development Network
Flagstaff, AZ

Michael Garrison recently left his job in Architecture to become one of the "new guys" at Spark (the "Core Team"), but he's still helping out Christ's Church of Flagstaff and other non-profits with tech needs in his off-hours, trying to make computers do what computers do best, so that people are freed to do what we do best: relate with people!