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:

Name

Mask

Description

Standard

%, .<D>f

Where D is the number of decimal places. Commas are injected into the figure as needed.

Other Standard Options

k or m

Indicates whether numbers in standard format are expressed in thousands (k) or millions (m).

Currency

<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.

Percent

<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 (%) and injects commas into the figure as needed.

Examples

Standard

This example produces 123,456.78:

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

This example produces 123,456:

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

This example produces 123,456.7

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

Other Standard Options

This example produces 123,456.78k:

format(123456780, "k")

This example produces 123.46m:

format(123456780, "m")

Currency

This example produces $123.44:

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

This example produces €123:

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

Percent

This example produces 12,343.6%:

format(123.436, 1)

This example produces 43.67%:

format(0.4367, 2)

This example produces 44%:

format(0.4367, 0)