Hi Tess Network,
I crafted a report that collects all Comp Tickets sold for a performance run by aggregating records from the t_sub_lineitem table. We would like to include a "revenue" value to associate with these comp ticket totals, by summing up what the base price woudl have been if they were paid for. I understand this value appears in the "Season Overview report" that Tessitura generates.
Where would I find a "base price" value in the database described in a detailed enough way to associate that value with each comp ticket from the T_sub_lineitem table?
Hi Aaron,
I've recently done the same thing.
I have a temp table in the query that returns the price associated to the Standard price for the performance which I then later join to get the value for the Comp. This works for us as we don't do much dynamic pricing, so I don't have to work out prices at different times of sale.
You'd be able to get the prices from t_perf_price_layer, t_perf_price_type and FT_GET_PRICES.
If you'd like to see the full code, I'd be happy to email it to you.
Caryl
Hi Caryl,
Thanks so much for the advice. It could be because of our dynamic pricing I'm getting a lot of different results from those three and I would very much appreciate seeing the code you've used.
The procedure RP_PERF_SALES_DATA has a cte that provides base price based on zone per performance. That should give you everything you need to calculate your revenue lost to comps. This procedure is the base of the Performance Revenue Gain-Loss report. I have comps in their own Price Type Category so I can get the revenue loss to comps from this report out of the box.
Dorothy