Formatting Alternate Graph Bars in a Series

I had a requirement recently where I had a single metric on a bad graph that needed to use alternating colors for readability.  Unfortunately, the MicroStrategy Graph options for formatting a Series is limited to a single color.  Today I’ll share a quick trick on how I was able to work around it with Conditional Formatting to achieve the desired results.  This trick will also work in formatting alternate rows in a Document Detail section, which is another common use for it.

Creating the Metric
The basic idea of banding colors is to switch between alternating colors.  This means that to achieve this, we’ll need to create a series of alternating values.  The math function that can accomplish this is the modulus function, which returns the remainder when two numbers are divided by each other.  This means that if we create a metric with the definition: Mod(metric, 2), then each value in a linear series will generate a 0 if it’s even, or a 1 if it’s odd.

To generate a linear series of numbers, we can simply use the RunningSum function.  This function simply adds the current row value to the grand total encountered this far.  That means if we have a series of 1s, the RunningSum will generate a linear series.  To obtain a series of 1s, we can simply use the formula Sum(1), which will return a 1 for every row.

Our final metrics will look like this:

Physical metric: RunningSum(Sum(1))
Derived metric: Mod(RSmetric, 2)

The RunningSum metric has to be a physical metric since we need the database to perform the action.  The analytical engine seems to have difficulty returning the correct result for this calculation.  You may be able to tie the Mod() function in with it, but that too has given me some trouble on some platforms, so I generally create the two objects, one physical and one derived.

Conditional Formatting a Graph Series
Open your report or edit the graph on the Document and setup a Threshold.  You have to be in Grid mode to set Thresholds.  Create a normal Threshold on the metric, setting the definition as your Mod Metric = 0 = Color A, and Mod Metric = 1 = Color B.  With the Threshold selected (the background will turn gray), click the “Enable Threshold on Graph” icon in the toolbar.  You have to do this for every Threshold item.

Switch back to Graph mode, and you’ll now have alternating colors in your Graph Series.

Color Banding in a Document Detail Section
In Documents, the feature is renamed from Thresholds to Conditional Formatting, and it’s found under the Format menu instead of the Data menu.  You have the option of formatting every section except the Detail, but we can work around this too.  Ideally, you can just fit your text boxes in the Detail section to take up 100% of the space.  Be sure to set the section properties under layout to “Height can Shrink / Grow” to ensure a best fit.  You can now set the Conditional Formatting for each text box to the alternating color formatting strategy that we used in the Graph Series example above.

Alternatively, you could add a single text box or rectangle to fill up the background and format just that one item.  This may make managing the thresholds easier if you have lots of text boxes in the detail section.  Just make sure that all of the text boxes have a Transparent background (they do by default).