{"id":22,"date":"2018-04-28T09:47:00","date_gmt":"2018-04-28T09:47:00","guid":{"rendered":"\/lisa\/post\/2018\/04\/28\/yaps-about-report-manager-making-do-with-data-driven-subscriptions.aspx"},"modified":"2018-04-28T09:47:00","modified_gmt":"2018-04-28T09:47:00","slug":"yaps-about-report-manager-making-do-with-data-driven-subscriptions","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2018\/04\/28\/yaps-about-report-manager-making-do-with-data-driven-subscriptions\/","title":{"rendered":"YAPS about Report Manager: Making Do with Data-Driven Subscriptions"},"content":{"rendered":"

I have often talked about how much I dislike the Report Manager and why I think it shouldn’t be exposed to end-users, or be considered a proper UI for production work (see: Nixing the Report Manager<\/a> for what I hope is my final word on that subject).  I’ve also explained that I think the wizards for creating subscriptions as part of the Report Manager are somewhat ridiculous, and easily bested by a small amount of code. (See: here<\/a> and here<\/a> for examples.)<\/p>\n

But, at Dominican<\/a>, the Report Manager is what we have, and we get quite a lot done with it.  (For examples of how I try to wring the last bit of functionality out of the UI and its underlying capabilities, see here<\/a>, here<\/a>, or search for branding<\/a> on this blog. I’d be updating that mini-series on Report Manager branding for 2016 if it wasn’t so much easier that I didn’t think it was worth a post… if you write and tell me differently, I’ll post something.)<\/p>\n

I’ve been finding, in fact, that some of my fancy-ass work to make data-driven subscriptions better can be done quicker by not<\/em> avoiding that crappy wizard, and just writing the proper code in-situ.<\/p>\n

To review<\/h3>\n

One of the best reasons to use a data-driven subscription is to obviate the sending of the subscription at all when it’s not needed.<\/p>\n

I’ve written about this before (for example, here<\/a>) but I’ll cut this down to the bare requirement:  You don’t want anything to go out when you’re attempting to get somebody to perform a task by sending the report, but right now there is no task to perform.  For example, no account out of balance, or no instance of a particular data anomaly to be listed in the report and cleaned up .<\/p>\n

You accomplish this very simply in a data-driven subscription by defining the recipients as follows — you can write this code directly in the stupid Wizard box, or write a stored procedure, doesn’t matter:<\/p>\n

declare @auditCount as int  = 0;\n\n-- get a count of the rows that would be contained in the\n-- report you'd be sending via subscription:\n-- this query should be a simplified version\n-- of the results query in your report,\n-- with parameters replaced by the values\n-- you're actually using in your subscription,\n-- whether default or derived by expression:\nselect @auditcount = count(*) \nfrom whatever\njoin whatever\non x = y\nwhere something; \n\n-- here's the important bit:\nselect 'your recipient list' as EmailTo, 'your CCs' as EmailCC \n-- add whatever other columns you wish to data-drive \n-- in the subscription, here, and add this important clause:\nwhere @auditcount > 0 ;<\/pre>\n
Now extend that thought<\/h3>\n
One of my persistent gripes about the stupid Wizard is the limited nature of the scheduler portion, which is not only not <\/em>data-driven but also terminally inflexible.  <\/p>\n
I mean, come on, does anybody even think the Start time  makes any sense, as implemented, for an hourly subscription?<\/p>\n
 \"\"<\/p>\n
Hmph. My typical requirements for subscription scheduling are more like<\/p>\n
\n
“For some months, run it every hour during business hours only<\/strong>, and for other months, run it once a day.” <\/p>\n<\/blockquote>\n
 <\/p>\n
… this is usually added into the the instruction to suppress the email notification whenever there was no data to report — which I’d easily be able to take care of in a wizard-based subscription, as sketched above.<\/p>\n
Recently I had one of these requests, and I was about to spend some time putting together a custom subscription job, as I have in the past. Then I realized I only had to extend what I was already doing a little bit and I was there.  I just needed to add a bit to the suppression criteria.<\/p>\n
So, after evaluating the count of rows to be reported, in the code sketch above, and before the “important bit” that actually determines the recipients, insert the following code:<\/p>\n
if @auditcount > 0 \nbegin\n    declare @today as datetime = getdate() ;\n    declare @thisHour as int = DatePart(hour,@today),\n            @thisMonth as int = DatePart(month,@today) ;\n    if @thisMonth in (1,2,3,9,10) \n    begin\n       if @thisHour != 8\n       begin \n          set @auditCount = 0 ;\n       end ;\n    end ;\n    else\n    begin\n       if @thisHour < 8 or @thisHour > 19\n       begin\n          set @auditCount = 0 ;\n       end ;\n    end\nend ;\n<\/pre>\n
… now we’ve effectively suppressed the report, even if there is data, except when it is wanted.<\/p>\n
If you’ve done this right, you’ll see this type of result (not an error), when the subscription doesn’t run because of your ever-more-complex criteria:<\/p>\n
\"\"<\/p>\n
For the purists among you…<\/h3>\n
Well, yes, it’s not gorgeous code, and I’m sure you could write it better. <\/p>\n
And, again, nobody likes typing into those stupid subscription text boxes, but you can see how to add this in your stored procedure, can’t you?<\/p>\n
I think I’m done.<\/p>\n","protected":false},"excerpt":{"rendered":"
I have often talked about how much I dislike the Report Manager and why I think it shouldn’t be exposed to end-users, or be considered a proper UI for production work (see: Nixing the Report Manager for what I hope is my final word on that subject).  I’ve also explained that I think the wizards for creatingRead more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6,12],"tags":[],"class_list":["post-22","post","type-post","status-publish","format-standard","hentry","category-reporting","category-sql-server","category-yaps"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/22","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=22"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/22\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=22"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=22"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=22"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}