YAPS about Report Manager: Making Do with Data-Driven Subscriptions

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 creating subscriptions as part of the Report Manager are somewhat ridiculous, and easily bested by a small amount of code. (See: here and here for examples.)

But, at Dominican, 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, here, or search for branding 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.)

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 avoiding that crappy wizard, and just writing the proper code in-situ.

To review

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.

I’ve written about this before (for example, here) 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 .

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:

declare @auditCount as int  = 0;

-- get a count of the rows that would be contained in the
-- report you'd be sending via subscription:
-- this query should be a simplified version
-- of the results query in your report,
-- with parameters replaced by the values
-- you're actually using in your subscription,
-- whether default or derived by expression:
select @auditcount = count(*) 
from whatever
join whatever
on x = y
where something; 

-- here's the important bit:
select 'your recipient list' as EmailTo, 'your CCs' as EmailCC 
-- add whatever other columns you wish to data-drive 
-- in the subscription, here, and add this important clause:
where @auditcount > 0 ;

Now extend that thought

One of my persistent gripes about the stupid Wizard is the limited nature of the scheduler portion, which is not only not data-driven but also terminally inflexible.  

I mean, come on, does anybody even think the Start time  makes any sense, as implemented, for an hourly subscription?

 

Hmph. My typical requirements for subscription scheduling are more like

“For some months, run it every hour during business hours only, and for other months, run it once a day.” 

 

… 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.

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.

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:

if @auditcount > 0 
begin
    declare @today as datetime = getdate() ;
    declare @thisHour as int = DatePart(hour,@today),
            @thisMonth as int = DatePart(month,@today) ;
    if @thisMonth in (1,2,3,9,10) 
    begin
       if @thisHour != 8
       begin 
          set @auditCount = 0 ;
       end ;
    end ;
    else
    begin
       if @thisHour < 8 or @thisHour > 19
       begin
          set @auditCount = 0 ;
       end ;
    end
end ;

… now we’ve effectively suppressed the report, even if there is data, except when it is wanted.

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:

For the purists among you…

Well, yes, it’s not gorgeous code, and I’m sure you could write it better. 

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?

I think I’m done.