How to create a query to report on revenue by event date

Follow the steps below to create an Ad-hoc query that reports on the amount of revenue earned for specific events.
Here are the steps to build a query to group revenue by event date:
  1. Go to the Analysis page and select Information library
  2. From here select Information library and Add an ad-hoc query
  3. Choose Sales Orders for the Record type and select OK
  4. Filter on the field Sales Orders/Refund status text being Not equal to Fully Refunded
  5. Leave the checkbox marked to Include blanks when saving this filter
  6. In the left hand column expand Sales order item, expand Sales order item ticket, and highlight the Program field
  7. Filter on the Sales Orders/Sales order item/Sales order item ticket/Program/Program record being equal to your specific program
  8. To filter on events in a specific date range filter on the Sales order\Sales order item/Sales order item ticket/Program events/Start date being equal to or between your specific dates
  9. Add the Name, Start date, and Start time fields from the Sales order\Sales order item/Sales order item ticket/Program events field into the Results fields to display section of the query to add them as additional output fields
  10. Under the results fields to display section highlight the Lookup ID field and select the option to delete it
  11. Add the additional field Sales Orders/Sales order item/Net amount field as an output field
  12. Highlight this field, select the icon for the Sum function, and choose the Sum option
  13. To add a column for Sales orders which are partially refunded add the field Sales Orders\Sales order item\Refunded item\Total as an output field
  14. Select the Preview results tab and confirm the correct results are showing



Was this article helpful?