Dynamic Parameters 1

[mholbert]

I am building a CRXI report against a stored procedure on a sql2k db.

The stored procedure is quite simple, and has three input parameters. I want to build a dynamic list for the user to pick from to satisfy one of the parameters, but I cannot find a way to accomplish this.

I need the parameter to do a simple lookup on a database table, so the end user can select a provider name, and the parameter will pass the corresponding ID to the report.

I suspect this is quite simple, and I have just missed something. Thanks in advance for the help.

 

[racskelly]

Hi mholbert...

this is how you want to do dynamic lists lets say you have a table called SALES and it has fields for SALES.country, SALES.state, SALES.city... and you would like to give your users a parameter to narrow their search down by one or more of those fields... to do this create a new Parameter (if you don't know how to do a step...ask and I will show you) and name it Country. In the popup give your parameter a name..lets call it Dynamic_City, and chose Dynamic (instead of static)

- leave chose data source to New

- then click under the first value row where it says click here to add item and chose SALES.country
- then beneath it click to add an item and choose SALES.state
- then beneath that click to add an item and chose SALES.city

enter an appropriate prompt in the bottom area for each value...then to the far right of each value press "click here to add parameter" the system will create a parameter for each value... your parameters should look like:

?Dynamic City - Country
?Dynamic City - State
?Dynamic City - City

then click OK.

now back on your report make a select statement and choose your city field and say equal to {?Dynamic City - City}

now run your report it should ask you to choose a country, then chose from the states in those countries, then chose from the cities in those states all live from your database.

 

[byteias]

My example (SP) returns an Id, lastname and firstname, so I added a dynamic LOV using the lastname and I got a SQL error (after creating a select statement (myproc.lastname={?lastname})).
"Prompting failed.....table could not be found"
How are you going to select (or create) a LOV from stored procedure?
Thanks,
Ivan.