KB6893: How to qualify on an element whose name contains a symbol usually interpreted as a wildcard in MicroStrategy Developer 9.x

 
In Strategy Developer 9.x, when users need to filter on an attribute element or any other object whose name contains characters such as *, %, etc. qualifying on that character will be interpreted as a wildcard by the Strategy SQL Engine and all the data will be returned as shown below:

 
The asterisk character (*) is considered a wildcard when the SQL is generated:
select a12.Year_Id Year_Id,
  a11.Category_Id Category_Id
into #ZZT5901002JOL000
from   LU_CATEGORY a11
  cross join LU_YEAR a12
where   a11.Category_Desc like '%%'
select pa1.Category_Id Category_Id,
  max(a12.Category_Desc) Category_Desc,
  sum(a11.Tot_Dollar_Sales) DOLLARSALES
from   #ZZT5901002JOL000 pa1
  left outer join YR_CATEGORY_SLS a11
    on (pa1.Category_Id = a11.Category_Id and
  pa1.Year_Id = a11.Year_Id)
    join LU_CATEGORY a12
      on (pa1.Category_Id = a12.Category_Id)
group by   pa1.Category_Id
drop table #ZZT5901002JOL000
 
To prevent this from happening:

• Create a new filter selecting the 'Add Advanced Qualification' option:
  

•  
  If the option 'Advanced Qualification' is not available please follow the steps described in the Strategy Knowledge Base document KB4000-7X0-0247.
   
   
  
• Select the option 'Custom Expression' and enter the expression below in the 'Custom Expression' field:
  

• ApplyComparison ("#0 like '*%'", Category@DESC)
   
  Users will place the name of the attribute on which they wish to qualify in place of 'Category'.
   
   
  
• Save and close the filter.
• Use the filter on a report.
   
  The filter works now as expected:
  

• select   a12.Year_Id Year_Id,
    a11.Category_Id Category_Id
  into #ZZT5900003OOL000
  from   LU_CATEGORY a11
    cross join LU_YEAR a12
  where   a11.Category_Desc like '*%'
  
  select pa1.Year_Id Year_Id,
    pa1.Category_Id Category_Id,
    a12.Category_Desc Category_Desc,
    a11.Tot_Dollar_Sales DOLLARSALES
  from   #ZZT5900003OOL000 pa1
    left outer join YR_CATEGORY_SLS a11
      on (pa1.Category_Id = a11.Category_Id and
    pa1.Year_Id = a11.Year_Id)
    join LU_CATEGORY a12
      on (pa1.Category_Id = a12.Category_Id)
  
  drop table #ZZT5900003OOL000