If none of the built-in number formats meet your needs, then we usually use Custom formatting. However trivial it sounds, custom formatting can really help to save your time if you add some simple tricks to your dossier creation routine.
To give you a simple example: how many times you created a derived metric to show your metric in thousands or millions, i.e. you divided a desired number by 10 or 100? In this article we will show you how to use custom formatting to obtain this goal. It will save your time and speed up your applications without a need of creating any extra objects.
All of examples that we provide are done using dossiers, however, the same method applies to ALL MicroStrategy applications like documents and reports.
You can create your own custom format in the Number Format tab by right clicking on a metric and by selecting Custom as the Category:
In the window that «General» appears, type in the custom format using the number format symbols. Custom formatting is the same one that is used in Excel files. This gives a lot of flexibility to obtain the perfect format for the display of your metric. Useful examples and instructions on how to use Excel custom formatting can be found on:
Now, let’s move to the practice.
- How to display metrics in thousands or millions without creating a separate derived metric?
a. We want to display AdjSales metric in thousands and we don’t want to create a separate derived metric.
b. Right click on the metric -> Number format -> Choose the format from the drop down list that fits the metric and that will be divided. In our example we chose the fixed option, with negative RED and thousand separator:
c. In the drop down box we change Fixed to Custom, so the chosen format gets populated in the custom box:
d. The formula that appeared has two parts separated with ‘;’: ‘#,##0;[RED]-#,##0′. First one for formatting positive numbers and second one for formatting negative numbers (you can read more about it here). To show the number in thousands, we need to add a ‘,’ to the formulas for positive and negative parts. So the final definition should looks like this: ‘#,##0,;[RED]-#,##0,‘. After accepting the new formula the result will be displayed the following way:
e. If you want to show the number in Millions, add ‘,,’ always to the end of formula for each condition (if there is one). The formula for millions, would look the following ‘#,##0,,;[RED]-#,##0,,‘. What if I have chosen a fixed number, with two decimal and no conditions? The same, you always add comas at the end, so for millions the formula would look like this: ‘#,##0.00,,‘.
2. How to add any symbol to my number without changing its format?
a. Imagine that our AdjSales metric is calculated and already displayed as a percentage and we want to add the % symbol to it. The first thing that you think of is to just change the format to the percentage, but what would be a result of such operation?
b. To append any kind of symbol to your formula without changing its format add ‘\’ followed by the symbol you want to add. In this case the final formula will look like this: ‘#,##0.00\%‘.
3. How to change the formatting on one metric used as «Generic Metric», when using dossier metric selector?
a. Thanks to this brilliant solution, now our Customers can use metric selectors in dossiers and they really like them! However if one metric gathers other metrics that have different formats, what would be the best solution to separate their display formatting? Imagine that we have a metric selector that switches between metric 1 and 2. Metric 1 is a fixed number we want to show without any decimals and metric 2 in a percentage value we would like to see with two decimals.
b. Adding different formatting to one metric can be solved using thresholds. Our threshold for fixed, no decimal metric 1 will look the following:
The metric number 2, with the percentage symbol and two decimals, will add another condition to the existing threshold:
If you are using totals, don’t forget to apply thresholds also to the totals!
c. Unfortunately the threshold solution doesn’t work in case of the graphs…In graphs, we are forced to use conditional custom formatting. For example in this case, we will try to write a formula with a condition that all the numbers higher than 100 (fix numbers), won’t have any decimals, and if they are lower than 100 (percentage), two decimals will be added to the result. To obtain this goal we used the following formula: ‘[>100]#.##;#.##0,00‘.
Examples above are the most common cases in which we used custom formatting during our customers engagements. Do you know or worked with any other cases in which this type of formatting might be useful?