Scheduling Performance Seating Book

Hi Matthew,

A few ideas for you:

• I'm sure you've thought of this, but a dynamic list could be designed to identify the donors with tickets for tonight's performance using relative data criteria. You could schedule the "Execute an Output Set" report to email an Excel document of the basic constituent data of the people on the list. Including "tonight's seat locations" is not a standard Output Set element though, so this might not meed all your needs. 
• The Order Export Utility is designed for a completely different purpose than what you're intending, but it does conveniently have parameters that are more conducive to a dynamic report schedule, and the output includes seat locations. (And if you're wondering, it does not update system data.)
  
• The Performance Seating Book is an SSRS report. It can be run from a browser, including from the Tessitura On The Go system. Perhaps the executives could be directed to this resource so they can access the information directly, or someone like an admin professional could assist them?

Best,

Michael Wilcox, Tessitura Network

Hi Matt,

I definitely feel your pain on this -- we run morning-of performance reports that go out to a specific group of people, and only need to go out on days with a performance. Eventually I plan to build a pretty complicated agent job that figures all of this out based on the performances each day, and probably some inventory keywords, but in the meantime I've managed to hack the functionality I want out of the report server with something a little closer to stock.

Basically, I made a copy of the Development/Performance Report procedure, and then changed the parameters and logic so that it runs based on the date of the performance and a couple other filters, and if the procedure determines that there are no performances for the date it is being run on, it errors out. Then the report is scheduled to run on a daily basis, with the performance date set as RunDate.

What I discovered is that if I throw the right type of error in the stored procedure, the report server will not send the Email-PDF output to the recipient list, AND will also not disrupt the schedule for future runs. (It will still kind of pollute the report server error log, but who cares.) The code snippet I have in the procedure looks like this:

IF NOT EXISTS (SELECT 1 FROM #perfs)
	BEGIN
		RAISERROR('No matching performances today', 11, 2)
		RETURN
	END

(#perfs is a temp table that is populated with the list of matching performances for today.)

So what this allows me to do for a run of performances is create ONE REPORT SCHEDULE that spans the entire range. The report sends output whenever there's a performance, and stays silent when there is not.

Hope this helps!

Nick, that is a super useful tip. I'm going to spend some time with the RP and see if I can find a way to make it work for us.

Thank you both!

Update: following Nick's excellent suggestion, I copied and modified the standard Performance Seating Book procedure to run using production, production season, start and end dates. Not having the time or inclination to deploy a whole new report this week, I pared the results down to be readable in Excel so that the leadership can see performance dates, names, and seat locations for the entire weekend of shows coming up.

I'm thinking I'll bypass the report server altogether and just email the results using sp_send_dbmail and a SQL Server Agent job. And eventually I'll turn it into a full-fledged report. But that can wait until after Thanksgiving!

Thanks again for the suggestions.