Solved

two reporting services questions - conditional subscriptions / business day logic

Posted on 2006-11-10
8
1,396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

705 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