Using Conditional Highlighting and Drill Down to perform analysis on summary calculations
We often want to know how many connections a volunteer had in a given time period. And there is a stock report in HandsOn Connect that can answer that question for you:
You'll find it in the HOC Basic Reports Folder: "# of Connections per vol (Calendar Yr)
Because this report counts connections, the number of records equals the number of connections that contact had during the Current Calendar Year.
If desired, you could filter further and only show connections with an attendance status of "Attendance Verified" if, for example, you wanted to know how many times they'd actually volunteered. (screening out their 'not attended' or 'unverified' service.
But ultimately, its easy to get a count of the number of connections each contact (volunteer) has.
But -- what if you want the 'count' of the number of contacts who volunteered that year? That isn't in the report.
Add the field "Number of Contacts" to the report, and summarize on it, and at the bottom of the report you'll know how many contacts.
It counts each individual contact and gives us a total.
We now know how many connections, and how many contacts. (Both are summarized at the bottom of the report)
But what if I'm only interested in volunteers (contacts) who have a certain number of connections in this time period.
A lot of folks contact us and ask how to create reports on "All volunteers who had 3 or more connections last year" - or "All volunteer who had 50 or more connections last year" and that's something you can't directly do within a report.
Here's why. You can't filter a report based on the calculated total of a summary field.
And the Number of Connections that appears in the report is a calculated total.
So you can't say can't ask for a report that only shows contacts with 3 or more connections in a calendar year, or contacts with 50 or more connections in a calendar year.
But by using an additional Salesforce report feature and the drill down functionality of reports -- we can analyze this report and get the answer we're looking for.
First we need to visually be able to see which contacts we're really interested in (based on the number of connections they have) - and to make these easy to spot, we're going to use conditional highlighting.
In the left picklist you can pick any of your calculated fields in the report. In this case I'm choosing "Connections Total" which is the total connections each volunteer has.
I've set parameters to conditionally highlight items as RED if they have less than 3 connections, and conditionally highlight them as green if they have 50 or more connections.
Now run the report and click on "Hide Details" so we're only seeing the contact records and their connection totals with our conditional highlighting. (It's easier to view this way).
Now I can see at a glance which contacts had <3 and >50 connections.
If I want to get a count on just the contacts who had more than 50 -- I could visually scroll through the report and manually count how many GREEN 'connections totals' I had -- and I'd have the answer.
But wait -- Now that we can quickly see visually which contacts have more than 50 connections, let's use the Drill Down Feature to filter this report even further!
Put a check mark next to each contact that has a 'green' connections total and at the bottom of the report, drill down by any info you might want -- I'm going to select "Full Name". (The reprot was using 'contact ID" but I want to see the names now.
Click the drill down button at the bottom of the report.
When I do this -- a filter is added that only shows the contacts I've checked, (the ones with more than 50 connections!
I can now see I have only 3 contacts who have >50 connections.
There's our answer!
Quick! Use "SAVE AS" and give the report a new name. (don't use 'save' or you'll overwrite the original report.
Let's call it "Volunteers with >50 connections current year"
You can now choose to 'show details" (if you want to see more detailed information about the connections.
We can continue to drill down to get any level of detail about these three contacts and their connections that we want.
This one report can be drilled down upon to get any level of information you want. Here's an example:
Now I can see, for these three contacts how many of their connections were declined, unreported (please verify), attended, cancelled, etc.
Continue to drill down as you wish. In this example I'm going to summarize the report so that only the "Attended (and hours verified" connections are shown.
And Valerie is our real winner! She attended 28 seperate connections this year, for a total of 68.90 hours of service!
This example allows you to visually filter the contacts with a certain number of connections.
But the uses are endless. You could use the field "Hours Served" instead, and find those who served "X" number of hours to qualify for the Presidential Service Awards.
You can also use conditional highlighting and drill down to get specific answers to ANY report that counts any 'summarized' calculation.