How to Modify an SSRS Subscription to Only Run First Monday of Every Fiscal Month
Hello, My team needs to run a scheduled report on every first Monday of every new fiscal month. Unfortunately, SSRS 2008 Subscriptions scheduler doesn't really allow a lot of flexibility in this area. To remedy this, i've been looking at the various tables in ReportServer DB but nothing is really popping out at me. Alternatively, i'm not sure if a data-driven subscription is the way to proceed or not as I do not have experience in this area. What is the best way to go about this? Some givens about my environment: - my team is using Report Manager to deliver reports to end users (I already know not the best idea) - we are NOT ASP.NET or web developers and with the exception of myself most of us do not know how to build applications to in which to embedded SSRS reports. As such, we do not know how to call the Reporting Services APIs nor how to use them. Further, we are restricted by company rules from going this route so it is simply not an option. All changes or development must be done in T-SQL. Thanks!!
December 28th, 2010 2:40pm

When you write the query in data driven subscriptions to get the parameter data write your tsql or sp in such a way that it only gets data on the dates you want the report to be sent out, rest of the days the t-sql should not bring back anything. This way Data driven subscriptions will only send reports on those dates. Regards, Vikram Kansal
Free Windows Admin Tool Kit Click here and download it now
December 28th, 2010 3:16pm

So i'm confused as to how this works - I simply write a query which returns the date on which I want to report to run? Once it runs, it calls my current stored procedure which returns my report on that date? I'm finding data-driven subscriptions to be confusing, and absolutely NO examples using dates.
December 28th, 2010 4:21pm

Just did a bit of google : http://joegilldotcom.blogspot.com/2009/10/reporting-services-data-driven.html in the above link if you think of your case only bring back email receipients on the dates you want to send it ouy. regards, Vikram
Free Windows Admin Tool Kit Click here and download it now
December 29th, 2010 4:54am

Here is my example: 1. Create a table to store data-driven subscription parameters. 2. Create a query to provider parameter for data-driven subscription, the query should have a condition to determine whether today is the first Monday of fiscal month. Such as: SELECT [To] ,[Cc] ,[Bcc] ,[Reply_To] ,[IncludeReport] ,[RenderFormat] ,[Priority] ,[Subject] ,[Comment] ,[IncludeLink] ,[Parameter] FROM [DataDrivenSubscription] WHERE GETDATE() = the first Monday of fiscal month 3. Create a data-driven subscription based on the query you created at step 2, and then set its schedule run once every day. In fact, the data-driven subscription will run once every day as you set at step 3. If the day which the subscription runs is not the first Monday of fiscal month, the query will return a result set contain nothing, and then the subscription will deliver the report to nobody, otherwise it will deliver the report as normal. So, it looks like deliver report on every first Monday of fiscal month. If there is anything unclear, please feel free to ask. Thanks, Albert Ye
December 30th, 2010 9:34pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics