Steps to insert security filter qualifications in Freeform SQL

Freeform SQL can allow security filter qualifications to be included or ignored in the Freeform SQL report. By default, the Freeform SQL reporting feature does not take security filters into account. The report designer must apply security filtering in each Freeform SQL report and configure it accordingly. Otherwise, any user can run the report without being limited in the data he or she sees.

Prerequisites

If you use a version of MicroStrategy prior to 8.0, make sure that you run the project update for the metadata; otherwise, the security filter functionality will not be applied to Freeform SQL reports.

Before You Begin

Security filters are not detected and do not function with Freeform SQL in the same way that security filters work with normal reports. Before you begin creating security filter qualifications in Freeform SQL, you should be familiar with the information on security filters in Freeform SQL addressed in the Advanced Reporting Help.

To insert security filter qualifications in Freeform SQL

1. Access the Freeform SQL Editor Security Dialog. (How?)

2. In the Attribute Mapping pane, map attribute forms to columns in the database. From the Object Browser, select the attribute to be mapped and click > to add it to the Attribute Mapping pane.

   For every attribute form you must provide the following information:

   • Attribute: When you add an attribute to the Attribute Mapping pane the name of the attribute is inserted here.

   • Form: Select the attribute form from the drop-down list. For example, to qualify on the CUST_ID column you can choose the ID form.

   • String: For every attribute form, provide the string that the Engine uses to replace the form itself when building a security filter expression into the SQL statement. This string is displayed when SQL is generated for the report. For example, Table1.Cust_ID.

3. In the Ignored Attributes pane, select attributes to be ignored for security filter qualifications.

4. Select the check box Allow security filters with Top and Bottom levels to be evaluated based on the select level of this report.

   This option explicitly defines the Select Level of the report (displayed in the line just above this option) as the true level of the data that is retrieved for the report when Top and Bottom criteria are evaluated.

   Exercise caution with this option:

   • Not selecting this option when the user indeed has Top and Bottom levels defined will cause the report to fail.

   • Select this option only when you are sure that your SQL statement does not retrieve data outside the restriction defined by the security filter. This means that you may need to check in advance the security filters for individual users one by one and see how each one may interact with the SQL statement.

5. In the Replacement String field, enter the syntax necessary to complete the string, for example, "and Security Filter" or "WHERE Security Filter".

   The default value for the replacement string is "Security Filter", which is replaced by the security filter condition when the report is generated.

   • Make sure that you write "WHERE" or "and" before "Security Filter" in the Replacement String field, instead of writing it directly into the SQL statement. This ensures that in the case when a user does not have a security filter, the whole string ("where Security Filter" or "and Security Filter") is dropped from the report SQL. Otherwise, the report fails upon execution.

   • You can modify the text before or after "Security Filter" based on your needs, even after the security filter is inserted into the SQL statement (double-click the security filter or right-click it and then select Edit).

6. Click OK. The security filter is inserted into the SQL statement at the current cursor location in the Freeform SQL Editor.