Multiselect Parameter in Command

[jbarbato]

Hi There! I am using Crystal Reports XI...

How do I define a multi select parameter in the Command select statement in the Database Expert?

Thanks!
- Jayme

 

[synapsevampire]

Unfortunately you don't.

If you convert the query to a View on the database (check with your dba), you can use conventional Crystal parameters which allow for this. It's a better idea anyway because it provides reusability by other tools.

-k

 

[BobSuruncle]

You could remove the parameter from the Command file.
Then create a MultiValue report parameter and use it in the Record Selection formula.
This will NOT be reflected in the SQL statement. Crystal will do the filtering of the result set LOCALLY.
This is NOT efficient, but will get you what you want.
Kai's suggestion to use a database View is a better one, but sometimes our hands are tied if we don't have access to the database or a cooperative DBA.


Bob Suruncle

 

[jbarbato]

i am already using a view...

are you suggesting that i place the parameter in the view on the database side?

i am currently using parameters created in crystal outside of the command file, but my complicated reports with subreports and dynamic grouping and such would run much more smoothly if i could place one line of code in the SQL, not 15 lines of code all over the place trying to select and suppress things. my simpler reports were fine, i could just place the code in the select expert and be done with it. my complicated reports, its not so simple.

 

[BobSuruncle]

If you're using a database view, why are you using a command file too?

If your view is doing all of the work, then you can create a parameter in Crystal (single value, multivalue, whatever)
and use that for selecting records.

Am I misunderstanding you, or is it that simple?


Bob Suruncle

 

[jbarbato]

One of the reasons i miss oracle reports - it gives you the ability to arrange groups no matter how you want...

Since i cannot do that with crystal, i end up with subreports, and data in headings of groups, and a big mess of how i want to filter everything out. i do not have any reports that only have details at the lowest group level. most often, there are details at every group level. subreports are sooo slow, so i try to avoid using them at all costs... off the subject, sorry.

if i printed out my views (which is built upon several other views, which are built on several other views, etc.) they would take up several thousands of lines of code... all i have in my command statement is:

select pprojectnumber,
ppackage,
ppackageid,
pparentpackageid,
ppackagetype,
ppackagedescription,
pparentindicator,
pdiscipline,
substr(pRP1Title, 1, 24) pRP1Title,
pRP1Date,
pRP1DateID,
substr(pRP2Title, 1, 24) pRP2Title,
pRP2Date,
pRP2DateID,
substr(pRP3Title, 1, 24) pRP3Title,
pRP3Date,
pRP3DateID,
substr(pRP4Title, 1, 24) pRP4Title,
pRP4Date,
pRP4DateID,

cpackage,
cpackageid,
cparentpackageid,
cpackagetype,
cpackagedescription,
cparentindicator,
cdiscipline,
substr(cRP1Title, 1, 24) cRP1Title,
cRP1Date,
cRP1DateID,
RP1Days,
substr(cRP2Title, 1, 24) cRP2Title,
cRP2Date,
cRP2DateID,
substr(cRP3Title, 1, 24) cRP3Title,
cRP3Date,
cRP3DateID,
RP3Days,
substr(cRP4Title, 1, 24) cRP4Title,
cRP4Date,
cRP4DateID,
RP4Days,
RP2Days



from pkg_tree_rpt_posn_ms_vc



I wish it were a simple report

 

[synapsevampire]

Instead of using a Command Object, select the View directly within Crystal's GUI and then you can use multiple discrete and range parameters within crystal, and it will pas them to the database.

You may need to turn on the File->Options->Database->and select Views under the Tables and Fields area.

Command Objects are a bad idea in general, they are handy if you cannot create database objects and need to pass SQL, but you don't need nor want them as you are already creating reusable database objects.

-k

 

[PNC]

How can you suggest using a View when a view can't have parameters?

And if you build the view without parameters and use the Select Expert of Crystal, the report will be slow because it has to retreive all the rows, then crystal will do the filtering.