November 14, 2011 at 6:46 am
Hi ,
I have @report_Start and @report_End params in my report and a bunch of other parameters which are dependent on these datetime params.
I am trying to set up a subscription to go thrice a day.
I did try scheduling every 8 hours starting at 4 am, I want to have @report_start , @report_end updated accordingly too, that is displayed on report. also the parameters updated accordingly, that are dependent on @report_Start and @report_End.
When I made @report_start, @report_end null in the description, one of the dependent parameter fails and I am not able to save the subscription.
any leads please?
Thanks
November 15, 2011 at 2:31 pm
This is probably happening because the values for those parameters are expected in the report.
My suggestion would be to create defaults for the parameters in the report with the necessary logic to do what you need. The subscriptions would not need to pass any parameters for the report then, and all should work fine.
Hope this helps.
November 16, 2011 at 6:37 am
Thanks for your reply.
I added defaults for @report_start and @report_end as for yesterday 24 hours 00:00 to 23:59, but even If i schedule it every 8 hours, the report would run with its defaults (00:00 to 23:59) and show the data that is captured between 00:00 to 23:59 yesterday and does NOT show the date-range in the emailed subscription-pdf-report
as Yesterday
00:00 to 8:00
8:00 to 16:00
16:00 to 23:59
any advice? thanks.
November 16, 2011 at 7:30 am
SQL_Nw (11/16/2011)
Thanks for your reply.I added defaults for @report_start and @report_end as for yesterday 24 hours 00:00 to 23:59, but even If i schedule it every 8 hours, the report would run with its defaults (00:00 to 23:59) and show the data that is captured between 00:00 to 23:59 yesterday and does NOT show the date-range in the emailed subscription-pdf-report
as Yesterday
00:00 to 8:00
8:00 to 16:00
16:00 to 23:59
any advice? thanks.
Yes...use a query for the defaults, as opposed to hard-coded values. Make use of the different date functions to ensure that the parameters get the correct values when the report runs through the schedule.
November 16, 2011 at 8:37 am
Do you mean I need to make changes in stored proc? I cant do that.
How can I do it in ssrs only. Any leads please..
November 16, 2011 at 8:44 am
Can you change the default parameter to take there values from a query?
Then have that query change the output based on the time of day.
Worse comes to worse, maybe you do a new report to set the parameter values and call that report instead.
November 16, 2011 at 8:45 am
SQL_Nw (11/16/2011)
Do you mean I need to make changes in stored proc? I cant do that.How can I do it in ssrs only. Any leads please..
No. Create a new dataset in your report. The query for that dataset should look something like this:
selectgetdate() as Report_Start_Date
,dateadd(hour,8,getdate()) as Report_End_Date
Use the values from this dataset for the default values of the parameters.
November 16, 2011 at 9:02 am
Just one more thing as I'm reviewing your initial posts again.
Have you tried to create a data-driven subscription? That will allow you to specify a query to return the values for the parameters without the need for defaults in the report.
My apologies if the initial suggestion was a little misleading.
November 16, 2011 at 10:00 am
A million thanks...Your solution /data set query looks great. I am using your dataset query and have put subscription for testing.
I am not aware about data driven subscriptions. It does sound great not having to put default dates. Would you please advise where to find more information about it and how to set it up.
Thanks all for your responses.
November 16, 2011 at 10:02 am
DDS requires enterprise version.
There's a way to fake that in standard but it's a pita.
November 16, 2011 at 10:02 am
Martin Schoombee (11/16/2011)
Just one more thing as I'm reviewing your initial posts again.Have you tried to create a data-driven subscription? That will allow you to specify a query to return the values for the parameters without the need for defaults in the report.
My apologies if the initial suggestion was a little misleading.
Sure...no problem.
Have a look at the following re data-driven subscriptions: http://msdn.microsoft.com/en-us/library/ms169673.aspx
Martin.
Edit: Didn't know that it was only Enterprise...thanks for that. To be honest I've never had to use DDS...
November 16, 2011 at 2:21 pm
Ninja's_RGR'us (11/16/2011)
DDS requires enterprise version.There's a way to fake that in standard but it's a pita.
Hi Ninja , I could not find data driven subscription tab in my report manager.
I am curious how you make it work in standard. any links?
thanks
November 16, 2011 at 2:36 pm
SQL_Nw (11/16/2011)
Ninja's_RGR'us (11/16/2011)
DDS requires enterprise version.There's a way to fake that in standard but it's a pita.
Hi Ninja , I could not find data driven subscription tab in my report manager.
I am curious how you make it work in standard. any links?
thanks
You can't, it's only with enterprise version.
The workaround is to entirely code it yourself. Not easy, but could be worth the 100K $ savings on a larger server ;-).
November 17, 2011 at 10:00 am
Ninja's_RGR'us (11/16/2011)
SQL_Nw (11/16/2011)
Ninja's_RGR'us (11/16/2011)
DDS requires enterprise version.There's a way to fake that in standard but it's a pita.
Hi Ninja , I could not find data driven subscription tab in my report manager.
I am curious how you make it work in standard. any links?
thanks
You can't, it's only with enterprise version.
The workaround is to entirely code it yourself. Not easy, but could be worth the 100K $ savings on a larger server ;-).
🙂 thanks
November 17, 2011 at 10:03 am
HTH.
PS You're avatar is not showing here. Unless it's a big red X.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply