Pass Multiple Parameters to SQL query in Drop down or Select Boxes

[Former Member]

Post Author: JasonW

CA Forum: Data Connectivity and SQL

Hello all- I am using CR XI and SQL server 2000.I have written a SQL statement and used that statement as a 'command' in the Database Expert, to pull data into a report. There are two issues I have that I need some help/advice on.-. The report needs 4 parameters. A, B, C, D.-. I can create Parameters in the Database Expert that will prompt me for the data when I refresh the report. However,-. I need to be able to select those parameters in drop down boxes based on data in the SQL database. The problem is that the parameter cannot pull the data without the criteria - so a drop down box seems impossible. OR is it? That is part of my question. The other part is - Even if it is possible, can Crystal do this somehow? NOTE: The query is selecting the data rather than Crystal - so Creating a Parameter field IN the report is futile. -. The other half of this problem is that users would like the ability to select multiple values from Field A and from Field B.

[Former Member]

Post Author: yangster

CA Forum: Data Connectivity and SQL

the list of values is restricted to 1000 values unless modify a registry key on your local machine (do not do this on the server as this blew up ours and the list of values stopped working all together)the problem with the list of values is it only grabs 1000 values at the lowest level of your dynamic list so the upper levels may be incomplete as welli've still got an open ticket that is unresolved dealing with the crappy list of values that are created when you try to add the word All to the list where the values below it do not generate properly

[Former Member]

Post Author: JasonW

CA Forum: Data Connectivity and SQL

I ended up using a stored procedure. This queries the proper data, and allows me to use Dynamic Parameters to make multiple selections on the data. (I query out a large portion of the data in the stored procedure, then pair it down using Dynamic Parameters in Crystal.) Here is the new problem this has created:My Dynamic Parameters are not pulling all the values from the query. Ex. One field in the query is a "directory" listing. It has 227 unique values in it. (It has multiple values, it is not constrained.) When I create a Dynamic Parameter off of this field, I only get 18 of the 227 as available selections in the drop down box. However, I can type in any of the 227 values into the field manually, I pull the proper data. So the data is there, its just not showing up in the drop down box. Anyone have any ideas on this?

[Former Member]

Post Author: pvierheilig

CA Forum: Data Connectivity and SQL

Would dynamic parameters work? They'll do what it seem you are requesting. Drop down boxes are possible - and I've not tried it myself - using 'web elements'. Doing a search on the Business Objects web site will lead you to it, but that may be much more complicated...and not fit your environment either (not stated, so just offered this as an option).