SYMPTOM:
A custom MDX metric is created in Strategy to simulate a conditional metric, where the attribute elements to consider in the metric calculation come from an optional prompt.
The following example is built against a Microsoft Analysis Services cube. The metric refers to a prompt that was created following the instructions in this Strategy Knowledge Base article:
KB17510: How to create a reusable prompt for MDX Cube reports in Strategy
If the user chooses Product Department elements when running the report, the metric evaluates properly. But, if the prompt is optional and the user does not provide an answer, the metric may return empty results, as shown below:
CAUSE:
Prompts in custom MDX formulas must be used in an ApplySimple expression. The prompt answer will be included in the MDX statement using simple string substitution.
If an optional prompt is unanswered, there is no string to substitute for the placeholder. In that case, the placeholder is simply omitted in the output formula:
Sum({ }, [Measures].[Store Sales])
This formula specifies a sum of Store Sales over an empty set. Adding up an empty list of sales values is, naturally, itself empty.
ACTION:
Currently this issue is still being reviewed for feasibility by our Technology team and it is not scoped for any upcoming scheduled Strategy release.
WORKAROUND:
If the prompt is edited to make it required, users will have to make an element selection and they will not encounter the empty metric results, as shown below:
