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

[BeeBear2]

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

 

[IanWaterman]

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

 

[lbass]

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

 

[BeeBear2]

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

 

[lbass]

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

 

[hilfy]

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-when-using-commands-with-crystal-reports/

for more information.

-Dell

Senior Manager, Data & Analytics
Protiviti

http://www.protiviti.com

 

[BeeBear2]

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 [highlight #CC0000]IN[/highlight] ‘{?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

 

[lbass]

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

 

[BeeBear2]

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))

 

[BeeBear2]

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.

 

[BeeBear2]

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.