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. 

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

The Sales Report or Sales by Price Type Report uses transaction date instead of event date for reporting.

Or you can 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: 
To isolate the events you would like to include in your query, use one of the following fields.
  1. To report on a specific program event, in the left column, expand Sales Order Item, then expand Sales Order Item Ticket. Highlight Program Event and drag Program Event Record into Include Records Where. Set this to be equal to your program event. Your field will appear like this: Sales Order Item\Sales Order Item Ticket\Program Events\Event is equal to Guided Tour
  2. OR If you would like to include all program events happening in a specific date range, follow the same steps in Step 1 and when you have Program Event highlighted in the left column, from the middle column drag Start date to Include records where. Set this field to be your date parameters. Your field will appear like this: Sales Order Item\Sales Order Item Ticket\Program Events\Start Date is equal to This week.

Add fields to Results Fields to Display: 
In this step, you will want to output the fields you need for your reporting purposes. 

  1. Here are some suggestions for event-related fields you may wish to include:
    1. Sales Order Item\Sales Order Item Ticket\Program\Name for the name of the program.
    2. Sales Order Item\Sales Order Item Ticket\Program\Category for program category.
    3. Sales Order Item\Sales Order Item Ticket\Program Events\Start Date for the date of the event.
    4. Sales Order Item\Sales Order Item Ticket\Program Events\Name for the name of the program event.
  2. To see information about the source of the transaction (online, daily sales, advance sales, group sales), highlight Sales Order at the top of the left hand column and drag Sales method type to Results Fields to Display.
  3. Next, we will add information about the revenue and quantity sold.
    1. To add the number of tickets sold, highlight Sales Order Item and drag Quantity to Results Fields to display.
    2. To add the dollar amount of those items sold, drag Net Amount to Results Fields to display. The Net Amount field will display how much was paid for the item (cost minus any discounts). 
  4. Next, we will add information about refunds that were made. 
    1. To add the number of tickets refunded, expand Sales Order Item and highlight Refunded item. Drag Quantity to Results Fields to display. 
    2. To add the dollar amount of those items refunded, drag Total to Results Fields to display. 
    3. In order for the refund fields to calculate properly, highlight each field in Results Fields to Display and click the Sigma sign and check the box to Sum.
      User-added image
  5. To add Price Type, highlight Sales Order Item Ticket beneath Sales Order Item. Drag Price Type Code to Results Fields to Display.
  6. Your final query will look similar to this one: 
    User-added image
  7. Tip: Use the pencil icon to edit the name of the field headers in your results. 
  8. 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?