For the past few years we have offered a 2-for-1 subscription deal. In our old ticketing system the value of the free ticket was recorded somehow and was posted to its own GL account.
When we moved to Tessitura two years ago this stopped happening. Mainly because no one outside of our finance dept seemed to know it was happening in the first place. :)
But I've been asked to make this happen again so we can report on the value of these tickets among other things.
I've racked my brain but can't come up with an ideal solution. We can't take simply double the amount of the paid tickets because if there tickets of different values purchased it is always the cheaper tickets that are free. We don't want to use a dummy payment method because (a) it would be a nightmare for our box office staff to process with huge potential for error due to tickets being different values and multiple subscription purchases in the same order and (b) it would count as revenue in our reporting which would skew the numbers drastically.
Are there any other organizations that have found a way to record the value of their free tickets? How do you do this? Is it even possible at all?
Thanks for any suggestions!
Price Type Reasons could be used to mark what price the comp ticket would have been if it were not a comp. Reasons are setup with the system tables TR_COMP_CODE and TX_PRICE_TYPE_COMP_CODE. You can require a Price Type Reason be selected for a specific Price Type by checking the Reason Ind box in TR_PRICE_TYPE.
Can you use some form of the "comp amt" calculation that Season Overview creates, and just point directly to your price type (assuming it's a specific price type for your 2-for-1 comps)? If so, you'd just need to do some custom SQL/Reporting, without changing your setup for future or past seasons. I was able to pull SQL straight from the Season Overview sproc that returned good data for me - YRMV.
------
declare @perf_no int = '913'
declare @price_type int = '56'
select a.performance_code,
a.mat_eve_flag,
a.performance_date,
comp_desc = case when r.price_type_category in (4) then isnull(c.description,r.description) else 'Press Seats' end,
comp_amt = sum(t.price),
comp_cnt = count(distinct x.sli_no),
a.perf_no
from [dbo].vs_imp_perf_data a
JOIN vs_perf p ON p.perf_no = a.perf_no and p.perf_no=@perf_no
JOIN [dbo].tx_perf_seat x WITH (NOLOCK) ON p.perf_no = x.perf_no
JOIN [dbo].t_sub_lineitem l WITH (NOLOCK) ON x.sli_no = l.sli_no
LEFT OUTER JOIN [dbo].tr_comp_code c WITH (NOLOCK) ON l.comp_code = c.id
JOIN [dbo].tx_perf_pmap f WITH (NOLOCK) ON p.perf_no = f.perf_no and f.price_category = 1 and f.base_ind = 'Y'
JOIN [dbo].t_subprice t WITH (NOLOCK) ON x.zone_no = t.zone_no and x.zmap_no = t.zmap_no and f.pmap_no = t.pmap_no
JOIN [dbo].tr_price_type r WITH (NOLOCK) ON l.price_type = r.id
where r.id=@price_type and
x.seat_status IN (8,13)
and not exists (select * from [dbo].t_benevolent_seat where perf_no = p.perf_no and seat_no = l.seat_no)
group by a.performance_code, a.mat_eve_flag, a.performance_date,
case when r.price_type_category in (4) then isnull(c.description,r.description) else 'Press Seats' end,
----------
Beth GillilandUMS
Thanks Boann and Beth! Great suggestions. :)
I definitely did not think of either of those options so I will be investigating to see if either of those will be suitable for us.