Conditional Attribute Text with Derived Metrics
One of the coolest features in MicroStrategy that I think we take for granted is Derived Metrics. They’re incredibly useful, save lots of time and most importantly, save lots of clutter. They allow us to do quick little tricks with calculations and display and have gained more of a foothold now that they bubble up to Documents as of 9.0. However, their counterpart has sorely been lacking, and that would be a “Derived Attribute”. While 9.0 also introduced Derived Elements, these are currently limited only to View Reports and Grids on a Document. We can’t use them on normal Reports, and even in the cases we can use them, it’s basically just a Consolidation-on-the-fly. Still cool, but not exactly the same tool as a Derived Metric.
What I’d like to see as a feature in the future is the ability to do conditional values like a Case statement to conditionally show text/attribute values on the fly. While you can mostly accomplish this using an ApplySimple, this requires that all elements of the expression reside in the same table. If they don’t, you’re out of luck and looking at Model changes or Logical Views. However, there is one possible workaround that I sometimes use where we can leverage Derived Metrics to sort of do Conditional Attribute Text.
Derived Metrics will lend us a hand here since that’s our only fully available On-The-Fly object. They support all functions supported by the Analytical Engine (ie, not ones that require database SQL), including the one we’ll use today, a CASE statement.
The requirement I’m going to be working with is a simple one (and yes, I know you could probably do this one with a Derived Metric / Threshold or even an ApplySimple since they’re all in the Geography hierarchy, but it’s just a simple example from Tutorial).
The rule I’m going with is if the Call Center = Miami, then display the text “Web”, if the Revenue is > $7,000,000, then display the text “Online”, otherwise display the text of the Region.
To accomplish this, I made a Derived Metric with a CASE statement as defined as:
The syntax of the CASE statement function is:
CASE(Condition, True, Condition, True, … , False)
You can keep listing Conditions followed by the value to use if they’re true, and then the final option is if everything else falls through. Keep in mind that they are applied in order, so if more than 1 condition would be true, only the first one is used.
Unfortunately, you can only use Metrics in a CASE statement, so I also need to add a Derived Metric layer for each of my Attributes. Pay close attention to their dimensionality so you get the correct values.
You can control which form you want to display by using the syntax “Attribute@Form”. Set the dimensionality to match the level of the report since we’re using a Max function, otherwise the results will be incorrect for some rows.
– All involved Attributes must be on the Report Template. For example, if you move “Region” into the Report Objects, then the “Conditional” column will come up blank. The intermediate derived metrics do not need to be on the report.
– Fixed width columns and set the columns you don’t want to 0
– Change the color of the column header / values to match the background color so they aren’t visible and place them between the Attributes and Metrics. Hopefully the gap it creates isn’t too ugly.
– You only need the attribute on the template at a minimum, not necessarily the fields you’re using, so you could only include the ID (or some other short Form) and that will minimize the width.