MicroStrategy ONE

Supporting Grid/Graph filtering of document datasets based on Freeform SQL reports

Report Services document designers can enable Grid/Graphs as report condition selectors to filter the data in a dataset report, allowing users to view subsets of large amounts of data, rather than loading and displaying all the data at once. For example, the document below includes a graph that displays the revenue and profit for subcategories of products during various years. This document also includes a Grid/Graph report condition selector that includes the Category attribute. You can click the categories of this Grid/Graph to display results for different categories in the graph.

For example, you can view results for the Books category, as shown below:

If you click the Movies category, the graph displays the results for movies, as shown below:

For standard reports in MicroStrategy, this dataset filtering is automatically supported by re-executing the underlying report definition for a dataset. For the example shown above, the graph's SQL statement that is automatically generated by MicroStrategy when clicking a category is as follows:

select a13.[YEAR_ID] AS YEAR_ID,
a12.[SUBCAT_ID] AS SUBCAT_ID,
max(a14.[SUBCAT_DESC]) AS SUBCAT_DESC0,
sum((a11.[TOT_DOLLAR_SALES] - a11.[TOT_COST])) AS WJXBFS1,
sum(a11.[TOT_DOLLAR_SALES]) AS Revenue

from [ITEM_MNTH_SLS] a11,
[LU_ITEM] a12,
[LU_MONTH] a13,
[LU_SUBCATEG] a14

where a11.[ITEM_ID] = a12.[ITEM_ID] and
a11.[MONTH_ID] = a13.[MONTH_ID] and
a12.[SUBCAT_ID] = a14.[SUBCAT_ID]
and a14.[CATEGORY_ID] in (3)

group by a13.[YEAR_ID], a12.[SUBCAT_ID]

In the SQL statement listed above, the highlighted syntax and a14.[CATEGORY_ID] in (3) is dynamically generated when clicking the category selector.

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. 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. The Define Conditions dialog box opens, which is shown below.

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  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 described 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 added to the SQL statement when a user clicks the report condition selector. Prior to clicking 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, before the user makes a selection, no data is shown for the report. Additionally, this means that the SQL syntax that 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 before a user makes 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.