Creating a Report to monitor team signups as a percentage of total connections
Use the following filters to restrict the report:
- All Volunteer Opportunities, regardless of date
- Team Member: Team member ID is not null (This will only show the connections where the person is a member of a team)
- Connection Status equals "Confirmed" (This is optional - but we're only interested in upcoming connections where the volunteers have been confirmed to attend.
- Occurrences with Connections (This is a cross filter, to filter out occurrences that don't have connections).
You can add more or fewer fields depending on what info you want to see in the report.
- Connection iD
- Team Name
- Connection Start Date & Time
- Occurrence End Date & Time
- Number of Connections (Summarize on this field to get the SUM of this number)
- Maximum Attendance
- Role (So we can find who the team captain(s) are easily
- Contact: Full Name
- Volunteer Opportunity Name: (Summarize on this field)
- Occurrence ID (Summarize on this field as a second summary after the Volunteer Opportunity Name)
Create a Formula Field called "Team Percentage" to calculate what percentage of the max volunteers are team members:
The formula is: HOC__Connection__c.HOC__Number_of_Connections__c:SUM / HOC__Volunteer_Opportunity__c.HOC__Maximum_Attendance__c:AVG
We are just dividing the sum of the number of connections (which are the team connections) by the maximum attendance of the volunteer opportunity (We take the average since we want the one number, not its sum) And we express this as a percentage.
Add this to your layout.
Use Conditional Highlighting on the "Team Percentage" field, to create color alerts that show the occurrences with the greatest percentage of Team Connections
In the "Show" Menu, choose conditional highlighting, which will open a window for you to set the colors and break points of your conditional highlighting.
I've set 1% as the first breakpoint, and 30% as the second (and set the color to red).
What this will do is mark all occurrences with 1% or less team connections to green (I can ignore these), and yellow highlight those that have even a few team connections to yellow. If over 30% of the connections for a given occurrence are team connections it will be highlighted as red.
It's the upcoming occurrences with red highlighting, where I'll want to contact the team captain and make sure their team members are coming!
Here's how my report looks when I run it. (Note: It shows ALL upcoming occurrences, whether or not they have ANY team connections at all -- but the number of connections remains at zero.)
Notice that I can now clearly see that the occurrence of Arts & Crafts with Kids on July 15 has 88% of its available slots taken up by team connections. So I'm going to give team captain, Valerie Volunteer a call and make sure she is planning to show up with all the team members she signed up!
Whether you want to filter out TBS or all express interest occurrences is up to you! Just build the report out to meet your actual business needs when it comes to monitoring team connections.
Optionally - you can create a dashboard component to make it easy to keep an eye on upcoming opportunities with a high percentage of team volunteers coming up.
For the dashboard component type shown above, you need to add a chart to your report -- and then build the dashboard component to meet your needs. Clicking on the dashboard component would take me to the report itself.
I could additionally filter my report to only show connections or occurrences coming up in the current month, etc... as a way of keeping only the most relevant and immediate data out front for me to think about.