Custom Formatting Strings

Formatting metrics is something that we're all familiar with.  Currency, commas, decimal places and percents are common choices, but what happens when we have to do something more advanced?  The Number Formatting screen for a Metric contains an option to provide a Custom Formatting string, but documentation is sparse on this hidden gem of a feature.  Today, I'll talk a little bit about how it works and provide some useful links for crafting your own formatting magic.

To start, to access the Custom Formatting box, simply right click a Metric and choose Formatting -> Values.  Under the Number tab, Custom is the last option in the list.

This text box can hold a Custom Formatting String that will apply to the values in the metric.  You can embed some basic logic into it, and it follows the same syntax of Microsoft Excel's Custom Formatting syntax.  For more some good documentation on the different things you can do, check out Microsofts article on the subject:

http://office.microsoft.com/en-us/excel-help/create-or-delete-a-custom-number-format-HP001216503.aspx

One particular use case that I wanted to highlight was a requirement to show values as 3.5K instead of 3,500 and 4.6M instead of 4,600,000 in a Graph's axis.  At first I attempted to solve this with Conditional Formatting (and was successful), but unfortunately that formatting didn't carry over into the Graph's Axis. A colleague of mine found the above linked Custom Formatting document and managed to craft this handy Format:
[>=1000000]#.#0,,"M";[>=1000]#.#0,"K";0
To break it down, you can think of this String as a Case statement with 3 possibilities, each separated by a semicolon.

[>=1000000]#.#0,,"M"

[>=1000]#.#0,"K"

0

Within the first 2 statements, the condition within the brackets gives the condition that we're catching, in this case, when the value is a million or a thousand.  The formatting string #.#0, tells it that we want to format it as a number (#) with a decimal point (.) that includes 0 instead of rounding it off (0) and separate larger numbers with commas (,).  Finally, we append some free text within the double quotes to denote M or K.  The final 0 simply catches the formatting for the 0 value.

Sample data from MicroStrategy Tutorial

Ultimately, this solution worked great and we were able to meet the requirement of formatting the Graph Axis in this manner.  In our case, this was a proof of concept for a complicated report that we ended up doing in Flash Builder, but we do still have some old exported PDFs that we emailed to users that showed it was working.  I mention this, because when I tried to reproduce it again for the purposes of this post, I was not able to.  It seems others in a forum post asking about this feature today (which jogged my memory that we had done this and lead to this post) also had similar issues.  Not only was I not able to reproduce it in a Graph, but in Web the format string didn't even work in a Grid.  I've reached out to MicroStrategy Technical Support, and will update this post when a workaround is discovered or a patch is released.

In the meantime, mastering the Custom Format String can open a lot of opportunities for meeting requirements and making your numbers look a little better.  Just don't try to use them in a Graph Axis ... yet.