FORMAT_STRING Clause (MDX)
Syntax and Details
You can use this clause when you define a calculated member with the CREATE MEMBER Statement or with the WITH Clause.
FORMAT_STRING = 'positive_piece;negative_piece;zero_piece;missing_piece;'
Where:
-
positive_piece controls how a positive value is displayed.
-
negative_piece controls how a negative value is displayed.
-
zero_piece controls how zero is displayed.
-
missing_piece controls how a missing value is displayed; this is not currently used.
Each piece is a literal and consists of one or more characters that include one of the following base units:
| Base Unit | Meaning | Example |
|---|---|---|
| # | Display the value without the thousands separator. Do not include any decimal places. | 12345 |
| #,# | Display the value with the thousands separator. Do not include any decimal places. This is the default display format for positive numbers. | 12,345 |
| #.## | Display the value without the thousands separator. Include two decimal places (or one decimal place for each pound sign after the period). Specify as many pound places after the period as you need. | 12345.67 |
| #,#.## | Display the value with the thousands separator. Include two decimal places (or one decimal place for each pound sign after the period). Specify as many pound places after the period as you need. | 12,345.67 |
You can include additional characters before or after the base unit, as follows:
-
If you include a percent sign (%), the system displays the value as a percentage. That is, it multiplies the value by 100 and it displays the percent sign (%) in the position you specify.
-
Any other characters are displayed as given, in the position you specify.
If a query includes multiple calculated members with different format strings, the SOLVE_ORDER clause controls which format string is used. (This clause is relevant only if the query contains calculated members on both axes. )
Examples
The following table shows some examples:
| Example | Logical Value | Display Value |
|---|---|---|
| FORMAT_STRING='#,#;(#,#);'
Note that this corresponds to the default way in which numbers are displayed. |
6608.9431 | 6,609 |
| –1234 | (1234) | |
| FORMAT_STRING='#,#.###;' | 6608.9431 | 6,608.943 |
| FORMAT_STRING='#%;' | 6 | 600% |
| FORMAT_STRING='$#,#;($#,#);' | 2195765 | $2,195,765 |
| –3407228 | ($3,407,228) |
