KB47226: How to use ApplyComparison to insert "like" function in text prompts in MicroStrategy Analytics Developer

When using the "like" function, the symbol "%" is usually used in the syntax. For example, the syntax "Region@DESC like '%South%'" will return TRUE if Region is "Mid Southern", "SouthEast", "South" and so on. However, when inserting the "like" function in a text prompt, the above syntax cannot be directly used or it will generate incorrect SQL and the report execution will fail. The following example describes this in detail:
 
EXAMPLE:

• Create a filter using the ApplyComparison expression with "like" function embedded.
  

•  
  

  
  ApplyComparison ("Upper (#0) like Upper (%#1%)", Region@DESC, ?[Type in a Region Name])

  
   
  
• Create a report with Region attribute in the row, and add the above filter. Run the report and type in "east" as the text prompt answer. The report failed to execute. The SQL view shows that it’s using an incorrect SQL code:
   
  Upper (a13.[Region_Name]) like Upper (%'east'%)
  
  That generates the following error message:
   
  
  

  
  Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [42000:-3100: on SQLHANDLE] [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression 'a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID] and
   a12.[REGION_ID] = a13.[REGION_ID]
   and Upper (a13.[REGION_NAME]) like Upper (%'east'%)'. Connection String: DSN=Strategy_Tutorial_Data;DBQ=C:\Program Files\Strategy\Tutorial Reporting\TUTORIAL_DATA_7200.MDB;DRIVERID=25;FIL=MS Access;MAXBUFFERSIZE=8192;PAGETIMEOUT=5;UID=admin;...

  
  
   
  
• To solve this problem, the ApplyComparison statement in the filter definition should be modified to be:
  

•  
  

  
  ApplyComparison ("Upper (#0) like Upper (#1)", Region@DESC, ?[Type in a Region Name])

  
   
  And instead the user can type in "%east%" when answering the text prompt. By this way, the report is successfully executed:
  

•  
  Also in the SQL view, it’s found that the correct SQL statement has been used:
   
  

  
  Upper (a13.[REGION_NAM]) like Upper ('%east%')