Skip to main content

FORMAT_STRING Clause (MDX)

Used with a definition of a calculated member, this clause specifies the display format for the data.

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)

See Also

FeedbackOpens in a new tab