Creating a dynamic date parameter in a SQL Reporting Services subscription
-
What I need to do seems really easy, but I don't know how to do it and haven't been able to find the answer. :mad: I have a SQL reporting services report that takes a date parameter. I want to create a subscription to the report that will run early each morning and show the data for the day prior. Nothing fancy, just a Now - 1. Only Now - 1 doesn't work. Does anyone know if/how this can be done?
Mike Devenney
-
What I need to do seems really easy, but I don't know how to do it and haven't been able to find the answer. :mad: I have a SQL reporting services report that takes a date parameter. I want to create a subscription to the report that will run early each morning and show the data for the day prior. Nothing fancy, just a Now - 1. Only Now - 1 doesn't work. Does anyone know if/how this can be done?
Mike Devenney
-
Not sure about SSRS, but in SQL you would say GETDATE()-1. If both of those don't work, what's the error message (or any other problem description)?
The need to optimize rises from a bad design. My articles[^]
I'm familiar with the SQL command to do this, I wanted to know if there is a way to specify a dynamic parameter in an SSRS report. GETDATE()-1 would work perfectly, but the question is how to set that as the parameter?
Mike Devenney
-
I'm familiar with the SQL command to do this, I wanted to know if there is a way to specify a dynamic parameter in an SSRS report. GETDATE()-1 would work perfectly, but the question is how to set that as the parameter?
Mike Devenney
Where are you launching the report from? C#? Could you post the code that's causing the problem. I must admit that I haven't used SSRS very much, only client side invocation and from IIS so this may be beyond my knowledge...
The need to optimize rises from a bad design. My articles[^]
-
Where are you launching the report from? C#? Could you post the code that's causing the problem. I must admit that I haven't used SSRS very much, only client side invocation and from IIS so this may be beyond my knowledge...
The need to optimize rises from a bad design. My articles[^]
SSRS reports can be scheduled for delivery (called a subscription) through the web interface. When setting up a subscription you can specify the value you want passed to the report when it runs. I want to pass GETDATE()-1 but don't know how.
Mike Devenney
-
SSRS reports can be scheduled for delivery (called a subscription) through the web interface. When setting up a subscription you can specify the value you want passed to the report when it runs. I want to pass GETDATE()-1 but don't know how.
Mike Devenney
Okay, as I imagined, waaaayyyy beyond my knowledge, sorry! The only things that I found were these: - http://wills-blog.com/?p=111[^] - http://www.developer.com/db/article.php/3513996[^] - http://www.eggheadcafe.com/community/aspnet/13/10002869/ssrs-2005--errors-using.aspx[^] - http://blogs.msdn.com/eugenebykov/archive/2007/07/10/report-parameter-controls.aspx[^] I hope those will help you forward, Mika
The need to optimize rises from a bad design. My articles[^]
-
Okay, as I imagined, waaaayyyy beyond my knowledge, sorry! The only things that I found were these: - http://wills-blog.com/?p=111[^] - http://www.developer.com/db/article.php/3513996[^] - http://www.eggheadcafe.com/community/aspnet/13/10002869/ssrs-2005--errors-using.aspx[^] - http://blogs.msdn.com/eugenebykov/archive/2007/07/10/report-parameter-controls.aspx[^] I hope those will help you forward, Mika
The need to optimize rises from a bad design. My articles[^]
Please don't apologize! I've been banging my head against the wall trying to find a way to do this that didn't involved modifying the underlying stored procedure, which I now think I'll end up doing. I've asked DBA's, developers, project managers and anyone else who would continue to listen to the question past the words SQL Reporting Services but no one seems to know. Well, to be more accurate most of them knew, but they didn't have the answer I was looking for. ;) Thanks for your time, I'll hit the links you posted and see if someone out there is going to save me from some relatively minor stored proc updates. Now that I think about it I've expended more effort trying to find the easy answer than I would have if I'd just made the update and pushed it to production. *sigh* I guess the adventure is the journey, right?
Mike Devenney
-
Please don't apologize! I've been banging my head against the wall trying to find a way to do this that didn't involved modifying the underlying stored procedure, which I now think I'll end up doing. I've asked DBA's, developers, project managers and anyone else who would continue to listen to the question past the words SQL Reporting Services but no one seems to know. Well, to be more accurate most of them knew, but they didn't have the answer I was looking for. ;) Thanks for your time, I'll hit the links you posted and see if someone out there is going to save me from some relatively minor stored proc updates. Now that I think about it I've expended more effort trying to find the easy answer than I would have if I'd just made the update and pushed it to production. *sigh* I guess the adventure is the journey, right?
Mike Devenney
Mike Devenney wrote:
I guess the adventure is the journey, right?
You're absolutely right about that! But then again, don't we all know the answer already (check the link if not familiar): 42[^]. However, the ultimate question seems kind of... forgotten :) Anyhow, I really hope you get your original question solved! Mika
The need to optimize rises from a bad design. My articles[^]
-
What I need to do seems really easy, but I don't know how to do it and haven't been able to find the answer. :mad: I have a SQL reporting services report that takes a date parameter. I want to create a subscription to the report that will run early each morning and show the data for the day prior. Nothing fancy, just a Now - 1. Only Now - 1 doesn't work. Does anyone know if/how this can be done?
Mike Devenney
You can use the following expression: =DateAdd("d",-1,Today())
-
You can use the following expression: =DateAdd("d",-1,Today())
For parameters StartDate and EndDate build two new parameters StartDateDefault and EndDateDefault. Move these two 'Default' parameters up the parameter list so they are calculated before StartDate and EndDate. Point StartDate and EndDate to StartDateDefault and EndDateDefault respectively so that default values are picked up eg. by StartDate from StartDateDefault. For the StartDateDefault and EndDateDefault, make the 'available values' come from a data set using Sql such as... select dateadd(day, -1, datediff(d,0,getdate())) as Code, 'Yesterday' as Description union select dateadd(day, -0, datediff(d,0,getdate())) as Code, 'Today' as Description Set StartDateDefault and EndDateDefault to be hidden. Also give these two parameters default values themselves if required, which will be dates matching those in the Sql query data set, use non-queried default values to provide a date in the same format as the Sql query eg. =DateAdd("d",-1,today()) Now, when the report initially runs, StartDate gets its value from StartDateDefault, which has a default start date of =DateAdd("d",-1,today()) and when you go to create a subscription, the DefaultStartDate parameter is available to change, and the values you can select come from the Sql query/data set created earlier.
-
For parameters StartDate and EndDate build two new parameters StartDateDefault and EndDateDefault. Move these two 'Default' parameters up the parameter list so they are calculated before StartDate and EndDate. Point StartDate and EndDate to StartDateDefault and EndDateDefault respectively so that default values are picked up eg. by StartDate from StartDateDefault. For the StartDateDefault and EndDateDefault, make the 'available values' come from a data set using Sql such as... select dateadd(day, -1, datediff(d,0,getdate())) as Code, 'Yesterday' as Description union select dateadd(day, -0, datediff(d,0,getdate())) as Code, 'Today' as Description Set StartDateDefault and EndDateDefault to be hidden. Also give these two parameters default values themselves if required, which will be dates matching those in the Sql query data set, use non-queried default values to provide a date in the same format as the Sql query eg. =DateAdd("d",-1,today()) Now, when the report initially runs, StartDate gets its value from StartDateDefault, which has a default start date of =DateAdd("d",-1,today()) and when you go to create a subscription, the DefaultStartDate parameter is available to change, and the values you can select come from the Sql query/data set created earlier.
A simpler solution is to add a hidden boolean parameter to the report, called for example 'IsSubscription' with a default value of false and add the folowing expression to the StartDate and EndDate default values:
=IIf(Parameters!IsSubscription.Value, DateAdd("d",-1,Today()), Today())When running the report normally, the IsSubscription parameter is defaulted to false and the default date is today. When creating the subscription, the IsSubscription parameter must is set to true, thus setting the default value to today-1 = yesterday. This solution can be aplied to do all kinds of changes based on the IsSubscription parameter value and expresions for the rest of the paremeters based on its value. The IsSubscription parameter must be defined before any other parameter that uses its value (moved up in the parameter list). -
A simpler solution is to add a hidden boolean parameter to the report, called for example 'IsSubscription' with a default value of false and add the folowing expression to the StartDate and EndDate default values:
=IIf(Parameters!IsSubscription.Value, DateAdd("d",-1,Today()), Today())When running the report normally, the IsSubscription parameter is defaulted to false and the default date is today. When creating the subscription, the IsSubscription parameter must is set to true, thus setting the default value to today-1 = yesterday. This solution can be aplied to do all kinds of changes based on the IsSubscription parameter value and expresions for the rest of the paremeters based on its value. The IsSubscription parameter must be defined before any other parameter that uses its value (moved up in the parameter list).I have used the code snippet above and it works great. However, for my purposes I would like to revert to using a user entered date, if it is not being run by subscription. Any suggestions?
-
For parameters StartDate and EndDate build two new parameters StartDateDefault and EndDateDefault. Move these two 'Default' parameters up the parameter list so they are calculated before StartDate and EndDate. Point StartDate and EndDate to StartDateDefault and EndDateDefault respectively so that default values are picked up eg. by StartDate from StartDateDefault. For the StartDateDefault and EndDateDefault, make the 'available values' come from a data set using Sql such as... select dateadd(day, -1, datediff(d,0,getdate())) as Code, 'Yesterday' as Description union select dateadd(day, -0, datediff(d,0,getdate())) as Code, 'Today' as Description Set StartDateDefault and EndDateDefault to be hidden. Also give these two parameters default values themselves if required, which will be dates matching those in the Sql query data set, use non-queried default values to provide a date in the same format as the Sql query eg. =DateAdd("d",-1,today()) Now, when the report initially runs, StartDate gets its value from StartDateDefault, which has a default start date of =DateAdd("d",-1,today()) and when you go to create a subscription, the DefaultStartDate parameter is available to change, and the values you can select come from the Sql query/data set created earlier.