• Replies 5 replies
  • Subscribers 337 subscribers
  • Views 74 views
  • Users 0 members are here
Related

Calculating Package Sales

Levi Sauerbrei
$organization

Oh mighty DBAs hear my plea!

This can't be as complicated as it seems. 

If I want to determine the sales of a particular performance as part of a particular package, I write something like this:

select a.perf_no,sum(a.due_amt),sum(a.paid_amt)
from t_sub_lineitem a (nolock)
join t_perf b (nolock)
    on a.perf_no = b.perf_no
where a.pkg_no = ###
group by a.perf_no

Since any returns or exchanges generate an additional record in t_sub_lineitem with a negative cash amount, I'm assuming the above query is telling me the total gross sales less all exchanges and returns.

What pieces am I missing? The numbers I get are close to, but not equal to, the numbers on several box office reports.  None of the box office reports agree with each other.

Now, I know the box office reports have specifically designed purposes but the folks trying to reconcile with finance are quite perturbed.  So, wouldn't the above query be the number that the box office should be handing to finance?

Any thoughts, strategies, additional head-banging-desk comments are appreciated.

Parents
  • $organization

    Hi Levi,

     

    Exchange sub line items do not have a package number on them (technically speaking they are single ticket sub line items, both the return and the sale) so they won’t be included in your query.  Because sub line item status is not a part of your query you should be getting all the original package performance sub line items, before exchanges.  The standard package sales reports filter out package performance sub line items that have a void status, which is why the numbers go down when exchanges start.  Your query does not exclude those voided (i.e. returned) sub line items, so it is not accounting for exchanges or full package refunds (which are probably rare but should be considered).

     

    The Performance Sales Summary by PT Category report might give you what you are looking for.  It groups ticket sales into single tickets and subscriptions based on the price type category of your price types.  As long as all the price types used for exchanges have a price type category of Subscription the report will accurately divide performance revenue between single tickets and subscriptions.  This is the ticket sales report we suggest to match against finance reports.  But this report isn’t going to tell you which packages led to the sales.  Because exchange sub line items do not have a package number I don’t think you really can sort package performance seats by the package they came from.  Probably the best you could do is sort unexchanged tickets by package and then have a bucket for exchanged package tickets.

     

    One other thing that could cause a discrepancy between GLs and ticket sales report numbers.  When there is an exchange downgrade (more expensive ticket to less expensive ticket) what do you do with the left over money?  If you use a fee or a payment method to take care of that money, it will no longer be counted by sales reports.  But if you use the same GL account for the fee or payment method as the GL map for the performance price maps, the GL reports will still count the money.  You would have to run a second report on the fee or payment method you use for downgrades and add that amount to your sales report to match the GL report.

     

    Kevin Sheehan

    Documentation & Learning Resources Specialist

    Tessitura Network

    1 888 643 5778 ext 329 Office

    ksheehan@tessituranetwork.com

     

Reply
  • $organization

    Hi Levi,

     

    Exchange sub line items do not have a package number on them (technically speaking they are single ticket sub line items, both the return and the sale) so they won’t be included in your query.  Because sub line item status is not a part of your query you should be getting all the original package performance sub line items, before exchanges.  The standard package sales reports filter out package performance sub line items that have a void status, which is why the numbers go down when exchanges start.  Your query does not exclude those voided (i.e. returned) sub line items, so it is not accounting for exchanges or full package refunds (which are probably rare but should be considered).

     

    The Performance Sales Summary by PT Category report might give you what you are looking for.  It groups ticket sales into single tickets and subscriptions based on the price type category of your price types.  As long as all the price types used for exchanges have a price type category of Subscription the report will accurately divide performance revenue between single tickets and subscriptions.  This is the ticket sales report we suggest to match against finance reports.  But this report isn’t going to tell you which packages led to the sales.  Because exchange sub line items do not have a package number I don’t think you really can sort package performance seats by the package they came from.  Probably the best you could do is sort unexchanged tickets by package and then have a bucket for exchanged package tickets.

     

    One other thing that could cause a discrepancy between GLs and ticket sales report numbers.  When there is an exchange downgrade (more expensive ticket to less expensive ticket) what do you do with the left over money?  If you use a fee or a payment method to take care of that money, it will no longer be counted by sales reports.  But if you use the same GL account for the fee or payment method as the GL map for the performance price maps, the GL reports will still count the money.  You would have to run a second report on the fee or payment method you use for downgrades and add that amount to your sales report to match the GL report.

     

    Kevin Sheehan

    Documentation & Learning Resources Specialist

    Tessitura Network

    1 888 643 5778 ext 329 Office

    ksheehan@tessituranetwork.com

     

Children