KB20832: How to create a case insensitive prompt against an Oracle Warehouse in MicroStrategy 9.4.1 and 10.x

SYMPTOM:
Users may run a report consisting of the Customer attribute and the Cost metric with a Customer Last Name prompt in the Report Filter. Since Oracle is a case sensitive database, users who answer the prompt with 'SMITH' return the Cost for all the Smiths in the database. However, users who answer the prompt with 'Smith' receive a message in the report window stating: :
'No Data Returned'
ACTION:
To create a case insensitive prompt, execute the following steps in the Strategy Tutorial project:
 

• Create a new prompt. Select Value as the prompt type and click on Next.
  

• Select Text and click on Next in the window that appears.
  

• Go to the end of the Prompt Generation Wizard and save the prompt as Customer.
   
• Create a new filter by selecting 'Add Advanced Qualification' and click on OK.
  

• Check the 'Custom Expression' option and add the following in the Custom Expression box :
  ApplyComparison ("upper(#0)=upper(#1)", Customer@, ?Customer)
  Note:The SQL query mentioned in this document is specific for Oracle Warehouse and provided as simply an example. If SQL is required for another database platform, users need to contact their database administrator to do so.
  

•  
  To explain this syntax, UPPER is the Oracle function that is used to translate the prompt answer into upper case. 'Customer' refers to the name of the attribute and 'Last_Name' refers to the Attribute Form within the customer attribute.
   
  
• Validate the expression and save it as 'Customer'.
   
• Create a report with the attribute 'Customer', the metric 'Dollar Sales' and the filter 'Customer'. Data is now returned when the prompt answer is 'Smith'.
  

 
 
20832