Filtering on Dates - and how to deal with "Individually Scheduled Opportunities"
Getting the right date filters so that you get the right information in a report can be tricky business. Here's a few tips:
1) Decide exactly what the question is.
For example: When you say you want to know about the # of connections in 2011, what exactly are you asking?
- How many connections were made during 2011?
- How many connections took place during 2011?
Notice those are very different questions. If someone signed up for an occurrence that takes place in January 2011, but they signed up in December 2010 then you're going to get different answers depending on the date you filter by.
- If you want to know how many people signed up (made a connection) in 2011, then the Connection Created Date is what to filter on.
- If you want to know how many connections took place during 2011, then the Connection "Start Date and Time" is the relevant date.
2) Decide which object really has the dates you want to use.
The most reliable record types for date information are either Connections or Occurrences. There are a lot of date fields in the volunteer opportunity record, but its when the OCCURRENCE took place that's probably relevant. And for individuals (volunteers), the Connection record is the right place to go. Be careful that you select the right date field that most clearly answers the question you want to answer in the report.
What's the right field to use for Date & Time Specific Opportunities
If you want information on all the occurrences and connections that took place during the year 2011 -- then filter on the Occurrence Start Date and End Date:
Filter:
Occurrence Start Date and Time > or equal to 1/1/2011
Occurrence End Date and Time < or equal to 12.31/2011
This will only return occurrences that took place during 2011.
Individually Scheduled Opportunities present a challenge however!
Individually Scheduled opportunities only have one occurrence, and the start date might have been years ago -- and it might not end until some date in the future. So how do you filter to find Volunteer Opportunities that are Individually Scheduled -- that appeared on your site (and have connections to them), that took place in 2011?
Filter:
Occurrence End Date & Time greater than or equal to 1/1/2011 (This will rule out any Individually Scheduled Opportunities that ended BEFORE 2011)
Occurrence Start Date and Time less than or equal to 12/31/2011 (This will rule out any Individually Scheduled Opportunities that started AFTER 2011)
Note: This particular filter will work for Date & Time Specific Opportunities to. So we recommend using this filter whenever trying to pin down the number of occurrences that appeared during a certain time interval.
One Caveat: filtering Individually Scheduled Opportunities in this way, will cause connections made to Individually Scheduled Opportunities in 2010 and after 2011 also appear in the report. Because if you allow this one ISO occurrence into the report, ALL its connections will be listed as well. You probably want to filter further based on the Connection Start Date / End Date (if you are capturing this information).
Here's a report that uses that filter
Note: The illustration below was created when we still used "To Be Scheduled" opportunity type -- it has since been replaced by the "Individually Scheduled Opportunity Type"
This report is using a custom report type that includes Volunteer Opportunity, Occurrences, and Connections fields.
It has some lookup fields to the Contact Record so we can count the Number of unique volunteers. (A contact with a connection is a volunteer, right?)
Notice that the filter is looking at all occurrences that ENDED AFTER 2010, and Started BEFORE 2012.
We also filtered on only ACTIVE occurrences in this case.
The details are hidden, so we just see the calculated field showing us the number of guest volunteers (A sum of all the number of the guest volunteer field in the contact record), and the unique number of contacts, occurrences, volunteer opportunities and connections.
0 Comments
Add your comment