How to report on unearned revenue

Some customers consider Online Sales and Advance Sales that happen in a month before the event takes place to be unearned or deferred revenue.  Currently, only Group Sales unearned revenue is tracked in the Total Revenue and Payments Report.  The goal of this solution is to provide a query for tracking other types of unearned or deferred revenue.
  1. Go to Analysis > Information Library.  Click Add an ad-hoc query.
  2. Select a source view of Sales Orders and click OK.
  3. In the middle, select transaction date and move this to Include Records Where.
  4. Set the criteria to equal to this month (or whatever date range you are trying to reconcile).
  5. Move Transaction date to Results Fields to Display.
  6. In the middle, select sales method type.  Move this to Include Records Where.  Set the criteria to Not equal to Group Sales.
  7. In the middle, select Refund Status Text.  Move this to Include Records Where.  Set the criteria to Equal to Not Refunded.
  8. In the left, click on the plus sign next to Sales Order Item, then click on the plus sign next to Sales Order Item Ticket.  Then click on Program Event in the left.
  9. In the middle, select Start date and move this to Include Records Where.  
  10. Set the criteria to Greater than this month (or whatever date range you chose for transaction date).
  11. Move Program event start date to Results Fields to Display.
  12. In the left, click on the plus sign next to Sales Order Item, then click on the plus sign next to Sales Order Item Event Registration.  Then, click on the plus sign next to Registrant. Then click on Event in the left.
  13. In the middle, select event start date.  Set the criteria to greater than this month (or whatever date range you chose for transaction date).
  14. Move Event Start Date to Results Fields to Display.
  15. In Include Records Where, click on the line of criteria just added that says Sales Order Item\Sales order Item Event Registration\Registrant\Event\Event start date.  Then, click on the OR button at the top of the query above the Include Records Where box.
  16. While clicking on this line of criteria, click on the add right parentheses button )+.
  17. Click on the line of criteria that says Sales Order Item\Sales Order Item Ticket\Program Events\start date.  Then, click on the add left parentheses button (+.
  18. On the left, click on Sales Order Payment.
  19. In the middle, click on Amount and move this to Include Records Where.  Set the criteria to not equal to 0.  Unmark the box to include blanks.
  20. Move Sales Order payment\Amount to Results Fields to Display.
  21. Go to the Set Save Options tab.  Name and save the query.
  22. Click to process the results.
Here is a screenshot of how the query should look when completed: 
Unearned Revenue Query



Environment

 3.15

Was this article helpful?