• Replies 3 replies
  • Subscribers 336 subscribers
  • Views 166 views
  • Users 0 members are here
Related

Refund Reporting

Christopher Cuhel
$organization

Hello,

Has anyone figured out a good way to report on actual ticket refunds (not exchanges, not upgrades/downgrades...just straight up refunds)? With the myriad of ways a refund could be processed, I realize this is a difficult question to answer in Tessitura so I thought I'd see if anyone has figured out a way to crack this issue.

- Chris

  • $organization
    I don’t fully have an answer, but here are the concepts.
     
    In Analytics, you could use the Finance cube and look for negative credit card payment amounts to get a rough idea of the order of magnitude.
     
    Assuming you have access to SSMS, the tables should be in the list below.
     
    T_SUB_LINEITEM
    T_ORDER
    T_TRANSACTION
    T_PAYMENT
     
    Where T_SUB_LINEITEM.status IN (4)
    -- 4 = returned.
     
    1. The payment methods should match
    2. The positive payment amount = negative payment amount (for a full refund).
    3. The subline item status = returned
    4. This method would not find orders that were initially put on account and refunded before using the on account $ for tickets.
     
    The easiest way could be to restrict it to only credit card payment methods. I imagine that something like 90% of your payments are via credit cards. Leverage the fact that If the CC payment is a negative amount, then it is a refund.
     
    You also might want to utilize CTE’s to group/organize the data so you can then get a final select statement. For example, a CTE to gather the ticket refunds and a CTE to gather the orders that initially were put on account and refunded.
     
    Neil
     
  • $organization

    You could also leverage transaction type from the system table TR_TRANSACTION_TYPE.  33 = Refund. However, I don't think transaction type is available in the Finance cube. Might have to use SSMS.

  • $organization in reply to Neil Cole

    Yep Transaction Type (refund) is in the Finance Cube.  

    So I have a widget for this but it's for data analysis and not really presentational (this is me being embarrassed that it's not pretty or even coherent to others).  The curve ball that Finance threw me was that they wanted a split on the original Order purchase year but I think that I managed it. 

    There are some inactivated rows there that I flick on and off when I was sense checking.  I spent a large amount of time in bi.VT_TRANSACTION_AND_PAYMENT  comparing orders to see what I was getting right.  Still might be wrong but it's moving ahead.  Capturing refunding to On Account was a tricky one and the Transaction Amount Value is actually a Formula (sum([Transaction  Amount]) ,  [Years in Date] )  where Years in Date is actually Order Year where I got to include ONLY 2021 original orders instead of the ticket booked in 2020 for 2021.

    BTW the dashboard is for Credits and Refunds and Ticket Donations (oh my)

    Happy to talk about this further but it does require a bit of energy.