Selecting multiple values for a parameter created in command object 1

[senamuna]

I want to select multiple values for selection criteria. I am using command object in CR XI. Database is Oracle 9i.

The parameters created at command object are visible in Field Explorer. But the "Allow multiple values" option is grayed out.

I cannot create the parameter in Field Explorer since then it is not incorporated in to the SQL passed to the database; and that will return millions of records to be filtered locally.

Thanks for any help.

 

[kenhamady]

Unfortunately, this is one of the limitations of using a command. If you could create the equivalent of this command using tables in Crystal (so that CR is generating the SQL) then you could get your multi-value parameter directly into the SQL. Or course you have less control over the SQL once you let CR generate it.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[senamuna]

Thanks Ken,

I am using UNION clause in the command object to access records from 6 identical databases. Therefore, I cannot use tables on Crystal side.

 

[lbass]

Turkbear pointed out a way to do this, which is a little awkward for the user, but works. In the command, assuming the parameter is a string, you would add the parameter with no quotes around it (unlike what you would ordinarily do) and using the in operator, like this:

where table.`field` in {?parm}

Then the user would enter a single parameter selection exactly like this, including the parens and quotes:

('value1','value2','value3')

-LB

 

[kenhamady]

Lbass,

Do all databases support the IN operator in SQL statements?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[lbass]

Ken,

I have no idea. I know it works for Oracle and the Xtreme Database (Access).

-LB

 

[senamuna]

Thanks to all who tried to help me. I figured out how to achieve what I wanted.

1. Use "all read" login. Create SEPERATE reports for each database picking tables in Crystal and having multiple value parameter. Suppress all except details section.

2. Create a main report with "all read" login and a multiple value parameter. No tables are selected.

3. Insert the individual reports as sub reports into sub sections of the header of the main report, linking each parameter to the main report parameter.

4. Create a seperate sub section as the with field titles and put it at the top.

5. Suppress blank subreport from main report.

The only draw back is that I cannot print the field titles in pages 2 onwards.

 

[synapsevampire]

If you create the UNION in a View all of these problems go away.

-k

 

[senamuna]

Hi Synapsevampire,

Sure. That's the shortest solution. Unfortunately, I only have read-only rights to the database.

 

[synapsevampire]

Always astonishes me how inefficient companies can be, you're going to fire multiple queries and code around these issues all because they don't want to run a Create View script...

-k

 

[kenhamady]

If you put the field titles in the page header of the main report, and then put the subreports in report footers (instead of headers), you should get field titles on every page.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/