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
-
Add a new prompt or existing prompt to the Freeform SQL statement.
-
In the SQL Statement pane (the top pane on the right), locate the prompt in pink.
-
Highlight the related syntax before/after the prompt (for example, "where") and the prompt itself.
-
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.