Reporting Analysis Features

This section discusses the following MicroStrategy reporting analysis features in relation to Freeform SQL reports:

• Filters
• Prompts
• Drilling
• Using Freeform SQL Reports to Filter Other Reports

Filters

A filter specifies the conditions that the data must meet to be included in the report results. For information on Filters in general, refer to Advanced Filters: Filtering Data on Reports, in this guide.

You cannot use existing filters in a Freeform SQL report; however, you can still filter data by including WHERE and HAVING clauses in the SQL statement. You can even embed prompt objects in the WHERE or HAVING clause, if needed. For example,

Only two types of prompts can be used: value prompts and element list prompts. For more information, see Prompts.

In addition, you can use the view filter functionality for Freeform SQL reports in the same way as for regular reports.

Prompts

A prompt is a MicroStrategy object that allows user interaction at report run time. For general information about prompts, see Advanced Prompts: Enabling User Input.

For Freeform SQL reports, only two types of prompts are supported—value prompts and element list prompts. To add prompts, you can select from the two options on the Edit menu in the Freeform SQL Editor:

• Add New Prompt: launches the Prompt Generation Wizard, which allows you to create a new value prompt or an element list prompt.

  Only project attributes can be used with element list prompts in Freeform SQL reports, as opposed to managed object attributes. For more information on managed objects, see Map Data to Non-Project Objects: Managed Objects.

  Any prompt created this way is saved as a normal object in the metadata.

• Insert Prompt: displays the Select a Prompt dialog box where you can select an existing prompt that you have previously created in the project, either a value prompt or an element list prompt.

  You cannot type the name of an existing prompt directly into the SQL statement.

When you exit the Prompt Generation Wizard or the Select a Prompt dialog box, the prompt is inserted into the SQL statement at the current cursor position. If an area in the SQL statement is highlighted, it is replaced by the prompt name. Prompt objects appear in the SQL statement in pink and are enclosed in brackets ([ ]) if the name of the prompt contains any spaces, for example:

Element List Prompts

If the prompt is an element list prompt and you use the key word In, you need to manually add parentheses around the prompt name in the SQL statement. You can select either a single answer or multiple answers to the prompt, yielding results such as (4) or (1,2,3,4). See the example below.

If you use other operators such as =, >, <, or =/, you do not need to add any parentheses around the prompt name. However, you can only select a single answer to the prompt. Therefore, make sure that the maximum number of answers allowed for the prompt is set to 1. See the example below.

Value Prompts

Date and number value prompts are properly formatted to the standards of the database platform that the Freeform SQL report is executed against. For example, a date value prompt yields TO-DATE('08-MAY-26') for Oracle and "2026-05-08" for DB2.

However, for text value prompts, you need to manually add single quotes (' ') around the prompt name if you want the text prompt answer to be applied as a text constant. See the example below.

You do not need to add the quotes around the prompt name if the answer is part of the SQL command. See the example below.

If you are prompting on which table to retrieve data from, you should enter the most commonly used or most appropriate table as the default value of the prompt.

Optional Prompts

When you create a new prompt to add to the SQL statement, you can make the answer optional by clearing the Prompt answer required option in the Prompt Generation Wizard. Alternatively, if you use an existing prompt, you need to know if this option was selected during the prompt creation. You can open the prompt with the Prompt Editor to check whether the option has been selected.

For both new and existing prompts, if the prompt answer is optional make sure that the syntax related to the prompt is also made part of the prompt string. Taking this step ensures that if the optional prompt is not answered, then neither the syntax nor the prompt will be processed when the report SQL is generated. If you do not take this step, the report will fail due to an invalid SQL statement.

To accomplish this, refer to the procedure below.

To Define Freeform SQL Statements to Support Optional Prompts

1. In the SQL Statement pane (the top pane on the right), highlight the related syntax before and/or after the prompt together with the prompt itself.
2. Right-click the highlighted part and select Prompt-dependent SQL. The highlighted syntax turns pink just as the prompt.

   You should highlight the entire syntax that could be affected by the optional prompt. For example, the first example shown below is correct, whereas the second example could return an error if no answer is supplied for the optional prompt.

   

   

Drilling

Drilling allows you to look at specific data at levels other than what is originally displayed on the grid or graph. For standard reports, you can drill in different directions, such as down, up, or across attributes, based on the drill map. For Freeform SQL reports, support for drilling is limited to attributes within what is known as a personal Intelligent Cube. For a description of what a personal Intelligent Cube is, see the section Intelligent Cubes.

Drilling in Freeform SQL reports works in the following ways:

• This functionality is controlled by the privilege named Drill within Intelligent Cube, and requires an OLAP Services license.
• You can only drill to attributes that are included in the Report Objects pane but are not on the report grid.
• You can only use drill maps with project attributes, since managed object attributes do not have associated drill maps.
• You can drill from managed objects to any other project objects that are included in the Report Objects pane but are not on the report grid. You cannot drill from a managed object to another managed object.

  For more information on managed objects, see Map Data to Non-Project Objects: Managed Objects.

• You can drill down and drill up on project attributes. For example, a Freeform SQL report has the Year and Quarter attributes on the report grid. When you move Quarter off the report to the Report Objects pane, you can drill down from Year to Quarter on the report. Additionally, if Quarter remains on the report and you move Year off the report, you can drill up from Quarter to Year.
• You can drill to attributes that are not in the same hierarchy as the attribute you are drilling from. For example, if Quarter from the Time hierarchy is on the report and Category from the Product hierarchy is only in the Report Objects pane, you can drill from Quarter to Category.

  Managed objects do not have an associated hierarchy, so they are able to drill to any other project attribute that resides in the Report Objects pane but is not on the report grid.

Using Freeform SQL Reports to Filter Other Reports

Freeform SQL reports can be used to filter other standard reports in MicroStrategy. This can be supported by using a Freeform SQL report as a shortcut-to-a-report qualification on a standard report. For information on shortcut-to-a-report qualifications, see About the Report-As-Filter.

However, there are additional configurations and requirements to allow Freeform SQL reports to be used as a shortcut-to-a-report qualification, as described below:

• The Freeform SQL report must map data to project attributes rather managed object attributes. This is required in order for the standard report to recognize the data that is available on the Freeform SQL report. For information on mapping data in Freeform SQL reports to project attributes, see Access and Analyze Multiple Data Sources with Freeform SQL.
• If the Freeform SQL report returns data from a data source that is not the primary data warehouse, you must have the Execute Multiple Source Report privilege. This privilege is available as part of MicroStrategy MultiSource. For information on MultiSource, see the Project Design Help.

To add a Freeform SQL report as a shortcut-to-a-report qualification in a standard report, open the standard report in MicroStrategy Developer. In the Report Filter area, double-click the arrow to add a new qualification. In the Filtering Options, select Add a Shortcut to a Report. Click OK. Click the … (browse button) to select a Freeform SQL report, and click OK. Click OK again to create the filter qualification.

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, 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.

  After 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.