Schedule in report

Question

Post reply

Schedule in report

Helical Joan2

Old Hand

Points: 368
More actions
May 21, 2010 at 11:20 am

#235384

Hi, I have a report but I want send email all days less holidays. For example, by weekend no problem, because in my suscription doesn't put saturday and sunday; but another day? How can I configure it?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Abs-225476 Hall of Fame Points: 3483 More actions · Answer 1

Hi,

You need to check to see if the standard subscription schedule fits your requirements.

If this does not then it is probably best to do a data driven subscription with a custom script or table.

Abs

Helical Joan2 Old Hand Points: 368 More actions · Answer 2

hi,

how can i do a data driven subscription with a custom script or table?

Thanks

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 3

Check this tutorial

http://technet.microsoft.com/en-us/library/ms169673(SQL.105).aspx

Abs-225476 Hall of Fame Points: 3483 More actions · Answer 4

Basically you will need to create a custom table which contains the dates you want the report to run.

Then you will need to create a simple sql script which will check the current date against the date in your custom table. The data driven subscription should be on a daily schedule which only fire when the yoour sql script has data, just make sure you test it before going live and that it does not return more than one row of data (otherwise you will ge multiple reports!)

Example

select * from custom_table

returns

date

'2011-01-02'

'2011-01-03'

'2011-01-04'

your sql script in the data driven subscription will be something like

select * from custom_table where date = getdate()

You will need to strip out the time element from getdate.

Cris E Ten Centuries Points: 1255 More actions · Answer 5

A quick alternative to this is creating a table of hoidays you want to skip and then incorporating some of the ideas from this other thread on not sending scheduled reports when they're empty. There's a sproc at the end of the thread that you could bend to include a check against that holiday table as the test for the RAISERROR. The beauty of this is that it'll work with Standard Edition Sql Server.

[font="Arial"]Are you lost daddy? I asked tenderly.
Shut up he explained.[/font]
- Ring Lardner