Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Setting function driven parameters in report manager Expand / Collapse
Author
Message
Posted Wednesday, October 1, 2008 6:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:18 AM
Points: 247, Visits: 698
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?
Post #579007
Posted Wednesday, October 1, 2008 8:02 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 1:11 PM
Points: 138, Visits: 698
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
Post #579073
Posted Wednesday, October 1, 2008 8:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 12:19 PM
Points: 371, Visits: 797
According to MS article, default value of the parameter on a parameters screen of Properties of a published report could only be a constant.
Post #579078
Posted Wednesday, October 1, 2008 8:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:18 AM
Points: 247, Visits: 698
Thanks. Do you mean I can put the name of a stored procedure in the default value box for the date parameter?
Post #579083
Posted Wednesday, October 1, 2008 8:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 12:19 PM
Points: 371, Visits: 797
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.
Post #579117
Posted Wednesday, October 1, 2008 8:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 1:11 PM
Points: 138, Visits: 698
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
Post #579118
Posted Wednesday, October 1, 2008 9:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:18 AM
Points: 247, Visits: 698
OK, many thanks. Will have to be done in bids then.
Post #579146
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse