MicroStrategy ONE

Define Conditions dialog box

If a dataset uses a Freeform SQL report to retrieve its data, a static Freeform SQL statement cannot automatically be updated to reflect this type of filtering. You must define the Freeform SQL report to apply this filtering to the Freeform SQL definition. Additional information on this filtering best practice is provided in Supporting Grid/Graph filtering of document datasets based on Freeform SQL reports.

You can provide this dynamic filtering support by inserting a condition placeholder into the Freeform SQL statement. Condition placeholders are created in a Freeform SQL statement by right-clicking within the Freeform SQL statement, and selecting Insert Conditions. This opens the Define Conditions dialog box.

You can then define the condition placeholder as follows:

  • Attribute: Click ... (Browse button) to select the attribute used in the report condition selector to filter the display of the dataset in the document. In the example described above, the Category attribute is used to filter the dataset.

  • Optional Text: Type the SQL syntax that is used for the condition in the Freeform SQL report. The SQL syntax should be created to filter data based on attribute elements, which ensures that the report condition selector can accurately filter the data.

    This text is used as part of the SQL statement, so it must support valid SQL syntax along with the rest of the SQL statement. Since the condition placeholder filters the results of the statement, this syntax should be included as part of the WHERE clause of the SQL statement.

    In the example describe above, the optional text can be provided as:

    and a14.[CATEGORY_ID] in ([Category])

    This statement filters the results based on the elements of the Category attribute. By including this at the end of the WHERE clause, this syntax is included in the SQL statement when a user clicks on the report condition selector. Prior to clicking on the report condition selector, the optional text is not included in the SQL statement.

  • Condition is required for report execution: Select this check box if the report for the dataset should only be executed when a selection is made using the separate report condition selector. If you select this check box, prior to the user making a selection, no data is shown for the report. Additionally, this means that the SQL syntax you include as the Optional Text is only included in the report when a user clicks on the report condition selector.

    If you clear this check box, the report is executed prior to a user making a selection using the report condition selector. This can provide users a view of the data prior to filtering the data. In this case, the SQL statement must be valid even if the optional text is removed completely from the SQL statement. In the example described for the optional text, the syntax and a14.[CATEGORY_ID] in ([Category]) is used because this can be removed completely from the SQL statement, and the WHERE clause still uses valid SQL.

Once you complete the definition, the condition placeholder is included in the Freeform SQL statement. This condition placeholder is dynamically generated whenever a user makes a selection using a separate report condition selector.