[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

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.
0
sam15
Asked:
sam15
  • 7
  • 6
  • 3
1 Solution
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now