Using Sales Order Queries To Report On Attendance And Tickets

Maybe you set up a program with multiple events and would like to see how many patrons attended each event. Also, how many of the tickets printed were scanned for admittance? Here we will talk about a few popular methods of using a Sales Order query to report on attendance and tickets.

Let's discuss building a sales order query to see how many patrons attended each program event. We will first begin our query by:

1. Navigate to Analysis, then click on Information library
2. Click Add an ad-hoc query
3. Select source view of Sales Order and click OK

Next we want to filter on the program(s) we want to review:

1. In the left column, expand Sales Order Item, expand Sales Order Item Ticket, and select Program. From the middle column, drag Name into Include Records Where. Set Equal to your program name.
2. We also want to exclude any tickets that were refunded:
3. From the left column, select Sales Order. From the middle column, drag Refund status text into Include records where.  Set Equal to Not Refunded.
   • Note: Manually type Not Refunded. This is spelling and case sensitive.

Then we'll decide what we want to see in our results. For this example, we'll keep it simple and only show the Program Event Name, Start Time and Ticket count:

1. In the left column, expand Sales Order Item, expand Sales Order Item Ticket, then select Program Events. From the middle column, drag Name into Results fields to display.
2. From the same middle column, also drag Start date into Results fields to display.
3. In the left column, select Sales Order Item. In the middle column, drag Quantity into Results fields to display.
4. Within Results fields to display, select Quantity. Click on the Summarize button and select SUM.
   • Note: Sales Order Lookup ID defaults within Sales Order queries. If you do not wish to see this in your output, within Results Fields to Display select Lookup ID then Delete using the red X.

Now, what if we are not so much interested in the count per event, but the number of events per constituent? We will use the same above steps, but adjust our Results fields to display:

1. Within Results fields to display, highlight each of the following line items and click the red X to delete: Lookup ID, Name, Start Date, and Quantity
2. Next, we'll decide what we now want to see in our results. For this query, we want to see patron name, and the number of times they have purchased a ticket to each event within this program.
3. In the left column, select Constituent. From the middle, drag Name into Results fields to display.
4. In the left column, expand Sales Order Item, expand Sales Order Item Ticket, select Program Events. From the middle column, drag Event record into Results fields to display.
5. Within Results fields to display, highlight the Event record line. Click the SUM button and select COUNT.

Now that we're happy with our patron's names and their attendance counts, we instead want to see how many of these patrons had their tickets scanned:

1. Within Results fields to display, we will keep our Constituent name, but highlight Event record and click the red X to Delete the line.
2. In the left column, expand Sales Order Item, expand Sales Order Item Ticket, and highlight Tickets. From the middle column, drag Scan date into Results fields to display.
3. Let's also say we want to see the price type for each ticket purchased: From the same middle column, drag Price Type Code into Results fields to display.

Finally, we're happy with the ticket scan dates but want to see how many tickets were purchased per price type per constituent:

1. Within Results fields to display, we will keep our Constituent name, but highlight Ticket Scan date and click the red X to Delete the line.
2. In the left column, expand Sales Order Item, expand Sales Order Item Ticket, and highlight Tickets. From the middle column, drag Price Type Code into Results fields to display a second time.
3. Highlight the second Price Type Code line item within Results fields to display, then click the Sigma button to COUNT the tickets per price type.
   • Note: If you do not care for Constituent name and prefer to see only number of each price type, Delete the Constituent name line.

I hope this has been helpful in displaying the versatility of a sales order query. Below are some additional resources detailing additional popular Sales Order queries which may prove to be helpful in the future as well:

• How to get a list of constituents who used a certain discount
• I need to report on discounts by program and price type
• How to report on scheduled program event revenue by price type