MicroStrategy ONE

Prompts in Freeform SQL reports

Prompts can be used in Freeform SQL reports as in standard MicroStrategy reports. Due to the nature of the Freeform SQL reports, only value prompts and element list prompts can be used.

How prompts are included within SQL statements

The prompts that you add to your Freeform SQL reports are inserted in the SQL statement location that you specify. Depending on the type of prompt you use, the SQL statements can be affected in the following ways:

If there are any spaces in the names of the prompts you include in your Freeform SQL reports, you must enclose the prompt with square brackets ( [ ] ). For example, a prompt named My_prompt does not need to be enclosed in square brackets, however, a prompt named My prompt must be enclosed in square brackets. Within the SQL statement, make sure prompts with spaces in their names are entered in the form [My prompt].

  • Value prompts: include date, numeric, and text prompts:

  • Date and number 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 will yield TO-DATE('08-MAY-74') for Oracle and "1974-05-08" for DB2.

  • Numeric prompts: include numeric and big decimal value prompts. The prompt name is replaced by the value entered by the user upon report execution. For example, a Freeform SQL report with a numeric value prompt could have the following syntax:

    • Select Customer_ID, Customer_Name
      From LU_Customer
      Where Customer_ID =      Value_Prompt

    • In this case, a single number such as 7 or 10 that is entered by a user replaces Value_Prompt.

  • Text prompts: are turned into strings without quotes, for example, My_text_prompt_answer. This means that you can use the text prompt answer to modify the actual SQL command (for example, you can use a prompt to select certain flat files to retrieve the data on the fly). Alternatively, you can also add single quotes around the prompt name in the SQL statement so the prompt answer is applied as a text constant.

  • If you want the prompt to be a database string, you must manually type single quotes around the prompt name in the SQL statement. See the example below.

    Select Customer_ID, Customer_Name
    From LU_Customer
    Where Customer_Name like 'Text_Prompt'

Adding single quotes around text prompts is a feature with MicroStrategy 8.0.1. If you used the MicroStrategy 8.0 version and upgrade to an 8.0.1 or later version, you may need to modify your Freeform SQL reports accordingly if they contain this type of prompt. Otherwise, the reports may fail.

  • If the prompt answer is part of the SQL command, you do not need to add anything extra. For example, if you want to select some objects from a few flat files, you can use the prompt as follows.

    • Select Product_ID, Product_DESC, Budget

    • FromText_Prompt

  • Element list prompts: allow you to select from an element list which elements of the specified attribute to include in the report. An element prompt used in a Freeform SQL report resolves to a list of IDs separated by commas without parentheses, for example, 1,2,3,4,5. Prompts of this type can only be used with columns mapped to project attributes. Columns mapped to managed objects cannot use element list prompts. You must include element list prompts in your SQL statement differently depending on whether the prompt follows the key work "In" or an operator such as =, >, or <.

  • If the prompt is an element list prompt and you use the key word "In", you must manually add parentheses around the prompt name in the SQL statement. See the example below.

    • Select a11.[YEAR_ID] AS YEAR_ID

    • From [LU_YEAR] a11
      Where a11.[YEAR_ID] in (Year_Prompt)

    • You can select either a single answer or multiple answers to this type of prompt, which yields a result such as (4) or (1,2,3,4).

    Adding parentheses around element list prompts is a feature with MicroStrategy 8.0.1. If you used the MicroStrategy 8.0 version and upgrade to an 8.0.1 or later version, you may need to modify your Freeform SQL reports accordingly if they contain this type of prompt. Otherwise, the reports may fail.

  • 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". See the example below.

    • Select a11.[YEAR_ID] AS YEAR_ID

    • From  [LU_YEAR] a11

    • Where a11.[YEAR_ID] = Year_Prompt

    • You can only select a single answer to this type of prompt, which yields only one result, such as 4. Therefore, make sure that the maximum number of answers allowed for the prompt is set to 1. Otherwise, the report will fail when a user chooses more than one element as the prompt answer.

Prompts in Freeform SQL reports are explained in more detail in the following topics: