MicroStrategy ONE
Security filter qualifications in Freeform SQL
With the Freeform SQL Security Filter Dialog you can create security filter qualifications to insert into the Freeform SQL definition. These security filter qualifications allow Freeform SQL to recognize security filters applied to users and user groups qualifying on certain attributes. Follow the procedure in, Steps to access the Query Builder Security Filter Dialog to access this interface.
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.
The Freeform Security Filter Dialog includes the following panes:
-
Object Browser: You can use the drop-down list and the folder list below to browse the project and select the attributes to include as an attribute mapping or an ignored attribute.
You can also use the Find drop-down list to enter or select keywords to search for attributes in the project. For example, you can enter "Customer" to return a list of all attributes containing the string "Customer" in their names.
-
Attribute Mapping: The Attribute Mapping pane is located on the upper right side of the Freeform SQL Security Filter Dialog. This is where you map attributes to columns in the query to connect to security filter qualifications. For every attribute qualification to be recognized in security filters, you need to provide the following:
-
Attribute: The attribute you select to be connected to a Freeform SQL column and recognized in security filters. For example, if your project has security filters for the attribute Customer, select the Customer attribute.
-
Form: The attribute form type such as ID, DESC, and so on to qualify on. For example, if security filters qualify on the Customer ID column, select ID from the Form drop-down list.
-
String: The SQL string for the attribute form selected. For example, if security filters qualify on the Customer ID column from the LU_CUSTOMER table, set the string to LU_CUSTOMER.
-
Ignored Attributes: The Ignored Attributes pane is located below the Attribute Mapping pane in the Freeform SQL Security Filter Dialog. This is where you specify attributes that may be ignored by the Engine when the report is being generated, even if they appear in a security filter qualification. This option can be useful if your Freeform SQL report does not include attributes that are included in security filters. By ignoring attributes that are not included in the Freeform SQL report, it allows security filters to apply qualifications for mapped attributes without creating any security holes.
The other options for this interface are:
-
Allow security filters with Top and Bottom levels to be evaluated based on the select level of this report: Select this check box to explicitly define 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.
-
Replacement String: Enter the string to include in your SQL statement for the potential security filter qualifications. The replacement string is inserted into your SQL statement only if the user running the report has a security filter that qualifies on an attribute that has been included as an attribute mapping for Freeform SQL. If the user does not have a related security filter, then the whole replacement string is dropped from the SQL statement.
Therefore, you must be careful to create the replacement string so that a valid SQL statement can be created whether a user has a security filter or not. You should enter "WHERE Security Filter" if you are inserting the security filter qualifications at the beginning of the WHERE clause. You should enter "and Security Filter" if you are inserting the security filter qualifications after other qualifications in the WHERE clause.