Reporting Services - Parameters in a Fileshare Subscription

Question

Reporting Services - Parameters in a Fileshare Subscription

GloriousRuins

Right there with Babe

Points: 730
More actions
October 26, 2007 at 5:01 am

#183286

Hi all,
I am setting up subscriptions for a number of reports I've created, and I've successfully managed to have them write to the fileshare folder. Some of the reports have date parameters (start and end date etc.), and I've got them working by entering the dates in the parameter values at the bottom of the subscription page. However, what if I want to have the reports run on a daily basis using different dates each day - for example, run using "today" as a parameter, or maybe "today" as the 'end date' and "today - 7 days" as the 'start date'? Is this something I'm going to be able to set up?
Thanks in advance
Scott

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

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

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 1

Scott B (10/26/2007)
Hi all,
I am setting up subscriptions for a number of reports I've created, and I've successfully managed to have them write to the fileshare folder. Some of the reports have date parameters (start and end date etc.), and I've got them working by entering the dates in the parameter values at the bottom of the subscription page. However, what if I want to have the reports run on a daily basis using different dates each day - for example, run using "today" as a parameter, or maybe "today" as the 'end date' and "today - 7 days" as the 'start date'? Is this something I'm going to be able to set up?
Thanks in advance
Scott

Scott,

No problem to achieve what you're looking for. When you design the reports, you'll need to set default values that are non-queried expressions. On the Report Parameters dialog box, for the End Date, set a default value of =Today . For the Start Date, use something like =Dateadd("d", -7, Today). Publish the report to the report server.

Now set up the subscription like you are currently doing. However, rather than entering hardcoded dates for the parameters, check the "Use Default" box. Since the default is dynamic based on "today", where today is the scheduled run date, you will get what you are looking for.

Hope this helps,

Scott Thornburg

GloriousRuins Right there with Babe Points: 730 More actions · Answer 2

It does indeed. I suspected it might have to be written into the report itself, but I was just curious as to whether or not it was something that could have been achieved from Report Manager, in the event that different people need to use different parameters in their subscriptions. But thanks for clarifying anyway, I can get on with things now!

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 3

Yes, the solution I provided does entail a change in the report itself to set the dynamic default dates.

If this is not possible, an alternative would be to build your own scheduled job that would run the report and export it to a file share. You then have control over the parameters without changing the report. This would be a lot more work, however, unless you could leverage the effort over many such reports.

Good luck to you.

Scott Thornburg

Jeffrey Reyes Valued Member Points: 73 More actions · Answer 4

Scott Thornburg (10/27/2007)
Scott B (10/26/2007)
Hi all,
I am setting up subscriptions for a number of reports I've created, and I've successfully managed to have them write to the fileshare folder. Some of the reports have date parameters (start and end date etc.), and I've got them working by entering the dates in the parameter values at the bottom of the subscription page. However, what if I want to have the reports run on a daily basis using different dates each day - for example, run using "today" as a parameter, or maybe "today" as the 'end date' and "today - 7 days" as the 'start date'? Is this something I'm going to be able to set up?
Thanks in advance
Scott
Scott,
No problem to achieve what you're looking for. When you design the reports, you'll need to set default values that are non-queried expressions. On the Report Parameters dialog box, for the End Date, set a default value of =Today . For the Start Date, use something like =Dateadd("d", -7, Today). Publish the report to the report server.
Now set up the subscription like you are currently doing. However, rather than entering hardcoded dates for the parameters, check the "Use Default" box. Since the default is dynamic based on "today", where today is the scheduled run date, you will get what you are looking for.
Hope this helps,
Scott Thornburg

I have tried making my start date parameter to =Today.But when i have previewed it,it dont have any value and it is greyed out and i cant click it.Have you tried that yourself.Can you tell me what i might be doing wrong?Thanks

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 5

Jeffrey,

I do this by creating a datetime parameter, leaving "Available values" blank, and setting a Non-queries default value to "=Today" (without the quotes). This is in SSRS 2005, and I think(but am not positive) that this also works with SSRS 2000.

Is this what you are doing?

Scott Thornburg

Chip Smithson-334408 SSC Enthusiast Points: 124 More actions · Answer 6

Scott,

I have found that creating Data-Driven subscriptions can give you alot more power as to how you can publish to a file share situation. That way you can create a query to define your date parameters as well as filenames that have alot more flexibilty.