Hacking an Object Prompt into a Metric on a View Report
I think I’ve done some pretty crazy things in MicroStrategy. It’s a lot of fun to push the limits of the tool and use it in ways that it wasn’t originally designed for. Today, I think I’ve pushed it farther than I ever have before in terms of just how crazy this post is. Surely, other things I’ve done probably have more usefulness and provide better impact, but in terms of just how far I had to go here, nothing has come close to this one. The backstory here is that I had already poured a ton of work into a set of reports, only to have a last minute requirement blow things up (we’ve all been there I think). In desperation I was trying all kinds of crazy things to avoid the new mountain of work, and struck gold with a zany, hair-brained idea that paid off. It saved me countless hours of tedious word, a long list of messy objects, and was really exhilarating in the process (at least, as exhilarating as MicroStrategy can be).
After completing the above, I was then tasked with showing the 7d Average for the chosen metric. In MicroStrategy, this can be accomplished by using the OLAP Function MovingAvg() like:
MovingAvg<SortBy= (Day@ID) >([Object Prompt], 7)
For each day, it will show the average of the previous 7 days which acts to smooth out the line and make the trend more obvious. Normally, this would be pretty straight forward: Just create a new metric with the Object Prompt I created earlier in place. But since it’s based on a View Report and not using the SQL Engine, this becomes very tricky since Derived Metrics can’t contain Object Prompts …
But, this formula does work as a physical metric…
I also can’t add a metric to the View Report if that metric isn’t in the underlying cube, despite the fact that the metric could be answered by the cube.
So how do we do it? The traditional way would be to add “7d avg” versions of every metric (about 25 in total in my project) and then edit my original 100 links to modify the Object Prompt answers. As I was dreading all of that work, I had some crazy ideas for some workarounds.
Hold on tight, because this is going to get pretty crazy …
Hacking an Object Prompt into a View Report
MicroStrategy View Reports can only include objects that are in the underlying cube. But since the Cube and View Reports are two different objects, and dependency checks aren’t performed when changing the definition of an object, it’s possible that the View Report can come into an invalid state.
I’ve setup a View Report with the Units Sold metric on the template and after everything was saved, I removed the Units Sold metric from the underlying Cube and republished it. Fortunately, MicroStrategy handles this invalid state for us and replaces the reference with what looks like a Derived Metric, even though if you check it’s properties, it still pulls up the physical metric.
If you run the report, nothing explodes, but since the Units Sold metric isn’t in the underlying cube, it just returns a blank column.
This is the behavior we’ll exploit. 🙂
First, we create our metric with a basic, inconsequential definition, like Sum(1).
This allows us to add the metric to the Cube …
And add it to the View Report (make sure you re-publish the Cube first) …
I’ve also dragged an Object Prompt onto the View Report which contains the metrics Cost, Profit, Revenue and Units Sold. Interestingly, you can’t drag it on from the Object Browser, but you can drag it in from Desktop.
Now, I will remove the 7d avg metric from the Cube and republish it. The result is the phantom metric reference like we saw in our test before.
Since we saw before that this is still a reference to the physical metric, we can now change the definition of the metric to the originally intended formula:
Now, when we save this metric and run the View Report, it’ll resolve this metric using the new definition, even though it isn’t in the underlying Cube. But since the metric definition is an Object Prompt that will resolve to a metric that is valid in the Cube, it’ll resolve correctly, giving the 7d avg for whichever metric chosen by the Object Prompt!
Whew, this post was long and complicated and it took me a very long time to write it up. This solution worked in all 4 of the cubes I was using in my project and I saved a ton of time. Hopefully MicroStrategy will someday add this functionality to the UI natively since clearly the engine can handle it, but in the mean time it was pretty fun to come up with this workaround.