Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Setting function driven parameters in report manager


Setting function driven parameters in report manager

Author
Message
Griffster
Griffster
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 713
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?
toolman-352714
toolman-352714
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 712
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.

toolman
Numbers 6:24-26
Glen Sidelnikov
Glen Sidelnikov
Old Hand
Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)

Group: General Forum Members
Points: 387 Visits: 804
According to MS article, default value of the parameter on a parameters screen of Properties of a published report could only be a constant.
Griffster
Griffster
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 713
Thanks. Do you mean I can put the name of a stored procedure in the default value box for the date parameter?
Glen Sidelnikov
Glen Sidelnikov
Old Hand
Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)

Group: General Forum Members
Points: 387 Visits: 804
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.
toolman-352714
toolman-352714
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 712
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.

toolman
Numbers 6:24-26
Griffster
Griffster
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 713
OK, many thanks. Will have to be done in bids then.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search