Dashboard based on Perf Date instead of Order Date

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.

Thanks Summer Hirtzel (Past Member), 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. 

I'd be interested, too Summer - thanks!!! jreynolds@clevelandart.org

I'm assuming you're referring specifically to the At-A-Glance Tickets widget? If you never want to use that widget based on order date, you (or you System Admin) can change it directly in System Tables, to make the At-A-Glance Tickets mimic what the At-A-Glance Attendance does. 

In TR_DASH_WIDGET_PARM_VALUE there is a row for WidgetParameterDefinitionID named Date Hierarchy Name. If you change the DisplayValue and RawValue for your At-a-Glance Tickets widget to match the At-a-Glance Attendance, the Tickets version of the widget will use the Perf Dt instead. This will change the widget for everyone at your organization though, and will affect all existing dashboards, so if you're considering doing this, make sure what you want is truly "always instead".

That said, we don't generally recommend modifying the standard values in this table. You're welcome to make the change above, but if you're considering any others beyond that, it's best to contact the T-Stats and Dashboards support team before doing so.

Thanks!  We actually have all the widgets installed twice...could I hypothetically modify one and rename it as suggested?  Also, to confirm, this change will simply look instead for the Perf Date instead of Order Date, and will still look for tickets sold (not tickets scanned like the attendance widget)...correct?

I tried it out and it is working brilliantly!  I now have a At-A-Glace Tickets widget (that works as normal) and a At-A-Glance Tickets (By Perf) widget (that pulls tickets sold based on Perf Date).  Thanks a bunch, this is exactly what I was looking for!!

Derrek, 

Is your organization in a consortium or do you otherwise have multiple divisions? If you do, then it would be expected that there would be more than one copy of each widget (one for each division). If you have unintentional duplicates, that was probably a mistake made during install, and we'd normally recommend your IT team reach out to support for guidance cleaning that up. That said, if you do have unintentional duplicates and wanted to repurpose one with the edits above, so you could do it both ways...yes, that should work. Correct, that changing the Date Hierarchy Name alone will not affect anything else about the widget: it would still reflect sales not scans.