Bypassing Security Filters In MicroStrategy – Display Comparatives Across Hierarchy Levels

(Originally published June 14th, 2016)

Security filters in MicroStrategy are useful in implementing data-level security in MicroStrategy but they are applied globally across a project. This tends to be a challenge because sometimes there are exceptions or use cases where the security filter should not be applied.

For example, if you want to provide benchmark metrics that are at higher levels in the organizational hierarchy than that of the security filter, as illustrated in the graphic below:

What would you do if you have a requirement like this? The dashboard screenshot below shows an implementation where I achieved that functionality and was able to bypass the security filter by using a workaround.

Here is the procedure I followed to achieve the security filter bypass:

1. I defined Level Metrics for the level higher than the security filter level at which I wanted to display the metrics at. In this case it’s Zone level.

2. Then I created a report using those level metrics and the report level as per the dashboard requirements.

3. Next, I configured the Data Mart settings on that report to have it export the result to a database table.

4. Then I created a Freeform SQL Report to read that exported data. You need to have a compatible level on the report to be able to do this.

5. Finally, I used that Freeform SQL Report as an additional data source for the dashboard along with other normal reports that the dashboard is based upon.

Since this is a freeform SQL report with no link to the attribute used in the security filter, the security filter logic does not get applied to this report. Hence enabling me to create the dashboard where the comparative metrics are shown at a higher-level in the organizational hierarchy than the security filter allows.