report_dynamic_parameter

I have crystal report in business object xi delveloper.
I am trying to change a static parameter to dynamic sql so it looks up stock items from a lookup table.
when i tried that, I only saw the tables in the query avaiable. is not there a way to get a field from separate lookup table not in the query.
sam15Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tommy11bCommented:
On the development end you can create a typed dataset that fits your needs and fill it with whatever data you want. You can then create a crystal report based on your typed dataset. When it comes time for the report, just pass it the filled dataset thats already filtered and only contains the values you want to display.
0
sam15Author Commented:
i do not understand. you mean static dataset defined for the parameter or creating another crystal report just for the data that has dynamic sql query.

I already have the report. all i need is change the field from statis to dynamic (select stock_item from lookup_table).
0
Tommy11bCommented:
Yes I was refering to creating a new report and passing it the filled dataset, makes things easier since you know exactly what is being passed to the report plus theres no need for the report to even have to connect to the database, makes it much faster. Anyway do you have the value for the parameter to pass to the report or does the report itself need to query the database to get the value? If you know the value you can simply pass it from the frontend.

rpt.SetParameterValue("ParamName", Value)

Or am I misunderstanding what you are asking?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

sam15Author Commented:
I think you misunderstood me.

I have a crystal report with one parameter "p_stock_number". I define all the values in the parameter by typing them. When user runs report he seems a dropdown list and he selectes the stock item and then run the report for that stock number.

I want to remove the static values for the paramter and make the field dynamic based on one SQL statement from a table. WHen i try to do that i ant see the lookup table i want to select.

kind of strange. it does not make sense to create another report for this dynamic lookup field.
0
Tommy11bCommented:
No I wouldnt change it for a single field, I was just talking more in general of an easier & more flexible way to design reports in the first place.

On a windows form a user makes a selection from a combobox.
The selected value is passed as a parameter to the report
(rpt.SetParameterValue("p_stock_number", ComboBox1.SelectedValue.ToString))
The report then uses the parameter in you where clause for your query.
0
sam15Author Commented:
parameter already exists in crystal. it is not in HTML.

Think i have a p_state and i types all the states in this parameter.
now, i just want the values looked up from a table.

look under online help wiht business objects developer tool ,"creating a parameter with a dynamic prompt" you will know what i am trying to do.
0
mlmccCommented:
What version of Crystal are you using?

mlmcc
0
sam15Author Commented:
crystal reports developer xi
0
mlmccCommented:
Which tool is Business Objects XI Developer?  I don't see that in the list of SAP/BO products.

mlmcc
0
sam15Author Commented:
the server is business objects XI server. Just to host the reports.
the developer tool is crystal reports developer xi
0
mlmccCommented:
I believe if you add the table or you can add a COMMAND to the report then it will be available to the parameter query

mlmcc
0
sam15Author Commented:
I tired adding the table to the query but the list was not complete. Not all the parts showed up - kind of strange.
I did not udnerstand what you mean by adding a command. Do you mean change the  report for wizard based to command based (write the query manually). would that make a difference.

Kind of strange. i though you can have a parameter with its own sql query separate from the report query.
0
mlmccCommented:
Not to change the query but you could use a command as the source for the parameter list.

Do you have more than 1000 values in the parameter list?

mlmcc
0
sam15Author Commented:
no i think around 500. is there a limtation on the number of values.
how do you use a command for the source of a prameter list.
0
mlmccCommented:
There is.  Default is 1000.  You can make a registry change to set a higher number.

mlmcc
0
mlmccCommented:
To use a command, I think you just add it to the report through the DATABASE EXPERT
Then you can assign it to the parameter

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.