• Replies 7 replies
  • Subscribers 961 subscribers
  • Views 208 views
  • Users 0 members are here
Related

Dashboard based on Perf Date instead of Order Date

Derrek Bentley (Past Member)
$organization

Hi everyone,

I am specifically trying to report on one season that has a performance every day as we are a museum.  The tickets get tracked and sold after the actual performance in this case and therefore, the available dashboards through T-Stats only seem to allow me to show based on Order date which means that the results are totally skewed for our executives.  Does anyone have a custom SSRS report or a creative way of showing sales based on Perf date instead of order date?  (I thought of using the new attendance widgets, however, none of these tickets get scanned so the widget shows 0).  Thanks for any ideas you might have!

Parents
  • $organization

    Have you played around with creating custom queries? www.tessituranetwork.com/.../Tessitura_Dashboards.pdf

    I don't have the patience to work with Tessitura's dashboards, so my creative solution is building out a homebrew SQL query for all my Ticket-Dashboard-Needs (you have to have access to SSMS, or have an IT/DBA person on staff do this for you), then loading the results of that query into Microsoft PowerBI (which is a dashboard program, kind of like Tableau -- PowerBI has a year-long free trial you can use, which should get you through to Tessitura v.15, when Analytics will ~supposedly~ change all our lives). I'm happy to share my full SQL query with anyone who's interested (the whole thing is a little large to share here). But this snippet might be enough to get you started:


    select sub.perf_code as PERF_CODE, main.create_dt as ORDER_DATE, sub.perf_dt as PERFORMANCE_DATE,
    sum(num_seats_pur - num_seats_ret) as tix, sum(tot_pur_amt + tot_ret_amt) as revenue

    from T_LINEITEM main
    left join
    (select a.perf_dt, a.season, a.prod_season_no, b.prod_no, a.perf_no, a.perf_code from t_prod_season b
    join
    t_perf a
    on a.prod_season_no=b.prod_season_no
    )sub
    on main.perf_no=sub.perf_no

    group by sub.perf_code, main.create_dt, sub.perf_dt

    The output of this select gives me 5 columns: Performance Code, Order Date, Performance Date, Performance Code, Number of Tickets Sold, and Total Revenue. So I can see, by performance code & date, how many tickets were sold and how much revenue came in.

  • $organization in reply to Summer Hirtzel (Past Member)

    Thanks , really appreciate it!  That was essentially my next step, I just don't have the knowledge to build from scratch what you just shared....and we don't currently have an available resource to build these.  Definitely excited for v15 and analytics, but in the mean time, would you mind emailing me the full query? I'd really like to just play around with it and see what I can come up with as a stop-gap for now!  derrek.bentley@humanrights.ca is my email.  Thanks for your quick response. 

Reply Children
No Data