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
Hi Darrell,
Have you tried running the two reports down to the performance level so you can at least isolate in which performance the discrepancy is occurring? (Assuming that you have more than one performance per concert, that is.) I'm thinking that if you can make your parameter universe smaller you can at least find the performance that it happened in and then investigate from there. Which may mean pulling each order until you find it... depending on how persistent your finance folks are, of course!
Whenever this happens to me, the answer always has to do with sub exchanges. Some days it seems I do nothing but chase down discrepancies, and there's usually a logical explanation for them. Frustrating, but educational...
Good luck!
We're fighting the same thing here. (And it frequently feels like we spend way too much time on this project). Unfortunately we can not "live with it" because we have to report back to our partners and balance with their finance departments, not just our own.
One tool we use is a spreadsheet crosstab that's (very loosely) based off of the Performance Sales Summary by PT Category and the Performance Payment Summary (new). I've uploaded the SQL query I run to generate the data file that I cut and paste into Excel and then create a crosstab that the Box Office uses to try and find the issues. Once the crosstab is built, we use the drill down features to figure out what orders need more investigation.
It's called OperaCrosstab.sql in my files area. If you have any questions about what this does or how to use it, feel free to ask and I'll do what I can to help. (There are NO comments in the code, sorry).
I keep meaning to open up an enchancement request - I think there is a fairly easy solution to this problem. (Warning, Geek alert!) If the transaction_no (and possibly sequence_no) was added to t_order_seat_hist (from my quick perusal of the code, it is available when rows are added to t_order_seat_hist) we would be able to link better between the money and the seats (which is what our partners keep asking us for).
-steve