Report to Send Last Day of Each Month?

Hi All...
Unless I'm missing it, I don't see a way to set a data-driven email
subscription to be scheduled to send on the "last day" of each month (at
10:00PM).
Is there a way to do this in SQL 2000 Reporting Services Enterprise Edition?
Thanks in advance!
Kind regards - FredHi Fred
Here is a work around
1. Create a shared schedule and set it up never to fire , you can back
date it .
2. Create your subscription and set it to use this schedule
3. Get the subscription id from sql server using a query like
select * from reportserver..subscriptions
4. Write some code to call the FireEvent function using
"TimedSubscription" as the first param and the subscriptionid as the
second param. The user needs to have "Generate events" permission
http://msdn2.microsoft.com/en-us/library/aa225803(SQL.80).aspx
5. Schedule the code .
You can do step 4 in various ways but I suppose the easiest is to
script a .rss file , using vb.net and use windows scheduler to execute
the rss code. The code will look something like
Sub Main()
Dim rs As New ReportingService()
<Check whether today is last day of the month and if it is>
rs.FireEvent("TimedSubscription", "<subscription id>")
End Sub
If you save that as a rss file, you can execute it using "rs.exe -i
<your rss file name> -s http://servername/reportserver"
Cheers
Shai
On Nov 20, 2:49 am, "Fred Block" <fblock_no_spamm...@.w-systems.com>
wrote:
> Hi All...
> Unless I'm missing it, I don't see a way to set a data-driven email
> subscription to be scheduled to send on the "last day" of each month (at
> 10:00PM).
> Is there a way to do this in SQL 2000 Reporting Services Enterprise Edition?
> Thanks in advance!
> Kind regards - Fred|||Took me a while to figure it out, but it works great. It might not be
exactly what you want, but who knows it may work out for you. I got a
request from a user that they get the previous month's report on the
first of the month at 7am (time doesn't matter here). So for example,
on August 1st at 7am, she will receive a report for July1-31st. You
can change the time to be 12:00am or whatever.
First in your report make two datasets with the following:
Dataset 1 (I called mine StartofMonth):
select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
Dataset 2 (I called mine EndofMonth):
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
In my main dataset, the code looks for data between two parameters
@.startdate and @.enddate. Default these parameters (under
Report>Report Parameters...) to the corresponding datasets. The
"field" here should just be ID. Run the report, it should pull data
from the first to the last of last month.
Now go to your subscription and make your schedule. Under Schedule
Details (when you select to make your own schedule) click the Month
radio button. Pick all the month (using the check boxes) and then
select the radio labeled "On Calendar day(s):" and place a 1 in that
box. Now just adjust your start time as you see necessary. August
1st is coming soon, so you can see the result of your effort soon!!!
Only issue with this work around is that whenever your users open the
report (if they ever will) the dates will default to the first and
last day of the previous month. Oh well... they can change it
manually I guess or you can make a specific report (duplicate) that is
just for subscriptions. Also, for sanity, you might want to display
your parameters on the report so you see that it worked correctly.
Let me know if that works out for you!|||Hi All,
Thanks to you "both"! I'll check out these ideas and methods. Thanks for
taking the time to help me with this!
Kind regards - Fred