• Replies 6 replies
  • Subscribers 338 subscribers
  • Views 135 views
  • Users 0 members are here
Related

Accurately joining T_PAYMENT > T_TRANSACTION > T_SUB_LINEITEM

Former Member
Former Member $organization

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 

 

Parents
  • $organization

    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,

    Heather
    Seattle Rep 

Reply
  • $organization

    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,

    Heather
    Seattle Rep 

Children
No Data