Parameter in Command

[jbarbato]

Hi There! I am using crystal reports xi...

I am trying to put a parameter in my select statement in the command, but I keep getting an invalid character...

The multi-select parameter is called ?Delv_Status. It filters the field Status_Id. If the user enters nothing (blank) then all fields in the report should be returned...

select d.status_id
from deliverables d
where d.status_id = "" or
{?Delv_Status} = d.status_id

I am currently using the select expert... however, this is not the only parameter in the report. There are over 15 parameters, all with different structures, and lots of subreports. It would be much easier if I could put all of my selection criteria into the command.

Thanks!

- Jayme

 

[lbass]

One thing I notice is the double quotes. Try single quotes.

-LB

 

[jbarbato]

Sorry, instead of copying & pasting, i just typed it because it came from such a huge select statement... i made another mistake too. Here is correct as i have it:

select d.status_id
from deliverables d
where {?Delv_Status} = '' or
{?Delv_Status} = d.status_id

FYI - if i take out ({?Delv_Status} = '') or ({?Delv_Status} = d.status_id) i get the invalid character either way... Definately something is wrong with the way I am referencing the parameter. Am I even allowed to reference the parameter in the command?

 

[lbass]

You are supposed to create the parameter within the "Add Command" area. On the righthand part of the screen you can create the parameter, and then place the cursor where you want the parameter in the command and double click to add it to the command.

I don't have XI available to me right now, so can't add much more probably.

-LB

 

[jbarbato]

I have created the ?Delv_Status parameter on the right hand side of the screen in the "Parameter Fields" section of the report... Then what? Was I supposed to create the parameter in the database expert?

thanks...

 

[jbarbato]

In the Command statement, Crystal accepts this...

'{?Delv_Status}' = '' or '{?Delv_Status}' = d.status_id

but no data is returned! Anyone know what is going on here?

 

[lbass]

I don't know what you mean. You should be using "Add Command" as your datasource. The parameter creation area is on the right of that screen. Once created, you add it into your command.

-LB

 

[jbarbato]

ohh i see it now. I never even noticed that there - no one pointed it out, and i have been creating parameters in the field explorere. I will play with that - thanks!

 

[jbarbato]

Ok so it works except for one thing...

If I created a parameter in the parameter fields section of the field explorer, you type in the information as is...

Deliverable Status: A

But if I create it in the Command area, I have to input the information with quotes -

Deliverable Status: 'A'

Why would this be set up this way?!?!? We are completely bypassing Crystal's Enter Parameter Values form and passing in the parameters from another application...

Does this mean that I have to either create all parameters in the command section, or create all parameters in the parameter field section, and I can't have both options?

 

[lbass]

I don't know how or if passing parameters from another application works with "Add Command".

Also, as I said earlier, I do not have access to XI right now, so I can't test anything out, and I'm not sure about whether you can create parameters outside of the command area when using a command as a datasource. Somebody else will have to jump in.

-LB

 

[busbyla]

I don't have much to add in the way of a solution, but have a very similar problem using Crystal XI. I cannot add more than one parameter in the Command. I can only add one. Each of the two I need work on their own when I add them separately. But, when I try to include both of the parameters in my statement, only one shows up for me to enter, then I get an invalid character error on the other.

Here's the portion of my command (SQL):

and TERM_CODE = {?RegTerm}
and MAJR_CODE = {?MajorCode}

I'm working around this by picking the most effective parameter to run on the server through the command, and then entering the other in Crystal, so it will filter locally. It's a pain, and is not very efficient in this case!

 

[lbass]

What are the datatypes of your two parameters? You can use more than one parameter in a command. String parameters must be enclosed in '' as in '{?mystringparm}'. I have used date and string parameters in the same command. I have not yet been able to get a number parameter to work--not sure what I'm doing wrong.

-LB

 

[jbarbato]

I was able to get {?Delv_Status} to work in Crystal - The problem, however, was that we have been passing parameters in through an application that we built with Macromedia Flex - It works! But when we pass in the parameters, we have to "adjust" them from what the user input to what Crystal wants to see. So having to add quotes to it when it is in the command statement vs. no quotes when its not is simply not going to work b/c we cant switch it back & forth every time!

You can't use a multiselect parameter in the SQL command either - Also, I cant use a function that was established in our database. I am hitting a huge road block with this parameter thing at the worst time in my project - Our reports are too complicated to be running every parameter through Crystal, and I keep running into silly problems (like busbyla & lbass mentioned) as well that shouldn't be an issue that slow me down.

Has anyone found any good documentation about parameters in crystal? I have yet to find any decent explanation of how all this stuff works - I cannot continue taking over an hour to figure out how to get a simple parameter into the Command, then getting frustrated and sticking it in crystal, which makes the report run slow as heck.

thanks,
jayme