MicroStrategy ONE

Steps to set prompt-dependent SQL for optional prompts

When you create a new prompt for a Freeform SQL report, as for a regular report, you can make answers optional by clearing the "Prompt answer required" option in the Prompt Generation Wizard. Also, if you use an existing prompt in the project, you also need to know if the prompt answer is required. You can open the prompt with the Prompt Editor to check whether the option has been selected.

If you do not make related syntax prompt-dependent for optional prompts the report fails at run time if the prompt is left unanswered. For example, if you have the following SQL statement:

Select   a11.[YEAR_ID] ASOptional_Year_Prompt

The syntax Where a11.[YEAR_ID] = has not been modified to be prompt-dependent. When a user runs the report and leaves the optional prompt unanswered, the following SQL statement is executed:

Select  a11.[YEAR_ID] AS YEAR_ID
From  [LU_YEAR] a11
Where  a11.[YEAR_ID] =

A SQL statement of this format fails upon execution. If the entire line Where a11.[YEAR_ID] = Optional_Year_Prompt is set as prompt-dependent, the following correct SQL syntax is executed when a user leaves the optional prompt unanswered:

Select   a11.[YEAR_ID] AS YEAR_ID
From   [LU_YEAR] a11

To set a prompt as optional in a Freeform SQL statement

  1. Add a new prompt or existing prompt to the Freeform SQL statement.

  2. In the SQL Statement pane (the top pane on the right), locate the prompt in pink.

  3. Highlight the related syntax before/after the prompt (for example, "where") and the prompt itself.

  4. Right-click the highlighted part and select Prompt-dependent SQL. The related syntax will turn pink just as the prompt. For example, Where [prompt].

The syntax related to the prompt and the prompt itself is not processed and is dropped from the report SQL if the prompt is not answered.

Related Topics