Hello all,
Firstly, its a pleasure to be here on the new website.
And . . . we have been using Tess for, coming up to a year and a half and we labour under the disadvantage of not having a database manager, or anyone who can write sql reports for us. We use two out-of-the-box reports to reconcile concerts and for the first of our 2009 Master series there is a small discrepancy between money received (Payments by Performance (New)) and the total value of ticket sales (Performance Sales Summary by PT Category). Short of searching through every sub-line item in every ticketing order in the performance can any suggest another approach??
Cheers, Darrell
Forgot to add that I regularly use the Single Sale Order Listing report to identify and paid/unseated orders - and then for the concert, for good measure I have done an order search with every combination of paid/unpaid seated/unseated that I can. The discrepancy is $64 which relates to a particular price type. I have also tried to pull a list for this particular price type to at least identify the possible constituent orders but didn't find anything very useful. Darrell
Have you run the Performance Sales Summary by PT category both including donated tickets and not including donated tickets to see if that makes a difference?
Thanks for the response Phil but we don't use the donated seats option so that doesn't impact on the report.
I have a feeling I might just have to go out and do crash course in SQL. Cheers Darrell
1, there are no reports can figure out what is the difference in Tessitura.
2, and a crash course cannot help you getting the result. (if it is so simple, the report will be already there.)
3, the difference actually is the sli_status (1,2,6). you can find them in T_sli_detail table.
the sales report only count sli_status (3,12).
payment report is checking t_transaction table.
comapre t_sli_detail table with t_transactiontable.
the difference is
money in sli_status 1,2,6 will not be counted in sales report
but it will be counted in t_transaction table.
my suggestion is living with it.
to create our daily sales reports took me two months to finish it.
it is very complicated.
there are two parts in the Tessitura,
1, T_order---> t_sub_lineitem-->t_sli_detail(tr_price_type,t_pmap)-->t_transaction
2, T_transaction--->T_GL_POSTING_HISTORY(payments) or t_payment
you cannot link them together!!!
you can compare them at certain point of time(we take a copy of sales records at 2:00 am)
t_transaction table is in the middle.
it is a bridge, and bridge only.
first part of talbes will give you a sales report.
second part of tables will give you a GL report.
they never agree each other!!!
Never.
Why?
1,time difference.
Only posted batches GL will appear on a GL report.
2, sli_status difference.
sli_status (1,2,6) you never find them in a sales report.
so what is missing?
this is very tricky question.
there are no transaction reports in the system.
the answer is a transaction report.
Our requirement for the enhancement should be a transaction report.
this report will compare t_transaction with t_sli_detail.
also will comapre t_transaction with GL records(T_GL_POSTING_HISTORY or t_payment).
In this way, everything is checked.
this is what we have as a daily sales report.
my suggestion still is living with it.
Thanks Ben, your response was very helpful.
Sorry if I seemed to be suggested that learning SQL was a cure-all or anything less than complex. I appreciate the explanation of which tables the reports read - had no idea. I'm perfectly happy to live with this but our finance department is less so and expect us in the box office to come up with some answers.
Cheers Darrell