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.
Start your query:
  1. Navigate to Analysis, then click on Information Library
  2. Click Add an ad-hoc query
  3. Select a source view of Sales Orders and click OK

Add fields to Include records where:
  1. To filter on a specific date range: In the left column, select Sales Orders. From the middle column, drag Transaction date into Include Records Where. Select Between in the drop-down and enter your date range
  2. To exclude unearned revenue from Group Sales: In the left column, select Sales Orders. From the middle column, drag Sales method type into Include Records Where. Set Not Equal to Group Sales
  3. To exclude refunds: In the left column, select Sales Orders. From the middle column, drag Refund Status Text into Include Records Where. In the drop-down select Equal To and then type in Not Refunded
  4. In the left column, expand Sales Order Item, expand Sales Order Item Ticket, then select Program Event. From the middle column, drag Start date into Include records where. Set to Between and enter the same date range you entered above
  5. In the left column, expand Sales Order Item, expand Sales Order Item Event Registration, expand Registrant, then select Event. From the middle column, drag Event start date into Include records where. Set to Between and enter the same date range you entered above
  6. Within 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 as well as the End right parenthesis button )+
  7. Within Include records where, click on the line of criteria that says Sales Order Item\Sales Order Item Ticket\Program Events\Start date. Then, click on the Begin left parentheses button (+
  8. In the left column, select Sales Order Payment. From the middle, drag Amount into Include records where. Set Not Equal to 0. In the Criteria screen, un-mark the option to Include blanks

Add fields to Results fields to display:
  1. In the left column, select Sales Orders. From the middle column, drag Transaction date to Results Fields to Display
  2. In the left column, expand Sales Order Item, expand Sales Order Item Ticket, then select Program Event. From the middle column, drag Start date to Results fields to display
  3. In the left column, expand Sales Order Item, expand Sales Order Item Event Registration, expand Registrant, then select Event. From the middle column, drag Event start date to Results fields to display
  4. In the left column, select Sales Order payment. From the middle, drag Amount into Results fields to display

Save your query:
  1. Click the Set Save Options tab
  2. Name your query
  3. Save and Close


 

Environment

 3.15

Was this article helpful?