1537356 - How can I find all instances of a word in a memo field regardless of capitalization of characters in some words?

SAP Knowledge Base Article - Public

1537356 - How can I find all instances of a word in a memo field regardless of capitalization of characters in some words?

Symptom

When using a parameter in a record selection formula to search a memo field you may not be able to find all instances of a word when viewing the report in the Crystal Reports viewer at runtime in a Visual Studios .NET application that uses Crystal Reports Basic. If the word you are trying to find does not match the case of the word entered in the parameter field then those records will not be returned. For example, entering the word "apple" will not return records that contain the word "Apple" because they do not have a capitalization match.

Environment

  • Crystal Reports Basic with Visual Studios 2008.

Reproducing the Issue

  1. In Visual Studios 2008 application create a Crystal Report Basic report that returns data from a memo field.
  2. Create a string parameter for the report.
  3. Create a record selection formula that uses the memo field and the string parameter to find the parameter value in the memo field.
    • e.g. {?<string parameter>} in {<database.memo_field>}
  4. Bind the report to the Crystal Report Viewer control in the .NET application.
  5. Running the application and viewing the report will bring up a parameter selection prompt. Enter a string value using lower case letters.
  6. The records returned will only include those that match the case of the string value entered into the parameter prompt.
    • e.g. Searching for "apple" will not return any records that contain "Apple".

Resolution

To find all instances of a word regardless of capitalization of any words you can adjust the record selection formula to force all words to upper case for the record search.

For example, adjust the above record selection formula from

{?<string parameter>} in {<database.memo_field>}

to

ucase({?<string parameter>}) in ucase({<database.memo_field>})

This change now brings back all instances of the word you are trying to find.

Keywords

crystal, syntax, ucase, upper, case, sensitivity, memo, string, parameter, search , KBA , BI-DEV , Business Intelligence Software Development Kits (SDKs) , How To

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2