Apply Security filter in MicroStrategy FreeForm SQL report

As we all know that the Freeform SQL or custom SQL  functionality adds great flexibility to user if they can implement for reporting.

MicroStrategy provides us the flexibility to use custom SQL to build the report directly. Traditionally, you use the MicroStrategy Engine to generate SQL to run against one specific relational database to get desired report. In addition to generating reports in the traditional way, you can also use your own customized SQL statements to generate reports from operational systems included in a MicroStrategy project . This capability could save your work time since you do not need to place the data into a data mart or data warehouse first.

You can use single pass as well as multiple pass query along with filters/some prompts etc.

But when it comes to security , you always want to use this report similar to standard report for different users . Since you are placing the security filter based on level of access in data is required for business users.

Normally FF(FreeForm) report do not apply the security filter on reports.

Ways to implement Security Filter

In order to apply the security filter for FreeForm SQL report. It required additional steps as shown below.

1. Create a new security filter, as shown below:

Generally all the objects which are created in FF report has objects which are managed by system in MicroStrategy . In order to create the Security filter you need to have stand alone attribute.

 After creating the security filter , apply it at user level of group level to user by opening the user manager (user -> Security filter or directly adding into group ).

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

2) 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

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

Based on the requirement you can choose the way and modify it accordingly.

Hope this helps you to implement the security at required level and you can deliver the desired/expected result to end users.

Happy learning !!!.

Please let me know you comments/feedback/suggestions in case of any .