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:
To see different examples of using number formatting, visit our Examples page.
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
}
]
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:
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:
Step 4. Click APPLY to save your configuration.
The number format will be applied to all measures and will be saved within the report.
Define the format and set its name property to an empty string (""):
report: {
formats: [
{
name: "",
// Other configs
}
],
}
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.
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();
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:
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.
Step 3. Configure the number format. For example:
Step 4. Click APPLY to save your configuration.
The number format will be applied to the measure and will be saved within 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
}
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();
Note A format can be applied to a measure that is not active in the slice (the active property is false).
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"
}
]
}
}
The currencySymbol property can be specified in three ways:
currencySymbol: "€").currencySymbol: "€").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.
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
}
}
Note The useOlapFormatting option is supported for Microsoft Analysis Services via both Flexmonster Accelerator and XMLA.
In the Format cells pop-up window, you can override the default formatting values and limit configs available in the dropdown menus.
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.
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:
textAlignsthousandsSeparatorsdecimalSeparatorsdecimalPlacespositiveCurrencyFormatsnegativeCurrencyFormatsnegativeNumberFormatsisPercentCheck out an example:
const pivot = new Flexmonster({
container: "pivot-container",
toolbar: true,
// ...
beforetoolbarcreated: function(toolbar) {
toolbar.defaults.numberFormatting.negativeNumberFormats = [
{
label: "-1",
value: "-1",
},
{
label: "1-",
value: "1-",
},
];
// Other customizations
}
});
You may be interested in the following articles: