Solved

two reporting services questions - conditional subscriptions / business day logic

Posted on 2006-11-10
8
1,394 Views
Last Modified: 2008-01-09
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 ?
0
Comment
Question by:dbaSQL
  • 5
  • 3
8 Comments
 
LVL 17

Author Comment

by:dbaSQL
ID: 17917201
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
 
LVL 21

Accepted Solution

by:
Yurich earned 500 total points
ID: 17919273
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 17921131
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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 17

Author Comment

by:dbaSQL
ID: 17921166
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
 
LVL 21

Expert Comment

by:Yurich
ID: 17927362
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 17929221
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
 
LVL 21

Expert Comment

by:Yurich
ID: 17935491
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 17940196
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question