Object prompts in MicroStrategy are very powerful. They allow users to swap out objects in a report quickly and easily. A good example is where there are a series of metrics like Gross Sales or a series of Net Sales metrics. In the example below I'll show you how to create a single prompt to change a metric series.
The screenshot below shows a report where I have a series of net sales and gross sales.
I want to create a single prompt which will display either net or gross sales metrics. With this in mind, I'll create two dummy objects which will be used for the prompt. I create a Max(1) Metric and name it "Net Sales" and a Max(2) metric and name it Gross Sales (for display in the prompt an to be use later).
Now I have my two metric objects. I need to give the user the ability to select one. I create an object prompt and make the selection required . Make sure to keep track of what you name the object prompt after you save it too!
Now I have a dynamic object prompt, but I need a static object to anchor the answer so I know which metric series to display. No problem, I create a metric which is a max of the object prompt. The tricky part is that you can't create this metric in developer. You'll have to create the metric in web and type out the formula. I name my new metric "Metric Series".
Now I have a single metric named "Metric Series" which is the prompt answer.
With this I can create the metrics I want to display on the report which will dynamically change based on the prompt answer. I use a case statement to define what should be displayed int he metric.
At the beginning we defined Net Sales as Max(1) and Gross Sales as Max(2). I will use these to determine which metric to display on the grid using a case statement. So, if the user select Net Sales in the prompt, which is 1, then display Net Sales else display gross. I'll just add that to a case statement within a metric for both CY and LY.
Now I run my report, answer the prompt, and my metric series is now dynamic!
Have fun!
Thanks for checking out this blog post. If you found this post helpful please consider donating. Any contribution is appreciated! Just click the PayPal icon at the bottom of this page.