How to report on scheduled program event revenue by price type

When you hold a program event, you may wish to see the number of tickets sold by price type and the associated revenue. This solution will guide you through the steps to build a query to display this information in Altru. I need to see how many of each ticket sold. 

Option #1: Run the Program Revenue Report. This report displays the revenue and number of tickets sold for scheduled and daily program events by event date.

Option #2: Run the Sales Report or Sales by Price Type report. These use transaction date instead of event date for reporting.

Option #3: Create the following query. Note: This solution covers how to report on scheduled programs and their attendance. To report on the attendance of daily admission programs, please use the Daily Admission Report. The Daily Admission report includes admission ticket counts for all sales methods.


Start your query:
  1. Go to Analysis > Information Library
  2. Click Add a new ad-hoc query
  3. Select the source view of Sales Order.
Add fields to Include Records Where: 
  1. To report on a specific program event: In the left column, expand Sales Order Item, then expand Sales Order Item Ticket and select Program Event. From the middle column, drag Program Event Record into Include records where. Set Equal to your program event.
  2. OR If you would like to include all program events happening in a specific date range: In the left column, expand Sales Order Item, then expand Sales Order Item Ticket and select Program Event. From the middle column,drag Start date to Include records where. Set this equal to your date parameters.

Add fields to Results Fields to Display: 

  1. For the name of the program: In the left column, expand Sales Order Item, expand Sales Order Item Ticket and select Program. From the middle, drag Name to Results fields to display.
  2. For program category: In the left column, expand Sales Order Item, expand Sales Order Item Ticket and select Program. From the middle, drag Category into Results fields to display.
  3. For the date of the event: In the left column, expand Sales Order Item, expand Sales Order Item Ticket and select Program Events. From the middle, drag Start Date into Results fields to display.
  4. For the name of the program event: In the left column, expand Sales Order Item, expand Sales Order Item Ticket and select Program Events. From the middle, drag Name into Results fields to display.
  5. To see information about the source of the transaction (online, daily sales, advance sales, group sales): In the left column, select Sales Order. From the middle column, drag Sales method type into Results fields to display.
  6. To add the number of tickets sold: in the left column, select Sales Order Item. From the middle column, drag Quantity to Results fields to display.
  7. To add the dollar amount of those items sold: In the left column, select Sales Order Item. From the middle, drag Net Amount to Results fields to display.
    • Note: Net Amount field will display how much was paid for the item (cost minus any discounts). 
  8. To add the number of tickets refunded: In the left column, expand Sales Order Item and select Refunded item. From the middle, drag Quantity to Results fields to display. 
  9. To add the dollar amount of those items refunded: In the left column, expand Sales Order Item and select Refunded item. From the middle, drag Total to Results fields to display. 
  10. In order for the refund fields to calculate properly: Within Results Fields to Display, select your preferred node. Click the Sigma sign and check the box to SUM.
    1. User-added image
  11. To add Price Type: In the left column, expand Sales Order Item, then select Sales Order Item Ticket. From the middle column, drag Price Type Code to Results fields to display.

Your final query will look similar to this one: 
User-added image

TIP: Use the pencil icon to edit the name of the field headers in your results.

Using Excel to open the .csv, you may use features such as Autosum, Pivot Tables, and OData to report on the data in different ways, please visit Microsoft Support for further assistance with Excel.

Environment

 2.7, patch 14

Was this article helpful?