<burkecro...@discussions.microsoft.com> wrote:
> thanks Ayman. i can see and understand your method and may use that for
> future reports. i was hoping not to have to redo the report query because of
> the oddities of my source data.
>
> i don't understand why MSFT has the difference between the scheduling
> interfaces of RS vs SQL Server Agent. seems like RS could have just
> inherited from SQL Agent...
>
> burke
>
> "Ayman" wrote:
> > On Jul 27, 9:12 am, burkecrosby
> > <burkecro...@discussions.microsoft.com> wrote:
> > > i'd like to schedule some reports to run just before midnight on the last day
> > > of each month. it doesn't appear that the RS interface allows me to create
> > > just one schedule that would do that (i end up creating 3 schedules: 1 for
> > > months ending 30th, 1 for months ending 31st, 1 for February).
>
> > > on the sql agent side of things, its interface allows scheduling on the last
> > > day of the month, but you're not supposed to mess with that....
>
> > > am i missing something? is there an easy workaround? thanks
>
> > 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!