Format (PQL)

Formats numbers to produce text or string items with the selected display format or 'mask'.

  • Returned Output: Text
  • Library: PQL \ Common \ String
  • Version: 2018.00.000
  • Compatibility:
    • Can be combined with and other PQL function throughout the application.
    • It CANNOT be used with MDX or VBA functions. But it can be used on MDX-based content in other parts of the application.

Syntax

Format( <Numeric> , <Mask> )

* Click on the function's arguments above for more details on the input values.

Comments
  • The numeric value can be any element that resolves to a numeric value.
  • The text mask is a prescribed input that instructs the engine on how to format the number.
  • Format produces a text string. It cannot be used for subsequent numeric operations.
Different Function types
  • This function is like the Excel Format function.
  • Use the DateFormat function to format dates.

Masks

The syntax for describing a format mask is based on Java specifications. Details on the specification can be found here.

To facilitate the use of the masking syntax, Pyramid has exposed a variety of format mask presets in the application. The following summarizes those options.

Standard

The mask is "%, .<D>f" - where D is the number of decimal places. It will inject commas into the figure as needed.

This produces 123,456.78

format(123456.78, "%, .2f")

This produces 123,456

format(123456.78, "%, .0f")

This produces 123,456.7

format(123456.78, "%, .1f")

Other Standard Options

Using the 'k' mask, will show numbers in standard format, but in thousands. While using the 'm' mask will show these numbers in millions. Notice that the masks are in single quotes.

This produces 123,456.78k

format(123456780, 'k')

This produces 123.46m

format(123456780, 'm')

Currency

The mask is "<C>%, .<D>f" - where C is the currency symbol and D is the number of decimal places. This is similar to normal numbers, except it includes details for the currency symbol.

This produces $123.44

format(123.436, "$%, .2f")

This produces €123

format(123.436, "€%, .0f")

Percent

The mask is "<D>" - where D is the number of decimal places. Unlike standard and currency formatting, the format engine multiplies the number by 100, appends a percentage sign (%) at the end and injects commas into the figure as needed

This produces 12,343.6%

format(123.436, 1)

This produces 43.67%

format(0.4367, 2)

This produces 44%

format(0.4367, 0)