Passing a parameter with a wildcard\like value

[Former Member]

Hi,

I'm trying to write a formula which will allow a user to enter a value and the report will return all names starting with what's inputted.

If {?Company Name} = "Please Select" then {SS_Supplier_View.Company Name}

Else

Like ({?Company Name}*)

[DellSC]

I avoid using If statements in the Select Expert because they're frequently not passed to the database for use in the query and instead Crystal will filter that data in memory. This causes a lot more data than will be used to come back from the database and can significantly slow down your report. Also, I think you don't have the correct logic in the first part of your if statement - it will only get to the else if there is no value in the parameter.

I would rewrite the statement like this:

(
  not HasValue({?CompanyName}} OR
  {SS_Supplier_View.CompanyName} Like {?CompanyName} & '*'
)

Another option would be to create a formula that will append the '*' to the end of the parameter value and use that formula in the Select Expert instead of appending the '*' in line.

-Dell

[Former Member]

Thanks Dell,

Am I correct in thinking the select expert is the record filter function within SAP Crystal Reports For Enterprise? I've used Crystal Reports 8 previously which had the select expert function but this version of Crystal Reporting doesn't seem to have it?

My previous code was in a formula rather than in the record filter function. Also I seem to be having a problem where the results are in uppercase and I can only get results if I enter the parameter request in uppercase, lowercase returns an empty report?

Thanks

[Former Member]

What version of Crystal are you using?

In newer versions select filter is found in the experts tool bar -> look for filter symbol and then select record

or in main menu -> report->Selection Formulas - >Record

Ian

[DellSC]

Yes, that's correct. Because you didn't mention CRE in your question, I assumed you were using "classic" Crystal. 🙂

-Dell

[Former Member]

Thanks I have modified the Parameter in an attempt to work it out myself (and return all values) but I still cant get the wildcard to work the error is this value must be of the same type as the previous 'IF' clause

Current code

IF hasvalue ({?CompanyName}) then {?CompanyName}

else {SS_Supplier_View.Company Name} LIKE ({?CompanyName} & '*')

[abhilash_kumar]

Hi,

Please try this:

{SS_Supplier_View.Company Name} LIKE ({?Company Name} & '*')

-Abhilash