MicroStrategy Reporting features to SQL - Microstrategy

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

  • filters
  • prompts
  • drilling

Filters

A filter specifies the conditions that the data must meet to be included in the report results.

You cannot use existing filters in a Freeform SQL report; however, you can still do filtering by including a Where clause in the SQL statement. You can even embed prompt objects in the Where clause, if needed. For example,

where Year_ID=[Year_prompt]

Only two kinds of prompts can be used: value prompts and element list 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 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 Wizard that allows you to create a new value prompt or an element list prompt.
  • Note the following:

    • Only project attributes can be used to create prompts in Freeform SQL reports.
    • Any prompt created this way is saved as a normal object in the metadata.
  • Insert Prompt: displays the Open 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.

Once you exit the Prompt Wizard or the Open dialog box, the prompt is inserted automatically 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 space, for example, where Year_ID = Year_prompt and where Year_ID = [Year prompt].

Element list prompts

If the prompt is an attribute element list prompt and you use the key word In, you need to manually add parentheses around the prompt name in the SQL statement. Note that youcan 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.

select a11.[YEAR_ID] AS YEAR_ID from [LU_YEAR] a11 where a11.[YEAR_ID] in (Year_prompt)

If you use other operators such as =, >, <, or =/, you do not need to add any parentheses around the prompt name, as you do when you use In. 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.

select a11.[YEAR_ID] AS YEAR_ID from [LU_YEAR] a11 where a11.[YEAR_ID] = Year_prompt

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-74') for Oracle and “1974-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.

Select a11.[YEAR_ID] AS YEAR_ID From [LU_YEAR] a11 Where a11.[YEAR_ID] in 'Text_prompt'

You do not need to add the quotes around the prompt name if the answer is part of the SQL command. This feature actually allows you to select objects from multiple few text files that you can use in the SQL statement. See the example below.

Select Product_ID, Product_DESC, Budget From Text_prompt

Adding single quotes or parentheses around the SQL strings of text value prompts or element list prompts, respectively, is a feature with MicroStrategy 8.0.1. If you used the MicroStrategy 8.0 version and upgrade to 8.0.1, you may need to modify your Freeform SQL reports accordingly if they contain these prompts. Otherwise, the reports may fail.

Optional prompts

When you create a new prompt to add to the SQL statement, you can make the answer optional by not selecting 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 (use the Prompt Editor to find out).

No matter whether the prompt is a new or an existing one, if the prompt answer is optional, then make sure that the syntax related to the prompt is also made part of the prompt string. To accomplish that, in the SQL Statement panelhighlight the related syntax before and/or after the prompttogether with the prompt itself, then right-click the highlighted part and select Prompt-dependent SQL. The related syntax will turn pink just as the prompt. For example, where Year_ID in Year_prompt, instead of where Year_ID in Year_prompt.

For step-by-step instructions, please refer to the online help (search for “Adding an optional prompt to the SQL statement”). 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.

If you use an older version of MicroStrategy prior to8.0, make sure that you run the project update for the metadata; otherwise, the optional prompt functionality will not be applied to Freeform SQL reports.

Drilling

Drilling allows you to look at specific data at levels other than what is originally displayed on the grid or graph. For regular reports, you can drill in different directions, such as down, up, or across attributes, based on the system-generated drill maps or custom drill maps. For Freeform SQL reports, support for drilling is limited to only attributes within the Intelligent Cube.

This functionality is controlled by the “Drill with Intelligent Cube” privilege.

For example, a report has the Year and Quarter attributes. When you move Quarter off the report and into the Object Browser, you can only drill down from Year to Quarter on the report. However, if both attributes are placed on the report, you cannot drill from either one of them.

If you move Year off the report and into the Object Browser, you cannot drill up from Quarter to Year on the report.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Microstrategy Topics