• Replies 2 replies
  • Subscribers 701 subscribers
  • Views 128 views
  • Users 0 members are here
Options
Related

Trying to balance finance dashboard with box office statement with no luck - help?

Jordan Edwards
$organization

Hello hive mind!  Crossposted from the analytics and reporting forum. 

My Finance Director wants a break down of transaction totals by payment method for a particular production season with just ticket values (no fees). I created a dashboard that seems to look right, but I  can't for the life of me get it to match up with what I have on a box office statement or ticket sales by period report.  I've tried pulling info from the database, but I'm not having much luck there either (granted, not super experienced with that, but I'm happy to share the sql I've used to get iffy results if someone has an idea on how to help).

Any suggestions?  My numbers are off by about $7k.  My dash file is attached. 

Here are the criteria I used for the Ticket Sales By Period report (those numbers match my box office statement):

Here is my sql if anyone is interested in that as well (I couldn't get these numbers to match up either):

SELECT [impresario].[dbo].[T_ORDER].[order_no]
,[impresario].[dbo].[T_ORDER].[appeal_no]
,[impresario].[dbo].[T_ORDER].[source_no]
,[impresario].[dbo].[T_ORDER].[customer_no]
,[impresario].[dbo].[T_ORDER].[create_dt]
,[impresario].[dbo].[T_ORDER].[tot_ticket_purch_amt]
,[impresario].[dbo].[T_ORDER].[tot_ticket_return_amt]
,[impresario].[dbo].[T_ORDER].[tot_fee_amt]
,[impresario].[dbo].[T_ORDER].[tot_ticket_paid_amt]
,[impresario].[dbo].[T_ORDER].[tot_fee_paid_amt]
,[impresario].[dbo].[T_ORDER].[transaction_no]
,[impresario].[dbo].[T_PAYMENT].[customer_no]
,[impresario].[dbo].[T_PAYMENT].[pmt_dt]
,[impresario].[dbo].[T_PAYMENT].[pmt_amt]
,[impresario].[dbo].[T_PAYMENT].[pmt_method]
,[impresario].[dbo].[T_PAYMENT].[create_dt]
,[impresario].[dbo].[T_TRANSACTION].[create_dt]
,[impresario].[dbo].[T_TRANSACTION].[order_no]
,[impresario].[dbo].[T_TRANSACTION].[perf_no]
FROM [impresario].[dbo].[T_ORDER]
LEFT JOIN [impresario].[dbo].[T_PAYMENT]
ON [impresario].[dbo].[T_ORDER].[create_dt] = [impresario].[dbo].[T_PAYMENT].[create_dt]
LEFT JOIN [impresario].[dbo].[T_TRANSACTION]
ON [impresario].[dbo].[T_ORDER].[order_no] = [impresario].[dbo].[T_TRANSACTION].[order_no]
WHERE [perf_no] = 400 OR [perf_no] = 406 OR [perf_no] = 403

DKPaymentMethodBreakdown.dash..dash..dash

  • $organization

    ,

    I don't have an answer for you.  I do have a few suggestions about moving forward.

    • I grabbed your .dash file and took a quick look.  I noticed a lot of filters that you have put in place
      • At the dashboard level
        • The narrowing of the GLS
      • At the widget Level
        • The inclusion of some Production Season
        • The inclusion of some Campaign
        • The inclusion of some Production
        • A restriction to a narrow range of very recent performance dates
        • Exclusion of some payment methods
        • Exclusion of some payment method types
        • The inclusion of a very limited set of GLS
        • The inclusion of All Fees
      • At the  formula level
        • In Payment Method Type the filtering out of On Account
        • In Payment Method the filtering our of some methods
        • In Campaign the inclusion of just the 19-20 ticketing season
        • In Debit about > 1.08
        • In total credit amount > 1.08
        • Total Transaction amount > 25931

    In your SQL above I do not see the same set of filters in place.  And without some research, I'm not quite sure that your joins are exactly correct for what you are trying to do here.  So, I'm not surprised that there might be some discrepancies there.

    You may also find that the row selection in your dashboard that focuses on payment methods may be missing some other rows out of the finance cube that are not payment rows. 

    So what to do:

    The unit of analysis of the Finance e-cube is the transaction.  

    I'd like to invite you to consider a much more simple case before doing a lot of filtering.  I've done a bit of playing with this.

    • I start from scratch in Tessitura Analytics. 
    • I've taken a single performance, with only filters to get me to all of the transactions for a single performance.
    • If this matches the performance numbers from your other performance reports.  That's good. 
    • Then make sure that all of the performances match.
    • You can then filter by transaction type.  (Fees are a set of transaction types.) . Or transaction date ranges to get the subset of data you are looking for. 
      • (Note that: these subsets of data will not necessarily match the total for the entire performance.) 

    Here is a picture of the board I was playing with.

    Here is the .dash file for the above.  You will have to change this dashboard to the name of your local e-cube to get any results. Here is a bit of a discussion on that process if you have not done that before.

    AllFinancaltransactionsforaperfomance.dash

    If these do not then work.  You may need to put in a support ticket for this.

    was there a conversation over in the TAMATO group about this kind of reconciliation that you could point Jordan too?

    Finally, you did not say what version of tessitura you are using.  There have been a lot of changes between the early 15.0 version and recent 15.0 and 15.1 versions of Tessitura Analytics. Thank you,  and the rest of the development team.  I know that some of the challenges we were having with reconciliation with the finance cube were fixed only when we went to Tessitura V15.1.4.

    I hope this helps a bit.  Others please jump in.

    Happy New Year!  Welcome to the 2020s.  

    --Tom

  • $organization in reply to Tom Brown (Past Member)

    Thank you Tom!  That helped! We are currently on 15.0 but are doing our test upgrade to 15.1 on Monday!