I need to report on discounts by program and by price type

You may need to report on what discounts were used for which price types. This solution will guide you through the steps to report on how many tickets were discounted on an order. 
Start your query: 
  1. Go to Analysis > Information Library 
  2. Click Add an ad-hoc query
  3. Select the Source View of Sales Order and click OK. 
Add Fields to Include Records Where: 
  1. If you'd like to see all discounts, regardless of what they were used for,  in the left column, highlight Sales Order Item and in the middle column, drag Type to Include Records Where. Set this to be equal to Discount. Your field will look similar to this one: Sales Order Item\Type is equal to Discount.
  2. If you'd like to see discounts only for a specific program or program category, in the left column, expand Sales Order Item, expand Sales Order Item Discount, expand Discounted Sales Order Item, expand Sales Order Item Ticket and highlight Program. From the middle column, drag your desired field to Include Records Where. For example, if you'd like to filter on a specific program, drag Program Record to Include records where. If you'd like to filter on a program category, drag Category to Include Records Where. Your field will look similar  to this one: Sales Order Item\Sales Order Item Discount\Discounted Sales Order Item\Sales Order Item Ticket\Program\Category is equal to Special Exhibit.
  3. If you'd like to filter this report based on date, in the left column, highlight Sales Order at the top and drag Transaction Date from the middle column to Include Records Where. Set this field to be is, between, or on of after to match the date range you would like in your report. Your field will look similar to this one: Transaction date is This week. 
  4. To eliminate any fully refunded orders, highlight Sales Orders at the top of the left column. Drag Refund Status Text from the middle column to Include records where. Set this to be not equal to Fully Refunded. Your field will appear like this: Refund status text is not equal to Fully Refunded.
Add Fields to Results Fields to Display:
  1. To output the name of the discount, in the left column, expand Sales Order Item and highlight Sales Order Item Discount. From the middle column, drag Discount Name to Results fields to display.
  2. In order to see the Number of Discounted Items, with the steps above, also drag Number of Discounted Items from the middle column to Results fields to display. 
  3. Next, to add information about what price type was used, in the left column, expand Sales Order Item, expand Sales Order Item Discount, expand Discounted Sales Order Item, and highlight Sales Order Item Ticket. From the middle column, drag Price Type Code to Results fields to display. The results fields to display will look like: Sales Order Item\Sales Order Item Discount\Discounted Sales Order Item\Sales Order Item Ticket\Price Type Code.
  4. If you'd like to see information about the program that was purchased, you can expand Sales Order Item Ticket (where it was highlighted from Step 3) and highlight Program or Program Events. From the middle column, drag any applicable fields to Results Fields to Display. For example: Sales Order Item\Sales Order Item Discount\Discounted Sales Order Item\Sales Order Item Ticket\Program\Program name
  5. If you'd also like to consider partial refunds in your count, you can output the number refunded. In the left column, expand Sales Order Item, expand Sales Order Item Discount, expand Discounted Sales Order Item, highlight Refunded Item. From the middle column, drag Quantity to Results Fields to Display. Highlight the field in Results Fields to Display and SUM the field for an accurate count. Your field will appear like this: SUM(Sales Order Item\Sales Order Item Discount\Discounted Sales Order Item\Refunded Item\Quantity). You will want to subtract the refunded number from the Number of Discounted Items for the count minus refunds. 
You can synthesize the data once it is in Excel by using a Pivot Table. For more information on creating PivotTables, you can visit Microsoft's Support Site here
Note: We provide links to third-party websites in an effort to help you resolve your issue. We are not responsible for the information on third-party websites and we cannot assist with implementing resolutions from these websites.
 

Environment

 Altru Arts & Cultural

Was this article helpful?