Auto Update of parameter 1

[macvicar]

I have a report in SSRS 2005 that currently requires the user to type
in a parameter value in order to return the data. I have a sp that
runs to create the report. I'd like to change it so the parameter
value is automatically populated from the last record that has been
inserted into a particular table. I have added a new dataset to the
report and have added the query to get the last record from my table.
I've set auto-refresh on and this seems to work. However, the sp is
not running as the report refreshes. Only the text box with my new
query updates.
Is it possible to do this ?

Thanks


Jeff

 

[xlbo]

You need to:

a: Assign the dtaa set to the available values for the parameter

b: Set the "Default Value" for the parameter to be the result of your new SP

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[macvicar]

I've added the new data set. The parameter I had I've changed so that the available values are from a query based on the new dataset. The default values also are set to a query from the new dataset. The first time I preview the report it works. But when the report auto-refreshes, the parameter values do not update automatically and no new data is in the report. The only way to get the latest data in the report is to click "view report", which then changes the parameter box to show default value. Then if I drop that down, the latest selection is there which I can pick, click view report again and the latest data is in the report. I'm trying to get it so that when the report auto-refreshes, the latest info is there without any user input.

Thanks

 

[xlbo]

Where are you previewing the report?

It may be that you need to set up the parameters in the report manager as the setup for them is set at 1st deploy and do not change with subsequent deploys - check the parameter settings in the report manager area...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[macvicar]

I was previewing the report in VS. But when I deploy, I get an error when it refreshes saying the default value or value provided is not valid.

Jeff

 

[xlbo]

Have a look at the parameter options in the report manager then - that would seem to be where your issue is

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[macvicar]

How should the parameter be set in RM ? It shows it is there. Has Default is checked and Prompt User is checked. When I run the report for the first time, it displays the correct info. However, when the report refreshes, it says, "Default value or value provided for the report parameter 'CSNID' is not a valid value.

Thanks

Jeff

 

[xlbo]

Might be a little simple but can you check that when you run the query that populates the paramter, it returns the data you would expect both the 1st time and the 2nd time (run in Query Analyser / Mgmt Studio rather than directly in RS)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[macvicar]

When I run the query in Mgmt Studio, it returns the correct data every time. It's just seems that the new data doesn't get populated into the parameter when the report refreshes.

Thanks

 

[macvicar]

I figured it out. Instead of using a parameter, I added the query I was using for the parameter as a nested Select statement within the query I was using in my sp. Thanks for your suggestions.
Jeff

 

[xlbo]

Good to hear you got it sorted - sorry I couldn't help more - work kinda got in the way!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question