Hi, all-
We have some reports that are being distributed daily, even on days where there is no information on the report. We would like to adjust the schedule so that the report is only emailed when data is actually populated.
Has anyone figured out a way to set this up?
Hoping someone has an answer or clever method...
Thank you!
I wrote a kind of package for handling this which is published on our Bitbucket site at https://bitbucket.org/TN_WebShare/report-server-tools/.
(If you don't have access to the TN_WebShare team, you can request access at https://tessituranetwork.com/devs).
There are two main components to the report-server-tools package. The first is a toolkit for manipulating the report server in a variety of ways, and the second (built on top of the first) is a Conditional Report Scheduler feature. We use this to do what you're talking about "at scale", where conditions (for example, "Report Not Empty") can be set up for any report, and then a user who has created a standard schedule can "conditionalize" it using a utility that takes it out of the standard report rotation and allows it to be managed by a daily agent job.
This was built on v12.5, but I am underway on a v14 update now, so if there are any changes needed, they will be published to the Bitbucket repo.
I am aware that there are some changes in v15 that will require some components of this project to be modified, which I am looking forward to doing once V15 is released (the Tessitura Processing Service runs reports much faster, which I'm very happy about). This feature is not going to be included out of the box in v15.
Hi, Nick--
Nick Reilingh said:This feature is not going to be included out of the box in v15.
So, the v15 Report Server still can't handle "don't send the report if it's empty", either? (I haven't looked closely at v15 yet)
*Sigh* That's sad, if so. Not like anyone wants that... *eyes the current count of 366 subscribers to this thread*
To be honest, this feature would be a pretty big ask for the devs. Think about how you would actually implement that -- report objects in Tessitura as they exist now don't have a concept of being empty or populated. An SSRS report can pull data from any number of sources, and ALWAYS outputs information like headers/footers and the parameters block. You can't tell SSRS to run a report and then ask it if it returned any results in a way that is both programmatic and reliable, i.e. generalized for the set of all SSRS reports.
The only way to do it is to implement the criteria in code and ship that along with each report; then provide for functionality in the report scheduler to accept custom criteria-based schedules. The solution I posted basically does this, but it takes advantage of the fact that nine times out of ten, reports are based on a stored procedure, and the notion of a report being populated or empty is equivalent to the rows output from that procedure for a set of parameters. (This is more difficult to actually implement than it sounds.)
What the network really needs to do for us is expand the API surface (either on the database side or elsewhere) for the Tessitura Report Server, in order to provide for custom scheduling logic. (My solution only works because I reverse-engineered the Report Server functionality and gooesoft tables and built a corresponding T-SQL API to develop against.) What if we could say "Every Wednesday, run one report for every performance of a certain set of perf types or prod seasons occurring in the next 3-5 days by passing parameters corresponding to each performance to a standard SSRS report, and then email the report to different people based on what title the performance is in"? I can do that on my tricked out database with about an hour of coding, but think of the kind of user interface that would be required to make Tessitura have that capability out of the box -- it would be INCREDIBLY complex. I don't think that's the problem we want the devs to be solving. It would be way more valuable for them to make the underlying structure more flexible and plugin-friendly.
Nick Reilingh said:report objects in Tessitura as they exist now don't have a concept of being empty or populated.
I wouldn't try to see if *any* report object was populated, only if the sproc generated any rows of output.
Nick Reilingh said:My solution only works because I reverse-engineered the Report Server functionality and gooesoft tables and built a corresponding T-SQL API to develop against.
The way I do it only works because I run the report twice, i.e. once -> a tmp table, and then again as a one-off Report Server job, if the first execution generates >0 rows. Feels pretty easy, but does require a custom sproc for every scheduled report.
The "hard part" I was talking about is basically taking the same approach as you mention, but solving it in a general case so that the tool can actually write and run the custom sproc for you -- you just tell it (for each Tessitura Report object) what procedure to run, what the expected output format for the procedure is, and how the report parameters map to the procedure parameters.
Nick Reilingh said:The "hard part" I was talking about is basically taking the same approach as you mention, but solving it in a general case so that the tool can actually write and run the custom sproc for you
Clearly. Exactly the sort of difficult, widely-shared problem that the network *should* be working on, imho.
In the spirit of Siskel and Ebert, I give this two thumbs up.