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.

You may also like...

16 Responses

  1. srk says:

    I wish i had known this before. Spent a lot of time for one of our custom formatting requirement, and finally we had to do it at the database side. Referred to help doc, but was not helpful… many thanks for sharing this!!

  2. iliyas says:

    It’s not working for million, but it is working for thousand.

    OrginalValue FormattedValue
    10000 10.0K
    1000000 100.0M (Which is incorrect)

  3. Hi Bryan,

    It is nice solution.

    Could you please explain me for this line “and separate larger numbers with commas (,). “

    If possible thn please provide small example.

    Thanks,
    Prince

    • Bryan says:

      There’s a link there that goes into Microsoft’s documentation that has very in depth details, but for this one:

      “A comma that follows a digit placeholder scales the number by 1,000. For example, if the format is #.0,, and you type 12,200,000 in the cell, the number 12.2 is displayed.”

  4. Hi Bryan,

    Thank you for your reply.

    Thanks,
    Prince

  5. Song Gao says:

    Hi Bryan,
    It is a greate solution.
    But as for the Graph Axis, it works for single axis, when it comes to dual axis, for example, Y1 for Revenue and Y2 for Revenue Mix(%), then the “$” or “K” or “M” will not show as expected.

    Any idea and way to fix it?

    Thanks,

  6. sravan says:

    Hello ,

    I’m able to bring K or M on the report but failed to bring the dynamics units (K or M ) on graph.Can any one throw some light on this

    Thanks

  7. kiran says:

    Hi,

    The above formatting when applied in Grid’s can be visible, but when tried to apply the same formatting in Dashboards, we are not able to see that effect.
    In dashboarding do we need to do any additional operations to achieve this?

  8. ★fire says:

    Hi Bryan,

    This formula works perfectly for us on reports, dashboards, charts. But it fails for negative numbers. Any idea how to make this work for both positive and negative numbers?

    Thanks,
    Aditya

  9. Saumya says:

    Hi,
    Can anyone suggest a work around for negative numbers? I tried using logical operator “OR” but its not working; it picks only the first condition.

  10. Sneha says:

    Hi,

    I have used Custom formatting ####-##-## to display number 20140220 as 2014-02-20. Using MicroStrategy 9.3.1, it works well in desktop and web, but does not reflect in iPad.

    Can anyone suggest?

    • Saurabh says:

      Hi Bryan,

      Thanks for the post. I tried the solution it and works perfectly for the Grid reports. When I try to convert the report to a graph the formatting is not available. In the graphs axis scale formatting as well I don’t see the option for custom formatting. Reading the posts on Microstrategy’s forum I saw it being stated as a bug in 9.2.1 . I am on the same version. Has it worked for graphs formatting for anyone on 9.2.1.

      Thanks!
      Saurabh

  11. Jennifer says:

    Does anybody know how to do this for Nulls? I would like to replace null fields with “N/A”. Thanks much!

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA Image

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current day month ye@r *