KB14752: Freeform SQL Reports have invalid SQL when run for a user with multiple security filters assigned in MicroStrategy Developer

SYMPTOM:
Incorrect SQL could be generated for a user with multiple security filters assigned on a project. This would be noticed only when the user attempts to execute a Freeform SQL report which incorporates a Security Filter condition that would apply to the user. For the steps on how to add a condition defined by a security filter, refer to the following Strategy Knowledge Base technical note:
KB13318 - How to use a security filter with a Freeform SQL report in Strategy Developer 9.4.x to 10.x
 
STEPS TO REPRODUCE:
The following steps indicate how this issue can be reproduced with the Strategy Tutorial project:

• Create 'Test' belonging to 'Test User Group'.
• Assign a security filter as year in ('2015') to Test, and year in ('2014') security filter to User Group, as shown below:
  

• Create a FreeformSQL with the following SQL:
  

•  
  Select Year_ID from LU_YEAR
  where Year_ID > 2000
  (security filter)
  
• And a Security Filter defined as follows
  

• Execute the report to view the SQL:
  Select Year_ID from LU_YEAR
  where Year_ID > 2000
  (and Year_ID in (2015)
  or and Year_ID in (2014))
  
• Notice that where 'Year_ID > 2000' and Year_ID=2015, 'or and Year_ID in 2014' is generated.
   
  A related and similar problem occurs if the SQL using the Security filter is defined as follows:
   
  Select Year_ID from LU_YEAR
  where Year_ID > 2000
  and (security filter)
  
• and the Security Filter does not contain the "and" criteria. In this case the user with multiple Security filters will see the correct SQL, however a user with no Security filter will see the following SQL:
   
  Select Year_ID from LU_YEAR
  where Year_ID > 2000
  and
   
  

CAUSE:
This behaviour is by design. Please see the steps explained in the "Action" section below that explain how the correct SQL can be generated to handle the different cases.
 
ACTION:
The correct position for the replacement string should be in the text box at the bottom of the Security Filter dialog box. This is the replacement string that will take effect if the security filter is applied, and will be removed from the SQL if the user does not have a Security filter for the project.
 

 
With this modification in place users should see the correct SQL as follows:
 
select a11.YEAR_ID YEAR_ID
from LU_YEAR a11
where a11.YEAR_ID > 2000
AND (a11.YEAR_ID = 2015
or a11.YEAR_ID in (2014))
Article Reference Number: KB99008