×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

(OP)
Hi, I know that you can't create a multi value paramter in the Crystal Command, but is there a way where you can pass the result of a parameter selection
EG create those parameters in Crystal not in Command and create a result formula similar to Join{?param}',') to create a string to use in an IN clause in the SQL.

I had to replace my standard table join way, with a select statement in SQL due to poor report performance, and now I have parameter issues.
I had a look at this FAQ (http://www.tek-tips.com/faqs.cfm?fid=6779) but It requires the Main report to house the parameters and pass to a Subreport where all the report action happened. I cant do that as I need a drill-down level below this.

Also, I need the option of Dynamic Cascading parameters aswell, with the 'All' option included.

I am using Crystal 2016 on a SQL Database.

Any help would be greatly appreciated.

Thanks

BeeBear

RE: Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

Create the Parameter you want in the main command dialog box and join to SQL as required.
create another command which contains the info for the parameter(this can include both value and description field)
Do not join the new command to existing command, CR will complain just ignore.

In Parameter set up window select the Command Parameter, convert to Dynamic and set up to use values in your new command, you can set up so User only sees description whilst parameter passes value to SQL.

Ian

RE: Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

You absolutely can create multi-value parameters in a command. You have to reference them differently if they are strings, by omitting the single quotes around the parameter and using ‘in’. Once you have created the parameter within the command screen, you can add a pick list in the main report and include the ‘All’ option.

I don’t know of a way to create a dynamic cascading parameter within a command, however. You could, however, create a second parameter and instruct users to select options from that parameter that align with the first parameter.

-LB

RE: Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

(OP)

Thanks for your help everyone.
Ive got it working for a single parameter, but not for the multivalue parameter.

What I have done is:
1. within Crystal Database Expert I have added the summary table RegionPostcodeMapping as a standalone table. Not linked to the command
2. I then built a parameter in Crystal called Region, and populated it with the Region LOV, plus 'All'.
3. In the command, added another STRING parameter also called Region, ticked the multi Value box, and put it in the WHERE clause of the Command:
Where ({?Region} = 'All' or Divison in {?Region})
Division is the field name holding the Region. I bracketed the whole thing since there will be other parts to the Where clause.

My problem is, it comes up with an error if I select more than one Region. Works for 'All' and works for a single Region.
ERROR MESSAGE IS:
An expression of non-boolean type specifiied in a content where a condition is expected near ',' SQL State 42000

What am I doing wrong?
Once I can get this Multivalue happening the rest as they say will be easy!

PS - I can create the dynamic cascading parameter the same was as Region - its just the code in the Where clause for when multi values are selected.

Thanks in advance

RE: Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

You should be using the command as the basis for your entire report. Not sure why you are trying to use both a command and a separate table.

You should ONLY be creating the parameter within the command. Once you have done this, you will see the parameter listed in the main report and you can then open it and add the list of values, including ‘All’.

-LB

RE: Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

Because this type of question comes up frequently, I wrote a blog post that has the details for how to do this. See https://blogs.sap.com/2015/04/01/best-practices-wh... for more information.

-Dell

Senior Manager, Data & Analytics
Protiviti
www.protiviti.com

RE: Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

(OP)
Hi LB - Unfortunately for a dynamic cascading parameter you have to build the parameter from a table to get the cascading values correct. I have managed to do this, my issue is purely with the code in the Command not working in the WHERE clause.

Hi Dell. I have seen your blog about this and tried to apply what you said in Parameters #2: Where (‘{?String Param}’ = ‘*All’ or MyTable.StringField = ‘{?String Param}’)


Changed to Where (‘{?String Param}’ = ‘*All’ or MyTable.StringField IN ‘{?String Param}’) as its a multivalue param, but it still wont work.
My exact versions of the entire WHERE Clause tried are:
Where PaymentDate BETWEEN {? StartDate} and {?EndDate} and ('{?Region}' = 'All' or Division in ('{?Region}'))
Where PaymentDate BETWEEN {? StartDate} and {?EndDate} and ('{?Region}' = 'All' or Division = ('{?Region}'))
Where PaymentDate BETWEEN {? StartDate} and {?EndDate} and ('{?Region}' = 'All' or Division = '{?Region}')
Where PaymentDate BETWEEN {? StartDate} and {?EndDate} and ('{?Region}' = 'All' or Division IN '{?Region}')
Where PaymentDate BETWEEN {? StartDate} and {?EndDate} and ('{?Region}' = 'All' or Division in ({?Region}))

I get the same error message whether I pick 'all or a region - Incorrect Syntax near 'Barwon' (the region I picked)

the original WHERE was
Where PaymentDate BETWEEN {? StartDate} and {?EndDate} and ({?Region} = 'All' or Division in ({?Region}))
this works for a single parameter value selection, but not for a multivalue (Error is
An expression of non-boolean type specified in a context where a condition is expected, near ','


Hope you can help because this is the only outstanding issue I have to publish this report, and I really cant work it out.
Let me know if you want the whole command statement

RE: Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

You can drilldown within subreports—you just can’t create a subreport within a subreport. I think you will need to use the subreport method if you feel the cascading parameter is necessary.

-LB

RE: Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

(OP)
Just to add another comment - I researched the error message at the end of my last post, and found that it may be to do with the '= All' section because the command didnt like it when I added more than one param value while using an '='

The fix on another page said to do this, but that doesn't help either (works for single use):
AND (SalespersonCode IN (@Salesperson) OR 'ALL' IN (@Salesperson))

RE: Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

(OP)
Cascading parameter is not a problem lbass - the only issue is my WHERE clause not working for a multi-value selection on a string parameter.

RE: Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

(OP)
Problem solved!!
After much fiddling around with adding and removing single quotes and brackets and rearranging things - I had too many brackets around the entire result:
Once I Removed the brackets around the param value after the IN, it worked

Where PaymentDate BETWEEN {? StartDate} and {?EndDate} AND (Division IN {?Region} OR 'ALL' IN {?Region})

Works perfectly for everything, and I can use this on the cascading dynamic parameter too. smile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close