Use this link to share with your colleagues:
Awesome Admin: Using Time Values in Reports: https://help.pm.leapevent.tech/a/1690853
Have you ever wanted a report with a chart that shows your busiest times of day for ticket sales, grouped by phone/walk-up versus online? This is a great resource as you plan box office staff schedules for your upcoming season, and could even be broken out by day of week. Or what about a report of scanned tickets that displays your busiest entry times, perhaps related to the time before the show? All of this is possible with custom fields and reports/dashboards. Join us as we walk through some examples to get you started!
This webinar is geared toward PatronManager admins and reporting enthusiasts.
We recommend setting the video quality to 720p (click on the gear at the lower-right of the embedded video) and watching the video in full-screen mode, if possible.
Additional resources
PatronManager Resources:
- Introduction to the PatronManager Admin Certification Program
- All About Barcode Scanning
- Add-On Reports and Dashboards for Box Office
- All About the Client Community
Salesforce Resources:
Sample formulas from the webinar
Click each section to view/copy the sample formulas referenced in the webinar.
These are the two fields we built in the webinar in order to answer the following question with a report:
- Which entry points are busiest and when
- How traffic flows in the hour leading up to a performance
First custom field: Scan vs Instance (minutes)
Object: Ticket Order Item
Field label: Scan vs Instance (minutes)
Field type: Formula, return Number, 0 decimal places
Description: Subtracts Entry Date from Instance Date and displays the result in minutes.
Formula: (copy and paste from below)
(PatronTicket__EventInstanceDate__c - PatronTicket__EntryDate__c)*24*60Second custom field: Scanner Label
This field is optional, and only useful if you a) scan tickets with an Android device, and b) have your devices labeled and used specifically by entrance.
Object: Ticket Order Item
Field label: Scanner Label
Field type: Formula, return Text
Description: The Label field from the Entry Device (scanner) used to scan a ticket.
Formula: (copy and paste from below)
PatronTicket__EntryDevice__r.PatronTicket__Label__cThese are the two fields we built in the webinar in order to answer the following questions with a report:
- What days and times are busiest and quietest in the box office
- Grouped by month, to map over our season
- Grouped by order origin, to compare phone vs walk-up sales
- Bucketed into blocks to match our shift schedule
First custom field: Order Created Day
Object: Ticket Order
Field label: Created Day
Field type: Formula, return Text
Description: Converts Created Date to day of week, prefixed with a number for sorting.
Formula: (copy and paste from below)
CASE( WEEKDAY( DATEVALUE(CreatedDate)),
1, "7-Sun",
2, "1-Mon",
3, "2-Tue",
4, "3-Wed",
5, "4-Thu",
6, "5-Fri",
7, "6-Sat",
"Error")You can adjust the leading numbers (the ones inside the quotation marks) for your preferred sort order. In other words, if you prefer that the week start with Sunday, use "1-Sun" instead of "7-Sun".
Second custom field: Ticket Order Created Hour
This field is optional, and only useful if you a) scan tickets with an Android device, and b) have your devices labeled and used specifically by entrance.
Object: Ticket Order
Field label: Created Hour [TIME ZONE]
Field type: Formula, return Number, 0 decimal places
Description: Converts Created Date to time only, rounded down to the nearest hour. Displays in [TIME ZONE] (regardless of date)
Formula: (copy and paste from below and adjust as instructed for your preferred time zone)
VALUE(MID(TEXT(CreatedDate-(0.0417 * 5)),12,2))The example formula above is for Eastern Standard Time. To display a different time zone, replace the number 5 in the above formula with the number of hours offset from UTC (GMT).
For example, Pacific Daylight Time is 7 hours behind UTC, so the formula would read as follows:
VALUE(MID(TEXT(CreatedDate-(0.0417 * 7)),12,2))
If your time zone is ahead of UTC, you'll also replace the minus sign in the above formula with a plus sign. For example, Australian Eastern Standard Time is 10 hours ahead of UTC, so the formula would be:
VALUE(MID(TEXT(CreatedDate+(0.0417 * 10)),12,2))