October 1, 2008 at 6:35 am
I need to put in a value in a certain parameter for a report I've setup which is linked from a more generic report. I need to set up a subscription to run the report and send to a client on the 1st of each month. The report has a date parameter in it which runs the report for the last month's worth of data. Is there a way of setting the date parameter value in the parameters tab in report manager to contain a date one month prior to the report execution date please?
October 1, 2008 at 8:02 am
Martin,
There is not a way to do what you want in SSRS 2000 or 2005 (not sure on 2008). You'll need to create a second version of your report with the date formula/expression as a default parameter value. Use that version for your subscription and the original for on demand reporting.
Sometimes it's easier to create a SQL stored proc that generates the desired date and reference that as your default. Added benefit of this is you can reuse the SP whenever you need this type of parameter value in future reports.
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
October 1, 2008 at 8:08 am
According to MS article, default value of the parameter on a parameters screen of Properties of a published report could only be a constant.
October 1, 2008 at 8:15 am
Thanks. Do you mean I can put the name of a stored procedure in the default value box for the date parameter?
October 1, 2008 at 8:45 am
Webopedia definition:
In programming, a constant is a value that never changes. The other type of values that programs use is variables, symbols that can represent different values throughout the course of a program.
A constant can be
# a number, like 25 or 3.6
# a character, like a or $
# a character string, like "this is a string"
No, you can't use expression or stored procedure call in report parameters page. You can only use this functionality in report designer where you are defining parameters.
October 1, 2008 at 8:47 am
martin.griffiths (10/1/2008)
Thanks. Do you mean I can put the name of a stored procedure in the default value box for the date parameter?
Never tried that but I don't think it would work. How would it know what Datasource to look in to find the proc?
What I usually do is create a new stored procedure dataset that uses the date proc. Then on the 'Report parameters' dialog, select the 'From Query' radio button under 'Default Values:' - Pick the date proc dataset from the 'Dataset' dropdown and then select the correct Value from the 'Value field' dropdown. Click OK and you should be good to go.
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
October 1, 2008 at 9:12 am
OK, many thanks. Will have to be done in bids then.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply