KB13318: How to use a security filter with a Freeform SQL report in MicroStrategy Developer 9.4.x - 10.x

Follow the steps below to associate a security filter with a user and use this with a Freeform SQL report in Strategy Developer 9.4.x - 10.x:
 

• Login to Strategy Developer as an administrator and create a new user with the User Manager, as shown below:
  

• Create a new security filter, as shown below:
  

• Open the user created in step 1 using the User Manager and go to the Security Filter tab. Choose the project for which the security filter is used and associate the user with the security filter, as shown below:
  

• To use this security filter with the Freeform SQL report, go to the Edit -> Insert Security Filter from the report menu and choose 'Insert the Security Filter'. Insert the security filter created in step 2, as shown below:
  

• Define the security filter. In this test case, the security filter is based out of the Year attribute which is defined off the Year_ID column from the LU_YEAR table, as shown below:
  

NOTE: In order for the SQL to be submitted correctly, there are two scenarios that must be taken into account when entering the text into the string field:
 

1. A WHERE clause exists. The string should be 'AND Year_ID'. The SQL would be generated as follows.
   select a11.year_ID,
   sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) WJXBFS1
   from YR_CATEGORY_SLS a11
   WHERE YEAR_ID > 1990
   AND YEAR_ID in (2000)
   group by a11.year_ID
   Without the AND in the string column of the security filter, the SQL would appear as shown below and would error out.
    
   select a11.year_ID,
   sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) WJXBFS1
   from YR_CATEGORY_SLS a11
   WHERE YEAR_ID > 1990
   YEAR_ID in (2000)
   group by a11.year_ID
   
2. No WHERE clause exists. The string should be 'WHERE Year_ID'. The SQL would be generated as follows.
   select a11.year_ID,
   sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) WJXBFS1
   from YR_CATEGORY_SLS a11
   WHERE YEAR_ID in (2000)
   group by a11.year_ID
   Without the WHERE in the string column of the security filter, the SQL would appear as shown below and would error out.
    
   select a11.year_ID,
   sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) WJXBFS1
   from YR_CATEGORY_SLS a11
   YEAR_ID in (2000)
   group by a11.year_ID