KB31904: How to create a prompt that lists elements existing in a fact table within MicroStrategy 9.4.1 and 10.x.

SYMPTOM:
A report designer may need to define a prompt that lists elements exists in a specific fact table. The following is an example to approach this requirement using the Strategy Tutorial project.
 
STEPS TO REPRODUCE:
Create a prompt that list all the Category attribute elements exist in YR_CATEGORY_SLS table.

• Within the Tutorial Project, create a new set qualification filter and save as “Categories from YR_CATEGORY_SLS”.
  
• Type: Relationship
• Output Level: Set of Category
• Relate By: YR_CATEGORY_SLS
  

• Create a new attribute qualification filter and select Category as the attribute.
• In the filter editor, define a prompt to list Category elements, and use the filter created in step 1 to reduce the number of elements.
  

• Save it as “Categories filtered by YR_CATEGORY_SLS” and close the filter editor.
  This is the prompt filter that list Category elements from YR_CATEGORY_SLS.
  

• When a report with the following definition is executed, the prompt doesn’t display elements that exist only in LU_CATEGORY.
  

•  
  
  Here is a SQL trace for warehouse when rendering prompt elements. By joining YR_CATEGORY_SLS, an element exists only in lookup table gets excluded.
  
   
  
  

  
  select  count(a21.CATEGORY_ID)  WJXBFS1
  from    LU_CATEGORY a21
  where   ((a21.CATEGORY_ID)
   in (select r11.CATEGORY_ID
      from    YR_CATEGORY_SLS r11))
         
  select  a11.CATEGORY_ID  CATEGORY_ID,
      a11.CATEGORY_DESC  CATEGORY_DESC0
  from    LU_CATEGORY a11
  where   ((a11.CATEGORY_ID)
   in (select r11.CATEGORY_ID
      from    YR_CATEGORY_SLS r11))
  order by        1 asc