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. Navigate to Analysis, then click on Information Library
  2. Click Add an ad-hoc query
  3. Select the source view of Sales Order, then click OK. 
Add Fields to Include Records Where: 
  1. To see all discounts: In the left column, select Sales Order Item. From the middle column, drag Type into Include Records Where. Set this to be equal to Discount.
  2. To see discounts for a specific program: In the left column, expand Sales Order Item, expand Sales Order Item Discount, expand Discounted Sales Order Item, expand Sales Order Item Ticket then select Program. From the middle column, drag Program Records into Include records where. Set this equal to your program.
  3. To filter this report based on date: In the left column, select Sales Order. From the middle column, drag Transaction Date into Include Records Where. Set this equal to the date range you would like in your report.
  4. To eliminate any fully refunded orders: In the left column, select Sales Orders. From the middle column, drag Refund Status Text into Include records where. In the Apply criteria screen change this to Not Equal to and manually type Fully Refunded. Click OK.
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. To see the Number of Discounted Items: In the left column, expand Sales Order Item and highlight Sales Order Item Discount. From the middle column, drag Number of Discounted Items into Results fields to display. 
  3. 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 select Sales Order Item Ticket. From the middle column, drag Price Type Code to Results fields to display.
  4. To see information about the program that was purchased: In the left column, expand Sales Order Item, expand Sales Order Item Discount, expand Discounted Sales Order Item, expand Sales Order Item Ticket, then select Program. From the middle column, drag Name into Results fields to display.
  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.
Here is an example of what your final query will look like: 
User-added image

Note: 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. 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?