May 2, 2008 at 6:48 am
I need to create a shared schedule in SSRS 2005 for the last day of the month. Not the last Friday, Monday, Sunday, etc., but the last day of the month. This option does not appear to be be available. Anybody figure out why and a work around for it?
Thanks
-A.
October 31, 2008 at 6:33 am
An update on this.
I thought I had figured out how to do this. I set up a total of three separate shared schedules (not bothering with leap year at this point),
1 - on the 30th day of April, June, September & November
2 - on the 31st day of January, March, May, July, August, October & December
3 - on the 28th day of February
However this doesn't work as I expected because I'm still getting schedules executed on the 30th day of the month in months that have 31 days.
Again, any insight would be helpful. I can't believe that I'm the only one that's trying to figure this out.
November 3, 2008 at 9:39 am
i did the exact same thing and have the same problem. i havent found an answer yet.
November 3, 2008 at 4:10 pm
What I had tried before was to create a managed schedule through SSRS, scheduling it for the last Friday of the month. Then I went to the SQL Server Agent and modified it to run on the last day of the month. When I did this before it didn't work and caused a fatal error within SSRS.
What I discovered is that you also have to modify the the next run time column in the SSRS table to make it work. Specifically, you need to modify the dbo.Schedule.NextRunTime column to really be the next run time,
Example:
On 10/31/2008, I created a shared schedule to run on the last Friday of the month at 10pm in SSRS, I changed the start date of the schedule to 11/1/2008, which made the next run time 11/28/2008 @ 10pm. I then went into the SQL Server Agent job that SSRS subsequently created and modified the job to run on the last day of the month (now the next run time listed in SQL Server Agent is 11/30/2008 @ 10pm).
I then went into the ReportServer database on my SSRS server and modified the dbo.Schedule.NextRunTime column for this schedule from 11/28/2008 @ 10pm to 11/30/2008 @ 10pm. This now matches what's in the SQL Server Agent job and it passes the consistency check that SSRS runs.
Hope this helps.
November 3, 2008 at 5:41 pm
Angela Henry (10/31/2008)
An update on this.I thought I had figured out how to do this. I set up a total of three separate shared schedules (not bothering with leap year at this point),
1 - on the 30th day of April, June, September & November
2 - on the 31st day of January, March, May, July, August, October & December
3 - on the 28th day of February
However this doesn't work as I expected because I'm still getting schedules executed on the 30th day of the month in months that have 31 days.
Again, any insight would be helpful. I can't believe that I'm the only one that's trying to figure this out.
Do you mean schedule jobs? Or ???
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2008 at 8:40 am
Shared schedules ~ SSRS
Scheduled jobs ~ SQL Server Agent jobs
November 4, 2008 at 8:48 am
I found this link and this should work. http://developmentnow.com/g/115_2005_11_0_0_629283/Scheduling-outside-SRS.htm
You basically create a phony subscription then go to the reporting services database on that server. Get the information you need from the subscriptions table. run dbo.addevent with the correct values and it will run the report.
So i put this logic into a stored procedure that checks if its the last day of the month then it will run the report.
Now just schedule a job to run daily.
March 17, 2009 at 10:25 am
Does anybody have any information, links or experience on expanding on / editing the SSRS Scheduling GUI or the ReportingServices.dbo.Schedules / ReportSchedule tables in SSRS 2005?
I think it's plain lazyness that Microsoft didn't give SSRS the same quality scheduling options that you get with the SSMS Agent!
March 26, 2009 at 11:55 am
Maybe this has already been said in a different way, but this is what I've done, and I think it works -- please feel free to try it out and post your findings.
I created a table SchedTable of dates that I need a data-driven SSRS report to go out, and set the query for the recipient list, fields, etc to:
SELECT 'me@awesome.com' as EmailAddress, 'blah' as parameter1, EmailDate from SchedTable where convert(varchar(10), getdate(),102) = EmailDate
I scheduled this to run daily in the SSRS scheduler, but since the query only returns data for the schedule dates in my table, the report is only sent out on the dates I want.
July 27, 2009 at 4:13 pm
I'm not sure why no one else listed this solution but it builds off of using the addevent to explicitly invoke the subscription. Only, instead of building a stored procedure which runs every day, simply add a new schedule to the SQL Agent on the report server which uses a SQL Task to process the addevent. Then you can take advantage of the same scheduling options you have in SQL Agent to invoke the SSRS subscriptions. It's a pretty simple work-around. Make sure you use a descriptive name when creating the new jobs to invoke the SSRS subscriptions however or later it becomes difficult to tell which job invokes which subscription.
You can also use sp_start_job to invoke the schedule created by the SSRS subscription. Either way should work.
You don't need to run an SP every day to check if it's the beginning or end of the month since SQL Agent has these options and will use the addevent to run the subscription at the appropriate time.
One more thing : Do not change the timing of the schedule that SSRS adds as part of the subscription (The one with the GUID name) because it will not work. I found this through trial and error.
August 28, 2009 at 3:24 pm
I just ran into this same problem, which is why I arrived at this forum. Man, for such an experienced software development company making such a MASSIVE oversight seems incredible. How could you create a reporting product that can't do something as critical as create month-end reports? And still enable it to create something as worthless as month-beginning reports?!?!?
Who in the world wants to know how much money they've made for the month on the first day of the month? Wow MS... what a HUGE oops!
August 28, 2009 at 3:36 pm
If you have a business that is 24/7, such as a website, no matter what time you run your reports at the end of the month, you risk missing some transactions. However, if you run your reports on the first day of the month and report on last month's figures, you are certain to get all of the transactions for last month.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 5, 2010 at 12:17 am
I have found a workaround that works for me.
Follow these steps to create the report for the entire last month:
1. Go to The Reports http:\\<yourserver>\reports
2. Click on show details on the right
3. Choose one Report you want to schedule and click on the Edit button
4. Click on the Edit Button in the Report definition and save the Report on your drive.
5. Open The Report in the notepad
6. In the first Default Value Section type: DateTime.Today.AddMonths(-1)
7. In the second Default Section type: DateTime.Today.AddDays(-1)
Example:
<DefaultValue>
<Values>
<Value>=DateTime.Today.AddMonths(-1)</Value>
</Values>
</DefaultValue>
<Prompt>Start Date</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate">
<DataType>DateTime</DataType>
<Nullable>true</Nullable>
<DefaultValue>
<Values>
<Value>=DateTime.Today.AddDays(-1)</Value>
</Values>
</DefaultValue>
<Prompt>End Date</Prompt>
8. Save it and go back to the Report tab in your browser click on upload file
9. Click on your Report --> Subscription --> New Subscription
10. Execute your Schedule monthly on calendar day(s): 1
11. In the Start date and End date Values click on both Use Default
Best Regards
RJordan
August 18, 2010 at 7:50 pm
Hi everyone - just wanted to pitch in. Trying to also set up my subscription to send out at the very last day of the month. I've got a pretty easy method I think but it does have a drawback which is pretty minor (depending on your users I guess).
OK, I have a monthly report that uses 2 parameters - month and year. I have the report parameters default to the current month and year. But this thing has to go out at the very last day of the month so as a workaround, I set the subscription to the very 1st of the month and then default my parameters like so:
MONTH parameter default value = IIF(Month(Now)=1,12,Month(Now)-1)
YEAR parameter default value = IIF(Month(Now)=1,Year(Now)-1,Year(Now))
Have to use the IIFs because of the issue of January - when January, now gotta default to December of the previous year, but otherwise, default to the previous month of the same year.
The downside? When users open the report, it'll default to the previous month. So it may confuse some users but I think it's a fairly minor nuisance. Hope this helps everyone.
August 20, 2010 at 9:20 am
dso808 (8/18/2010)
Hi everyone - just wanted to pitch in. Trying to also set up my subscription to send out at the very last day of the month. I've got a pretty easy method I think but it does have a drawback which is pretty minor (depending on your users I guess).OK, I have a monthly report that uses 2 parameters - month and year. I have the report parameters default to the current month and year. But this thing has to go out at the very last day of the month so as a workaround, I set the subscription to the very 1st of the month and then default my parameters like so:
MONTH parameter default value = IIF(Month(Now)=1,12,Month(Now)-1)
YEAR parameter default value = IIF(Month(Now)=1,Year(Now)-1,Year(Now))
Have to use the IIFs because of the issue of January - when January, now gotta default to December of the previous year, but otherwise, default to the previous month of the same year.
The downside? When users open the report, it'll default to the previous month. So it may confuse some users but I think it's a fairly minor nuisance. Hope this helps everyone.
Ummm.... why can't the call for the automatic first of the month report provide the date whereas the users provide their own date? I wouldn't build that particular nuance into the code directly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply