Examples

The named format 'Fixed' is formatting values as a percent. Displays at least one digit to the left and two digits to the right of the decimal separator.

Query

with member val as 50 / 100, FORMAT_STRING = 'Fixed'
select from sales where val cell properties formatted_value

Overridden XMLA Properties

Result

0.50

Assertion : MDX Equals

with member val as "0.50"
select from sales where val cell properties formatted_value

The named format 'Fixed' is formatting values as a percent. Displays at least one digit to the left and two digits to the right of the decimal separator.

Query

with member val as 50, FORMAT_STRING = 'Fixed'
select from sales where val cell properties formatted_value

Overridden XMLA Properties

Result

50.00

Assertion : MDX Equals

with member val as "50.00"
select from sales where val cell properties formatted_value

The named format 'Percent' is formatting values as a percent. Displays the number multiplied by 100 with a percent sign (%) appended to the right. Always displays two digits to the right of the decimal separator.

Query

with member val as 50 / 100, FORMAT_STRING = 'Percent'
select from sales where val cell properties formatted_value

Overridden XMLA Properties

Result

50.00%

Assertion : MDX Equals

with member val as "50.00%"
select from sales where val cell properties formatted_value

The named format 'Percent' is formatting values as a percent. Displays the number multiplied by 100 with a percent sign (%) appended to the right. Always displays two digits to the right of the decimal separator.

Query

with member val as 50, FORMAT_STRING = 'Percent'
select from sales where val cell properties formatted_value

Overridden XMLA Properties

Result

5000.00%

Assertion : MDX Equals

with member val as "5000.00%"
select from sales where val cell properties formatted_value

The named format 'ElapsedMillis' is formatting millisecond values and display hours, minutes, seconds and milliseconds.

Query

with member val as ((3600 + 60 + 2) * 1000) + 432, FORMAT_STRING = 'ElapsedMillis'
select from sales where val cell properties formatted_value

Overridden XMLA Properties

Result

01h 01m 02s 432ms

Assertion : MDX Equals

with member val as "01h 01m 02s 432ms"
select from sales where val cell properties formatted_value

Format a value using a JAVA format as described here.

Query

with member val as 1000 / 3, FORMAT_STRING = '%3.1f'
select from sales where val cell properties formatted_value

Overridden XMLA Properties

Result

333.3

Assertion : MDX Equals

with member val as "333.3"
select from sales where val cell properties formatted_value

To display a (localized) thousand separator, use the comma character inside the JAVA format as following:

Query

with member val as 1234.56, FORMAT_STRING = '%,.2f'
select from sales where val cell properties formatted_value

Overridden XMLA Properties

Result

1,234.56

Assertion : MDX Equals

with member val as "1,234.56"
select from sales where val cell properties formatted_value

To format a value that represents a percentage, you'll have to escape the % character as demonstrated in this example. Note that the 'Percent' FORMAT_STRING will multiply
the value by 100.

Query

with member val as 50.0, FORMAT_STRING = '%2.0f%%'
select from sales where val cell properties formatted_value

Overridden XMLA Properties

Result

50%

Assertion : MDX Equals

with member val as "50%"
select from sales where val cell properties formatted_value

Format a date value using a JAVA simple date format as described here.

Query

with member val as now(), FORMAT_STRING = 'd-M-yyyy'
select from sales where val cell properties formatted_value

Overridden XMLA Properties

Result

13-5-2012

Assertion : MDX Equals

with
  member today as now()
  member val as str(day(today)) + "-" + str(month(today)) + "-" + str(year(today))
select from sales where val cell properties formatted_value

The function formatter('duration', 'pattern') allows to format a duration value expressed in milliseconds. Available pattern tags are:

• %y : year (no leap)
  
• %M : month (as 30 days)
  
• %d : day
  
• %h : hour
  
• %m : minute
  
• %s : second
  
• %S : millisecond
  

Query

with member val as (25 * 3600 * 1000) + 25 * 60 * 1000 + 30 * 1000, FORMAT_STRING = formatter("duration", "%hh %mm")
select from sales where val cell properties formatted_value

Overridden XMLA Properties

Result

25h 25m

Assertion : MDX Equals

with member val as "25h 25m"
select from sales where val cell properties formatted_value

As for any other CELL PROPERTIES an MDX expression can be used to define the FORMAT_STRING. In this example, we are demonstrating how to test for Nan/Infinite numbers and display a dedicated message accordingly. Note the usage of a declared FUNCTION; a better solution would be to define this function once at schema level and re-use it in many places.

Query

with
  function myFormatString() as
             iif( isNaN( currentCellValue() ),
                  'invalid number',
                  iif( isInfinity( currentCellValue() ), 'infinite number', '%f' )
             )

  member nan as 0.0 / 0.0, FORMAT_STRING = myFormatString()
  member infinity as 1.0 / 0.0, FORMAT_STRING = myFormatString()

select { nan, infinity } on 0 from sales cell properties formatted_value

Overridden XMLA Properties

Result

Assertion : MDX Equals

with
  member nan as 0.0 / 0.0, FORMAT_STRING = 'invalid number'
  member infinity as 1.0 / 0.0, FORMAT_STRING = 'infinite number'

select { nan, infinity } on 0 from sales cell properties formatted_value