Formatting Report Cell Data

The Format Cells dialog box consists of the following tabs:

Number: Allows you to select the number formatting options, such as decimal spaces, currency symbol, time format, zip code format, and so on. If none of the built-in number formats meet your needs, you can create your own custom number formats using number format symbols. For more details on custom formatting, see Custom Formats.
Alignment: Determines how the contents of the section are aligned when the formatting is applied. You can select horizontal and vertical alignment, as well as select if you would like to wrap the text or not.
Font: Defines the text font for the selected section. You can select the font name, font style, size, color, and effects.
Border: Defines how the borders are displayed for the selected section.
Background: Defines how to fill the cell background. You can choose whether the background is a solid color, uses gradient colors (a two-color combination), uses a pattern, or is transparent to allow what is behind the cell to show.
Chart: Applies a background pattern and color for a metric when it is displayed as a series in a graph report.
By default, the chart color that you define for a metric overrides any default color schemes for the graph report, although you can disable this metric formatting. For steps, see Formatting a Graph Using Colors, Shading, and Effects.

Custom Formats

Custom formats allow you to create your own formats for data in a report. You can format text, numbers, and date and time using custom formats. Once you create a custom format, you can use it in other metrics and report objects as well. Each custom format can have up to four optional sections, one each for:

Positive numbers
Negative numbers
Zero values
Text

You can specify these sections, separated by semicolons in the order listed above. If you specify only two sections, the first is used for positive numbers and zeros, and the second is used for negative numbers. If you specify only one section, all numbers use the same format.

Numeric Data Formatting

You can format fractions or numbers with decimal points by including appropriate digit placeholders in the custom format. This is explained in detail in the following table:

Numeric formatting strings supported in MicroStrategy can have a different effect when applied to the Big Decimal data type. For example, if the numeric format for a Big Decimal contains only number signs (#) to the left of the decimal point, numbers less than one are displayed beginning with a zero rather than a decimal point. The format #.00 will display the number 0.43 as 0.43. Other data types display the same number in the same format as .43. For numeric formatting descriptions and examples when using the Big Decimal data type, see the Data Types appendix in the Project Design Guide.

Symbol	Description
0 (zero)	Digit placeholder. If the number contains fewer digits than the placeholders contained in the format, the number is padded with zeros. For example, the format code `00000` will display the number 12 as 00012. If there are more digits to the right of the decimal point than the placeholders in the format, the decimal portion is rounded to the number of places specified by the placeholders. If there are more digits to the left of the decimal point than the placeholders in the format, the extra digits are retained. If the format contains zeros to the left of the decimal point, numbers less than one are displayed with a zero to the left of the decimal point.
#	Digit placeholder. This digit placeholder displays only significant digits and does not display insignificant zeros. For example, the format code `##.##` will display the number 0025.630 as 25.63. If there are more digits to the right of the decimal point than the placeholders in the format, the decimal portion is rounded to the number of places specified by the placeholders. If there are more digits to the left of the decimal point than the placeholders in the format, the extra digits are retained. If the format contains only number signs (#) to the left of the decimal point, numbers less than one are displayed beginning with a decimal point. The format `#.00` will display the number 0.43 as .43.
?	Digit placeholder. This digit placeholder adds spaces for insignificant zeros on either side of the decimal point so that decimal points align when formatted with a fixed-width font. You can also use ? for fractions that have varying numbers of digits.
%	This symbol displays the number as a percentage, by multiplying the number by 100 and appending the % character.
, (comma)	Thousands separator. If the format contains commas separated by #'s or 0's, commas separate the thousands. Note that the actual thousands separator used depends on the session locale. A comma following a placeholder scales the number by a thousand. For example, using `0,` scales the number by 1000, so that 10,000 displays as 10.
. (period)	Decimal separator. Note that the actual decimal separator used depends on the session locale.
E+, E-, e+, e-	Scientific notation. If the format contains a scientific notation symbol to the left of a 0 or # placeholder, the number is displayed in scientific notation and an E or e is added. The number of 0 and # placeholders to the right of the decimal determines the number of digits in the exponent. E- and e- place a minus sign by negative exponents. E+ and e+ place a minus sign by negative exponents and a plus sign by positive exponents.

Character/Text Data

You can include formats for text and character data as mentioned in the following table:

Symbol	Description
"text"	Displays the text inside the quotation marks. Even if the text is a valid formatting symbol, it is treated as literal text if it appears within quotes. Use quotation marks around any character that is not a formatting symbol, including a space, the dollar sign ($), minus sign (-), slash (/), exclamation mark (!), ampersand (&), tilde (~), curly brackets ({ }), equals sign (=), less than and greater than signs (< >), and the caret (^). This ensures that the text appears correctly in both MicroStrategy Developer and MicroStrategy Web.
:	In a date/time format, the colon (:) does not need to be enclosed in double quotation marks (" "). However, to display it in a numeric format, it must be enclosed in quotes. For example, if you have an integer that must be displayed as 12:34:56, the correct format is "##":"##":"##".
*(asterisk)	This symbol repeats the next character until the width of the column is filled. Only one asterisk can be used in each format section.
_(underline)	This symbol skips the width of the next character. For example, to make negative numbers surrounded by parentheses align with positive numbers, you can include the format _) for positive numbers to skip the width of a parenthesis.

Date and Time

The format codes for formatting days, months, years and time in a report are given in the following table:

Symbol	Description
m	Month number. Displays the month as digits without leading zeros, such as 1. Can also represent minutes when used with h or hh formats.
mm	Month number. Displays the month as digits with leading zeros, as in 01. Can also represent minutes when used with the h or hh formats.
mmm	Month abbreviation, such as Jan.
mmmm	Month name, such as January.
d	Day number. Displays the day as digits with no leading zero, such as 1.
dd	Day number. Displays the day as digits with leading zeros, as in 01.
ddd	Day abbreviation, such as Sun.
dddd	Day name, such as Sunday.
yy	Year number. Displays the year as a two-digit number, such as 00.
yyyy	Year number. Displays the year as a four-digit number, such as 2023.
h	Hour number. Displays the hour as a number without leading zeros, such as 1. If the format contains an AM or PM format, the hour is based on a 12-hour clock; otherwise, it is based on a 24-hour clock.
hh	Hour number. Displays the hour as a number with leading zeros, as in 01. If the format contains an AM or PM format, the hour is based on a 12-hour clock; otherwise, it is based on a 24-hour clock.
m	Minute number. Displays the minute as a number without leading zeros, such as 0. The m format must appear immediately after the h or hh symbol; otherwise it is interpreted as month.
mm	Minute number. Displays the minute as a number with leading zeros, such as 00. The mm format must appear immediately after the h or hh symbol; otherwise it is interpreted as month.
s	Second number. Displays the second as a number without leading zeros, such as 0.
ss	Second number. Displays the second as a number with leading zeros, such as 00.
AM/PM am/pm A/P a/p	12-hour time. Displays time using a 12-hour clock. Displays AM, am, A, or a to display time between midnight and noon; displays PM, pm, P, or p to display time between noon and midnight.
[h]	Displays the total number of hours.
[m]	Displays the total number of minutes.
[s]	Displays the total number of seconds.

Color

You can change the color of data in your report using custom formatting. The following table lists the format for color codes:

Symbol	Description
[Black]	Displays cell text in black.
[Blue]	Displays cell text in blue.
[Cyan]	Displays cell text in cyan.
[Green]	Displays cell text in green.
[Magenta]	Displays cell text in magenta.
[Red]	Displays cell text in red.
[White]	Displays cell text in white.
[Yellow]	Displays cell text in yellow.

Currency

You can include the following currency symbols in a number format. Keep the ALT key pressed and type the ANSI code of the currency. The ANSI code should be followed by the format code for the number.

To type ANSI code for the currency symbol, turn on NUM LOCK and use the numeric keypad. As you type the ANSI code, the Custom box appears blank. The currency symbol is displayed only when you finish typing the code.

Hold the ALT key down and type this code	To display
0162	¢
0163	£
0165	¥
0128	Є

Conditional Symbols

You can apply conditional formatting to monitor the data in your report.

Symbol	Description
[conditional value]	Designates a different condition for each section. For example, data in a column has values ranging from 200 to 800 and you want the text "Poor" to be displayed in black for values less that 400, the text "Good" to be displayed in Red for values greater than 600, and the text "Average" to be displayed in blue for values ranging between 400 and 600. You can use the following code for these conditions: `[<400][Black]"Poor"; [>600][Red]"Good"; [Blue]"Average"` In this example, [<400] and [>600] are the conditional values.

Symbol

Description

[conditional value]

Designates a different condition for each section. For example, data in a column has values ranging from 200 to 800 and you want the text "Poor" to be displayed in black for values less that 400, the text "Good" to be displayed in Red for values greater than 600, and the text "Average" to be displayed in blue for values ranging between 400 and 600.
You can use the following code for these conditions: [<400][Black]"Poor"; [>600][Red]"Good"; [Blue]"Average"

In this example, [<400] and [>600] are the conditional values.

Custom Number Formatting Examples

The following table lists examples of custom number formats. It includes the formatting symbols, the report data, and how that data is displayed after using the formatting.

Format	Cell data	Display
#.##	250.436 0.43	250.44 .43
#.0#	250.436 125	250.44 125.0
???.???	123.43, 45.90, 345.809	With aligned decimals
#,##0"CR";#,##0"DR";0	2567 -4567 0	2,567CR 4,567DR 0
#,###	1500	1,500
0,	10,000	10
"Sales="0.0	123.45	Sales=123.5
"X="0.0;"x="-0.0	-12.34	x=-12.3
"Cust. No. " 0000	1234	Cust. No. 1234
m-d-yy	2/3/23	2-3-23
mm dd yy	2/3/23	02 03 23
mmm d, yy	2/3/23	Feb 3, 23
mmmm d, yyyy	2/3/23	February 3, 2023
d mmmm yyyy	2/3/23	3 February 2023
hh"h" mm"m"	1:32 AM	01h 32m
h.mm AM/PM	14:56	2.56 PM
#?/?	1.25	1 1/4
#?/8	1.25	1 2/8
ALT+0163 #.##	250.45	£ 250.45
#.##%	.08 2.8	8% 280%
$* #,##0.00;$* -#,##0.00	5632.567 -12.34	$ 5,632.57 $ -12.34
0*-	250.45	250.45----
*-0	250.45	----250.45
000-00-0000	345126789	345-12-6789
0.00E+00	10000	1.00E+04
##0.0E+0	10000	10.0E+03
0.00E+00	0.0001	1.00E-04
##0.0E-0	0.0001	100.0E-6
0.0E-00	0.0001	1.0E-04