☝️Small business or a startup? See if you qualify for our special offer.
+
All documentation
Connecting to data source

Number formatting

Use number formatting to configure the appearance of the value cells in your report. A number format is described by the FormatObject.

When formatting values, you can define the following:

  • Separators for thousands and decimals.
  • The number of decimals to show after the decimal separator.
  • How null and infinity values are displayed.
  • The currency symbol and its position – right or left of the number.
  • How negative values are formatted.
  • Text alignment.

To see different examples of using number formatting, visit our Examples page.

Number format properties

See the full list of available FormatObject properties.

Check out an example of the FormatObject:

format: [
{
name: "",
thousandsSeparator: " ",
decimalSeparator: ".",
decimalPlaces: -1,
maxDecimalPlaces: -1,
maxSymbols: 20,
currencySymbol: "",
negativeCurrencyFormat: "-$1",
positiveCurrencyFormat: "$1",
isPercent: "false",
nullValue: "",
infinityValue: "Infinity",
divideByZeroValue: "Infinity",
textAlign: "right",
beautifyFloatingPoint: true
}
]

Live example

Default number format

The component has a built-in default number format applied to all measures. It is composed of the default values of the FormatObject properties.

You can override the default number format in the following ways:

  • Via UI
  • In the report 
  • Using API calls

Via UI

Step 1. Select Format ( ) > Format cells ( ) on the Toolbar. As a result, the Format cells pop-up window will appear.

Step 2. In the pop-up window, choose Default value from the CHOOSE VALUE dropdown menu.

Step 3. Configure the number format. For example:

Default number formatting configuration

Step 4. Click APPLY to save your configuration.

The number format will be applied to all measures and will be saved within the report.

In the report

Define the format and set its name property to an empty string (""):

report: {
formats: [
{
name: "",
// Other configs
}
],
}

Live example

Note The values specified for the default number format will be shown as the predefined values in the Format cells pop-up window Live example.

Using API calls

Step 1. Create a FormatObject and set its name property to an empty string ("").

Step 2. Apply the created object using setFormat(). Skip the measureName and aggregation parameters.

Step 3. Redraw the component using the refresh() API call.

For example:

let format = {
name: "",
currencySymbol: "$",
thousandsSeparator: ","
};

pivot.setFormat(format);
pivot.refresh();

Live example

Number format for a specific measure

A number format can be applied to one or several measures. However, a measure can have only one format.

You can apply a number format to a specific measure in the following ways:

  • Via UI
  • In the report 
  • Using API calls

Via UI

Step 1. Select Format ( ) > Format cells ( ) on the Toolbar. As a result, the Format cells pop-up window will appear.

Step 2. In the pop-up window, choose a measure from the CHOOSE VALUE dropdown menu and configure its number format.

Note Starting from version 2.8.22, you can choose several values in the corresponding dropdown menu to apply formatting to several measures simultaneously.

See an example
Here we show how to set a number format for multiple measures simultaneously

Step 3. Configure the number format. For example:

number formatting

Step 4. Click APPLY to save your configuration.

The number format will be applied to the measure and will be saved within the report.

In the report

Step 1. Define the format and specify its name property.

Step 2. Specify the format's name in the measure’s format property in the slice.

For example:

report: {
formats: [
{
name: "currency",
currencySymbol: "$"
},
// Other formats
],
slice: {
measures: [
{
uniqueName: "Price",
aggregation: "sum",
format: "currency"
},
],
// Other slice properties
},
// Other configs
}

Live example

Using API calls

Step 1. Create a FormatObject and specify its name property.

Step 2. Apply the created object to the measure using setFormat().

Step 3. Redraw the component using the refresh() API call. 

For example: 

let format = {
name: "PriceFormat",
currencySymbol: "$",
thousandsSeparator: ","
};

pivot.setFormat(format, "Price", "sum");
pivot.refresh();

Live example

Note A format can be applied to a measure that is not active in the slice (the active property is false).

Inheriting properties from the default format

If some properties are not defined in a format for a specific measure, their values are inherited from the default format. In the following example, each measure with the number format "currency" or "amount" will also have thousandsSeparator: "," since it was defined in the default format:

report: {
  dataSource: {
    filename: "https://cdn.flexmonster.com/data/data.csv"
  },
  formats: [
    { 
      name: "",
      thousandsSeparator: ","
    },
    {
      name: "currency",
      currencySymbol: "$"
    },
    {
      name: "amount",
      decimalPlaces: 0,
      currencySymbol: " pcs.",
      positiveCurrencyFormat: "1$"
    }
  ],
  slice: {
    rows: [ { uniqueName: "Category" } ], 
    measures: [ 
      { 
        uniqueName: "Price",
        aggregation: "sum",
        active: true,
        format: "currency"
      }, 
      {
        uniqueName: "Discount",
        aggregation: "sum",
        active: false,
        format: "currency"
      },
      {
        uniqueName: "Quantity",
        aggregation: "sum",
        active: true,
        format: "amount"
      }
    ]
  }
} 

Live example

Setting a currency symbol

The currencySymbol property can be specified in three ways: 

  • As the symbol itself (e.g., currencySymbol: "€").
  • As a numeric HTML code of the symbol (e.g., currencySymbol: "€").
  • As a named HTML code of the symbol (e.g., currencySymbol: "€").

All the approaches are interchangeable unless you are planning to:

In these cases, specify the currencySymbol as the symbol itself, e.g., by copy and paste. Otherwise, it will be displayed as code.

If exporting the grid to HTML or image, you can use all available approaches to set the currencySymbol.

Number formatting from an OLAP cube

If you have already defined formats for measures in an OLAP cube and you want to use those formatted values in Flexmonster, set the options.useOlapFormatting property to true (it is turned off by default), as follows:

report: {
dataSource: {
type: "microsoft analysis services",
proxyUrl: "https://olap.flexmonster.com/olap/msmdpump.dll",
cube: "Adventure Works",
catalog: "Adventure Works DW Standard Edition"
},
options: {
useOlapFormatting: true
}
}

Live example

Note The useOlapFormatting option is supported for Microsoft Analysis Services via both Flexmonster Accelerator and XMLA.

Customizing the Format cells pop-up window

In the Format cells pop-up window, you can override the default formatting values and limit configs available in the dropdown menus.

Overriding the default formatting values

To change values that are selected by default in the Format cells pop-up window, specify the default number format in the report. As a result, values from the default number format will be selected as the predefined values in the Format cells pop-up window Live example.

Limiting configs available in the dropdown menus

To limit the list of available configs in the dropdown menus, specify only the needed values in the toolbar.defaults.numberFormatting.<config_name> array inside the beforetoolbarcreated handler.

List of configs whose values can be limited:

  • textAligns
  • thousandsSeparators
  • decimalSeparators
  • decimalPlaces
  • positiveCurrencyFormats
  • negativeCurrencyFormats
  • negativeNumberFormats
  • isPercent

Check out an example:

const pivot = new Flexmonster({
container: "pivotContainer",
toolbar: true,
// ...
beforetoolbarcreated: function(toolbar) {
toolbar.defaults.numberFormatting.negativeNumberFormats = [
{
label: "-1",
value: "-1",
},
{
label: "1-",
value: "1-",
},
];
// Other customizations
}
});

Live example

What’s next?

You may be interested in the following articles: