We just started implementing Flex Headers, which for the most part have been a delight to all. However, we're getting results with the last perf element that aren't correct. Instead of getting the performance date that most recently precedes the get date, we're getting the very first performance date in a patrons ticket history, resulting in the last perf being as far back as 2003. I believe I need to adjust the SQL code for that element, however I'm not sure exactly what to tweak.
Any help would be appreciated. Thanks.
I am guessing that you would enter the following.
Data Select: !.last_perf_dt
Data From: LV_LAST_PERFORMANCE
Data Where: leave empty
Cheers
Sandra
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Adria Gunter Sent: Monday, November 23, 2015 12:29 PM To: Sandra Ashby <sashby@tessituranetwork.com> Subject: Re: [Tessitura Technical Forum] Issue with Flex Header Last Perf element
Thanks for this Brian.
Nick needs to know what goes in the data select column in the TR_QUERY_ELEMENT.
He created the view.
This is what I have so far.
Description: Last Performance
Category: Ticketing
Data Select:???
Data From: ???
Data Where:???
On Wed, Nov 18, 2015 at 6:28 PM, Brian Wilbur Grundstrom <bounce-brianwilburgrundstrom7163@tessituranetwork.com> wrote:
You can simply create a view, and put that in tr_query_element. Something like this view will give you the last performance date per person. This will not rely on ticket history being updated. create view LV_LAST_PERFORMANCE as select a.customer_no, MAX(c.perf_dt) as last_perf_dt from T_ORDER a join T_LINEITEM b on a.order_no = b.order_no join T_PERF c on b.perf_no = c.perf_no group by customer_no BRIAN WILBUR GRUNDSTROM | Database Administrator SHAKESPEARE THEATRE COMPANY Recipient of the 2012 Regional Theatre Tony Award® 516 Eighth Street, SE | Washington, DC 20003-2834 p 202.547.3230 ext. 2216 | c 917.952.7957 bwg@shakespearetheatre.org www.shakespearetheatre.org www.brianwilbur.com 2015|2016 SEASON •• Farber’s SALOMÉ • Porter’s KISS ME, KATE • Hatcher’s THE CRITIC & Stoppard’s THE REAL INSPECTOR HOUND • Shakespeare’s OTHELLO • Orwell’s 1984 • Shakespeare’s THE TAMING OF THE SHREW From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Beth Hawryluk Sent: Wednesday, November 18, 2015 5:59 PM To: Brian W. Grundstrom Subject: Re: [Tessitura Technical Forum] Issue with Flex Header Last Perf element Hi Adria, The reason for the updating nightly is because the local procedure pulls from Ticket History (as does the flex header element from this post) and not from live order data. If your organization is like ours (and I believe this is the standard), the procedure that updates ticket history runs once nightly anyway and so having the procedure that pulls this last performance info run more frequently wouldn't do much. If you're super duper desperate and your nightly ticket history procedure doesn't use up too many of your resources you could manually force it to run now followed by the procedure to pull that last performance info you've already set up using the output set cookbook. I've had to run my ticket history updates during the day on occasion and it hasn't caused us issue as long as there isn't much other action going on. As for a more long term solution... you could look at building an element that looks at live order data instead of ticket history although I'm not sure if that's the best way. I've never gone down that path myself. I frequently remind my coworkers how our ticket and package history updates so they aren't surprised by being a day behind. My first concern would be impact on application performance. I have no clue if it would have a big impact or not but it's something that you could try and test. The second concern I would have is making sure you're limiting your query to only look at seated/ticketed/paid orders so you don't get any returned tickets or anything else causing a show to pop up as their last performance if they didn't actually have tickets. Not quite the answer you were looking for but hopefully it's a little bit helpful... From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com> Sent: 11/18/2015 5:22:12 PM Beth, I'm kinda desperate. Do you know if there's a way to pull Last attended performance into an output set without having to create a procedure? I just copied the recipe from the Output set cookbook not realizing that a procedure needs to be run overnight. I need the info now. Do you know of a list of some kind? I see this flex header thing but I've never done a custom flex header before. Any help would be appreciated. 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! -- View this message online at http://www.tessituranetwork.com/Community/forums/p/12969/45399.aspx#45399 or reply to this message Adria Gunter | Subscriptions and Groups Manager STUDIO THEATRE 1501 14TH ST NW WASHINGTON DC 20005 T 202-232-7267, ext. (365) F 202-588-5262 www.studiotheatre.org Subscriptions are online! Reserve your group of 10+ today and enjoy a discount! 2015-2016 SEASON MAIN SERIES CHIMERICA |THE APPLE FAMILY CYCLE: SORRY & REGULAR SINGING | BETWEEN RIVERSIDE AND CRAZY | MOMENT | HEDDA GABLER 2015-2016 SEASON STUDIO X ANIMAL | CONSTELLATIONS 2015-2016 SPECIAL REMOUNT BAD JEWS 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!
You can simply create a view, and put that in tr_query_element.
Something like this view will give you the last performance date per person.
This will not rely on ticket history being updated.
create view LV_LAST_PERFORMANCE
as
select
a.customer_no,
MAX(c.perf_dt) as last_perf_dt
from T_ORDER a
join T_LINEITEM b on a.order_no = b.order_no
join T_PERF c on b.perf_no = c.perf_no
group by customer_no
BRIAN WILBUR GRUNDSTROM | Database Administrator SHAKESPEARE THEATRE COMPANY Recipient of the 2012 Regional Theatre Tony Award® 516 Eighth Street, SE | Washington, DC 20003-2834 p 202.547.3230 ext. 2216 | c 917.952.7957 bwg@shakespearetheatre.org www.shakespearetheatre.org www.brianwilbur.com 2015|2016 SEASON •• Farber’s SALOMÉ • Porter’s KISS ME, KATE • Hatcher’s THE CRITIC & Stoppard’s THE REAL INSPECTOR HOUND • Shakespeare’s OTHELLO • Orwell’s 1984 • Shakespeare’s THE TAMING OF THE SHREW
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Beth Hawryluk Sent: Wednesday, November 18, 2015 5:59 PM To: Brian W. Grundstrom Subject: Re: [Tessitura Technical Forum] Issue with Flex Header Last Perf element
Hi Adria,
The reason for the updating nightly is because the local procedure pulls from Ticket History (as does the flex header element from this post) and not from live order data. If your organization is like ours (and I believe this is the standard), the procedure that updates ticket history runs once nightly anyway and so having the procedure that pulls this last performance info run more frequently wouldn't do much.
If you're super duper desperate and your nightly ticket history procedure doesn't use up too many of your resources you could manually force it to run now followed by the procedure to pull that last performance info you've already set up using the output set cookbook. I've had to run my ticket history updates during the day on occasion and it hasn't caused us issue as long as there isn't much other action going on.
As for a more long term solution... you could look at building an element that looks at live order data instead of ticket history although I'm not sure if that's the best way. I've never gone down that path myself. I frequently remind my coworkers how our ticket and package history updates so they aren't surprised by being a day behind. My first concern would be impact on application performance. I have no clue if it would have a big impact or not but it's something that you could try and test. The second concern I would have is making sure you're limiting your query to only look at seated/ticketed/paid orders so you don't get any returned tickets or anything else causing a show to pop up as their last performance if they didn't actually have tickets.
Not quite the answer you were looking for but hopefully it's a little bit helpful...
From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com> Sent: 11/18/2015 5:22:12 PM
Beth,
I'm kinda desperate. Do you know if there's a way to pull Last attended performance into an output set without having to create a procedure? I just copied the recipe from the Output set cookbook not realizing that a procedure needs to be run overnight. I need the info now. Do you know of a list of some kind? I see this flex header thing but I've never done a custom flex header before.
Any help would be appreciated.
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!
-- View this message online at http://www.tessituranetwork.com/Community/forums/p/12969/45399.aspx#45399 or reply to this message
Adria Gunter | Subscriptions and Groups Manager
STUDIO THEATRE
1501 14TH ST NW
WASHINGTON DC 20005
T 202-232-7267, ext. (365)
F 202-588-5262
www.studiotheatre.org
Subscriptions are online! Reserve your group of 10+ today and enjoy a discount!
2015-2016 SEASON MAIN SERIES CHIMERICA |THE APPLE FAMILY CYCLE: SORRY & REGULAR SINGING | BETWEEN RIVERSIDE AND CRAZY | MOMENT | HEDDA GABLER
2015-2016 SEASON STUDIO X ANIMAL | CONSTELLATIONS
2015-2016 SPECIAL REMOUNT BAD JEWS