• 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

    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 Purkiss
    Sent: Tuesday, June 26, 2012 12:51 PM
    To: Ryan Creps
    Subject: [Tessitura Technical Forum] Accurately joining T_PAYMENT > T_TRANSACTION > T_SUB_LINEITEM

     

    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 

     




    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!

Reply
  • $organization

    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 Purkiss
    Sent: Tuesday, June 26, 2012 12:51 PM
    To: Ryan Creps
    Subject: [Tessitura Technical Forum] Accurately joining T_PAYMENT > T_TRANSACTION > T_SUB_LINEITEM

     

    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 

     




    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!

Children
No Data