SQL guru’s….help needed!!!
I am trying to create a report for our finance department that reports on Payment Method (specifically Amex, Visa and MC) by performance, contribution (by fund or campaign depending) and fee.
I have tried (and tried) and failed to come up with an accurate way of joining T_TRANSACTION (which seems to act as the bridge between T_PAYMENT and other tables like T_SUB_LINEITEM, T_CONTRIBUTION etc.) onto anything that means we can report on pmt_method via perf etc.
I saw a long forum post dated 2009 that spoke about issues joining these tables and wondered if there had been any solutions in the last couple of years? It seemed at that point, that T_TRANSACTION was a bridge view ONLY, and these 2 sets of tables were unable to be joined. Is this still the case?
Thanks for any advice,
Natasha
To be short, this is not possible to link T_TRANSACTION to T_SUB_LINEITEM without making some fairly limiting and quite possibly ill-advised setup assumptions about the values for T_TRANSACTION.perf_no and T_TRANSACTION.pmap_no. There is no direct link to T_SUB_LINEITEM from T_TRANSACTION apart from ensuring that the total transactional amounts for the order balance with the contents of the order.
For a good example of what is possible in regards to transactional reporting using performance sales data, take a look at the underlying procedure to Performance Transaction Detail, RP_PERFORMANCE_TRANSACTION_DETAIL.
+Ryan Creps
+Tessitura Network
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Natasha PurkissSent: Tuesday, June 26, 2012 12:51 PMTo: Ryan CrepsSubject: [Tessitura Technical Forum] Accurately joining T_PAYMENT > T_TRANSACTION > T_SUB_LINEITEM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Natasha,
You should be able to join t_payment to t_transaction using sequence_no. The only tricky thing is you have to treat certain transaction types differently and exclude certain ones.For example you probably don't want to include transaction types like "Future to Current" and "Non-Restricted to Restricted"
Also, for Fee Change and Ticket Change transaction types, you will need to use trn_amt instead of pmt_amt since there is no payment involved with these transaction types.
For gifts you would probably want to do a different query with different joins but still joining t_payment to t_transaction using sequence_no.
Dale
Hi Natasha -
First, have you looked at the Payments by Performance standard report? This might give you a place to start. You are correct that there is no direct link between order/contribution tables and that you do need to go through the t_transaction table to find the relationship between the two.
The larger issue when writing such a report (and most finance departments ask for - so you aren't alone!) is that in Tessitura you can have multiple payments on a single contribution or order. And you can have multiple things in an order or contribution (pledge payments, performances, fees, etc). This is where things get tricky. What happens if there's a split payment between two customers for a single order (like two subscribers who pay each pay for their sub with a different method). Someone who uses a gift certificate and then a Visa to cover the rest of the amount that the gift certificate doesn't cover? A donation with a pledge payment who changed cards because their old card was stolen? This is where the t_transaction table really comes into play because it breaks down those parts of an order or contribution and links them directly to a payment method.
Pittsburgh Cultural Trust has shared a report (#172) that is a modification on the Payments by Performance report that you might take a look at as well if for no other reason than to look at the SQL behind the report.
It's not the magic bullet of an answer you were looking for, and I'm sure other people will pipe in here, but I hope it explains some of the "why" and issues with connecting orders/contributions and payments.
HTH,
HeatherSeattle Rep
Hi,
In response to Dale's comment, due to something I found out today...
I had a query on a show settlement report where the value of the seats purchased didn't equal the payments that were being applied to the show. After lots of head scratching I found it was down to the join between t_transaction and t_payment (using transaction_no and sequence_no). Basically what had happened was that a customer had returned their seats for the performance, and then bought another show. So while there were transaction in and out of the perf, there was still only 1 payment, and if you follow the join it would show on the first show...
Over the next few days I'm giong to be investigating how we can get more accurate report, and will post back with what I find...
That's exactly why you would want to exclude ticket change and fee change transactions from your primary query but include them in a secondary query pulling trn_amt from t_transaction rather than pmt_amt from t_payment.
dale
What a great response, thanks everyone for all your replies! It is somewhat comforting to know that this was not just something really simple I was missing.
Thanks Heather and Ryan for bringing up the Payments by Performance standard report, a quick glance over this and already I can see it’s going to be a great help. Now that I realize what finance is asking for specifically is not possible, I can work on giving them the info they need in a different format. I couldn’t see one, but there isn’t a report like this for contributions is there?
Dale thanks for you advice on totaling contributions too, I’m from a Box Office background so still not 100% confident with that area.
I look forward to your report Simon....