Solved

two reporting services questions - conditional subscriptions / business day logic

Posted on 2006-11-10
8
1,392 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now