Hello all,
I'm wondering if anyone knows of a way to run a report/extraction or anything really that will find all constituents that HAD tickets to an event. We need something that would theoretically catch anyone who exchanged out of, or received a refund due to a cancelation, as the only methods I can currently think of only display current patrons for an event.
Thanks,
Maurice
Thank you, Lucie.
This worked perfectly and pulled all of the constituents with returned orders for the canceled event. Just an FYI to others: you only will need the first half of the query if there are no orders left in the event (i.e. all orders have been returned/refunded).
Chuck Buchanan
Manager, Direct Marketing and List Management
92nd Street Y
1395 Lexington Ave | New York, NY 10128
(212) 415-5451 | http://www.92Y.org
From: Tessitura Ticketing Forum [mailto:forums-ticketing@tessituranetwork.com] On Behalf Of Lucie SpielerSent: Wednesday, January 11, 2012 2:42 PMTo: Chuck BuchananSubject: RE: [Tessitura Ticketing Forum] Report for Constituents when a show is canceled
Here’s the whole query as I sent it (below).
The first 6 lines will give you any account that has a sub lineitem in an order for that performance, whether seated, ticketed, returned, or whatever.
The next line, “AND ISNULL(f.ticket_no,))<>0”, if you include it, will find people who are, or who have ever been, ticketed for the performance.
However, if you leave off everything after “AND ISNULL(f.ticket_no,))<>0”, you should get everyone who was ticketed for the performance in question. All the second half of the query does is to suppress people who still have ticket history for the performance in question.
You will need to replace 851 in the query with the number of the performance you are looking for. You can get that number by going to the specific performance in ticketing setup. (And, if you include the “AND a.customer_no not in” and following, you’ll need to replace the performance name with the perf code for the performance in question.)
/**START OF QUERY**/
Select distinct a.customer_no
From t_customer a (NOLOCK)
JOIN t_order e (NOLOCK) ON a.customer_no = e.customer_no
JOIN t_sub_lineitem f (NOLOCK) on e.order_no = f.order_no
Where IsNull(a.inactive, 1) = 1
AND f.perf_no = 851 --REPLACE
AND ISNULL(f.ticket_no,0)<>0
AND a.customer_no not in
(Select Distinct c.customer_no
From t_customer c (NOLOCK)
JOIN vs_tck_hist g (NOLOCK) ON c.customer_no = g.customer_no
Where IsNull(c.inactive, 1) = 1
AND g.performance_name = '120121LR' )
/**END OF QUERY**/
If nothing comes up when you run the query above, then ask an IT friend to run the following query in Microsoft SQL Server Management Studio (replacing 851 with the proper performance number):
select * from T_SUB_LINEITEM where perf_no = 851
If that query comes up blank, then you have no order history whatsoever, which shouldn’t be the case.
Lucie
______________________________
Lucie SpielerIT Development and Training Manager
Editor, Season ProgramFLORIDA GRAND opera8390 NW 25th StreetMiami, FL 33122305.854.1643 x. 1521Box Office: 800.741.1010To Give a Gift: 305.403.3315www.FGO.org2011-2012 • 71ST SEASON • LOVE FORBIDDEN, NOT FORGOTTENLuisa Fernanda • La rondine • Rigoletto • Roméo et Juliette
From: Tessitura Ticketing Forum [mailto:forums-ticketing@tessituranetwork.com] On Behalf Of Charles BuchananSent: Wednesday, January 11, 2012 12:59 PMTo: Lucie SpielerSubject: RE: [Tessitura Ticketing Forum] Report for Constituents when a show is canceled
The performance seating book will only work if the customers tickets have not already been returned and refunded. I am trying to pull all constituents in an extraction for an event that was canceled and all of the tickets were returned/refunded so the perf. seating book comes up blank. Does anyone know the query code I can enter in List Manager as a "manual edit" to pull this information?
Thanks!
From: Ryan Creps <bounce-ryancreps9649@tessituranetwork.com>Sent: 1/4/2012 6:52:13 PM
Maurice,
An alternative would be to run the Performance Seating Book report for the performance. You can then save the report results to a list using the option “Save as List” under the file menu.
The performance code criteria is based on ticket history, which is typically set up to update nightly. Ticket history may not be updating on that schedule or may have an issue. One way to check is by loading a constituent that you know attended the event and checking the ticket history panel under their record.
+Ryan Creps
+Tessitura Network
From: Tessitura Ticketing Forum [mailto:forums-ticketing@tessituranetwork.com] On Behalf Of Maurice NounouSent: Wednesday, January 04, 2012 7:42 PMTo: Ryan CrepsSubject: RE: [Tessitura Ticketing Forum] Report for Constituents when a show is canceled
Thank you firstly. I gave this a try and it came back with 0 results. I think it is because there isn't the line to replace 120121LR within the query you posted. Is that the entire query or did part of it get cut off?
From: Lucie Spieler <bounce-luciespieler8144@tessituranetwork.com>Sent: 1/4/2012 6:22:13 PM
If you paste the following query into the manual edit area of a new list, and replace 851 with the perf_no of the performance you are looking for, and the 120121LR with the perf_code for the same performance, this should give you everyone who has ever had a seat reservation for a particular performance, but who is not currently ticketed as of the last time your ticket history updated (ours updates once a day, at night).
If you don’t have access to ticketing setup, I don’t know how to find the perf_no, which appears on the performance screen, in the Tessitura client. (If you can look at T_PERF via Microsoft SQL Server Management Studio, of course, that’s the easiest way.) You’ll have to get someone in IT to help you there. The performance_name appears on the ticket history screen in our system as “Perf. Name” and also on the top of a relevant seat map in the House View of Season Manager, as well as being the perf_code in an order in the ticketing module.
AND f.perf_no = 851 REPLACE
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Ticketing Forum. You may reply to this message to post to the Ticketing 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!
We had a canceled perf recently, but we found that this script also pulls anyone who exchanged out of the performance well before the cancellation. Is there anyway to constrain it by order date, so that the list only contains the patrons who were still in the performance at the moment it was canceled?
This won't exactly help if the show were already cancelled and you're tracking down the people after the fact, but Ryan's suggestion of the seatbook is a good one for this. When we've had to cancel a performance before, we've usually run that first, to get all the people currently in the perf and save to a list, and then you can either script creating CSIs for all the people specific to the cancellation, or make a source code related to the cancellation to use with refunds, or just to have that list for reference.Then after isolating who they are, run Performance Seat Release (or whatever other method you're using to get people out of the cancelled perf). Then you don't have to backtrack.
If you are doing it after the fact, it might be easier to pull people with returned sublineitems for the perf that were created after the cancellation, rather than figuring out who was seated/ticketed as of that time. Something like
AND f.perf_no = 481--replace with the perf_no
AND f.sli_status in (4)
AND f.create_dt>'2/1/2012' --replace with the date it was cancelled