- Go to Analysis > Information Library
- Click Add a new ad-hoc query
- Select the source view of Sales Orders. Note: A sales order source view is required because the price type information is related to ticketing and cannot be accessed in a Registrants source view.
Add fields to Include Records Where:
- First, we need to add a field to filter on the event that we need this list for. In the left column, expand Sales Order Item, then expand Sales Order Item Ticket. Highlight Program Events and in the middle column, drag Event Record to Include records where. Set the filter to be equal to and search for your event. Your field will appear like this:
Sales Order Item\Sales Order Item Ticket\Program Events\Event record is equal to Art Class.
- (Optional) If you would like to eliminate Cancelled Registrants (Note: Registrants that have been refunded also display as Cancelled in the roster), you can add a field for that as well. In the left hand column under where you expanded Sales Order Item Ticket in the previous step, expand Sales Order Item Ticket Registrant, then highlight Registrant. Drag Is cancelled to Include records where and set the filter to be equal to No. Your field will appear like this:
Sales Order Item\Sales Order Item Ticket\Sales Order Item Ticket Registrant\Registrant\Is cancelled is equal to No.
Add fields to Results Fields to Display:
- First, we will add Registrant Name to Results Fields to Display so we can display the registrant name in our final results. With Registrants still highlighted in the left column, drag Name from the middle column into Results Fields to Display. We also recommend selecting this field and clicking on the 'Pencil' icon just above so you can edit the column header to display a succinct title such as "Registrant". Your field will appear like this:
Sales Order Item\Sales Order Item Ticket\Sales Order Item Ticket Registrant\Registrant\Name(Registrant)
- Next we will add the price type. From the left column, highlight Sales Order Item Ticket where we expanded it in Step 4. From the middle column, drag Price Type Code into Include Records where. YYou can also edit this field with the 'Pencil' icon so the column header is much shorter and intuitive such as "Ticket". Your field will appear like this:
Sales Order Item\Sales Order Item Ticket\Price type code(Ticket
- You can also add additional fields to Results Fields to display depending on your needs for this list. A few recommendations:
- This query will give you one line per registrant, therefore we do not recommend also using this query for revenue. The correct revenue field would be Sales Order Item\Net amount, which would duplicate for each registrant. If you need to report on revenue, we recommend using the query in this related solution: How to report on scheduled program event revenue by price type
NOTE: If you have more than one registration information question, we also do not recommend outputting the answers to those questions in this query as this will also create multiple lines per registrant. If you need this information along with price type, we recommend using option #2 of this resource here to build an export process.
Here is an example of your final query: