two reporting services questions - conditional subscriptions / business day logic

This is sooooooo very urgent.  I need to know if there's any way to refrain from sending an email if the tSQL behind the rdl is empty?  it's a trading report, clearing information is sent out in a report via reporting services.  i do not want to send a blank email if no trades exist.  but i must have it in place/enabled/scheduled, such that the report does capture trades if/when they occur.  i've got the report and subscription created, i just now received a blank email.  i cannot do that once this is deployed

similarly, and slightly less urgent, can i add business day logic to the sql agent job?  i use this piece in each of my sql agent jobs:

SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
BEGIN
....whatever....
END

this way i can remain consistent with the business days defined in my calendar table.  (you know...don't send a blank report on christmas day)
is there any reason why i cannot put the same condition into the RS-created SQL Agent jobs ?
LVL 17
dbaSQLAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
YurichConnect With a Mentor Commented:
The NoRows property is a bit different and it's got nothing to do with subscribing. It's used to show a message where there is no result in your data region (table, list, matrix). To access it, select your data region (e.g. your table) and press F4 (for properties). You'll find it among other properties. You can put a phrase in the parameter saying something like "There is no updates for 11/11/2006".

I don't know any ways of conditioning your subscription depending on the result set. I don't think there is any easy way of doing it. Only through external programming I'd assume.

Regards,
Yurich
0
 
dbaSQLAuthor Commented:
i am reading the hitchiker's guide to RS...it would seem i can set the NoRows property to just give a message if/when the report returns no data.  i'd prefer nothing was sent, but this will do for now.  but can anybody tell me where that property is set?
0
 
dbaSQLAuthor Commented:
Perfect, Yurich.  Yes, I'd rather refrain from sending anything at all, if there is no data for viewing, but this will be just fine.  I was all over that thing looking for the NoRows property....just couldn't find it until you gave me the F4.  I had been right clicking and selecting properties...don't really know the difference, but I found it now, and it works.  very cool

thank you very much
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
dbaSQLAuthor Commented:
Yurich, do you believe I could do this:

>>>>>>>>>>>
similarly, and slightly less urgent, can i add business day logic to the sql agent job?  i use this piece in each of my sql agent jobs:

SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
BEGIN
....whatever....
END

this way i can remain consistent with the business days defined in my calendar table.  (you know...don't send a blank report on christmas day)
is there any reason why i cannot put the same condition into the RS-created SQL Agent jobs ?
0
 
YurichCommented:
you can do similar stuff in expressions for conditioning of the output, but I doubt, or better say, I don't know how you can do it for subscriptions.

Regs,
Yurich
0
 
dbaSQLAuthor Commented:
well, you know the subscriptions create their own sql agent jobs.  what i'm asking is do you think there's any problem with me modifying the agent jobs with that piece up there....to control the execution of the scheduled subscription, such that it corresponds with my business day calendar?
0
 
YurichCommented:
I'm sorry, but I've never done it myself, but you can try and tell us whether it worked or no ;)

regs,
yurich
0
 
dbaSQLAuthor Commented:
Well, Yurich, I think it worked.  I changed this:
exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='8141add6-a259-484c-9416-63321181686b'

to this:
SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 4 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN
exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='8141add6-a259-484c-9416-63321181686b'
END

today's DATEPART is 3, so i figured this would pretty much make it invalid
sql agent says it ran, my job log says this:
Job 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4' : Step 1, 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4_step_1' : Began Executing 2006-11-14 12:15:00

not much, yes, i know
in report manager, the subscription page, Last Run is blank.  no date...so...kinda like it ran, but it didn't send the subscribed email

what do you think?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.