The way how numeric values are formatted in the component can be defined in a report.
A default format is for all measures. The specific number formats can be defined for some measures in addition to the default format. More details are below in the following sections:
If the component is connected to OLAP cube and you already have formatted numbers there (Microsoft Analysis Services or Mondrian), you can display these formatted values without applying number formatting in the component. See more about this option below:
With number formatting you can define thousands and decimal separators, the number of decimals to show in the fractional part of a number, how to display null and infinity values, you can format number as currencies specifying the currency symbol and its position – right or left.
Here is a list of all available properties:
name – String. It identifies the format in the report, thus, it should be unique. The default is "", which means that this number format is a default one and it is applied to all the measures for which the specific number format is not set.thousandsSeparator – String. The default is " " (space).decimalSeparator – String. The default is ".".decimalPlaces – Number. The exact number of decimals to show in the fractional part of a number after the decimal separator. The default is -1, which means that the number will be shown as is.maxDecimalPlaces – Number. The maximum number of decimals to show in the fractional part of a number after the decimal separator. The default is -1, which means the number will be shown as is.maxSymbols – Number. The maximum number of symbols in a cell. The default is 20.currencySymbol – String. The symbol which is shown near the value (currency symbol, hours, percent, etc.). The default is "".currencySymbolAlign – String. The alignment of the currency symbol. It can be "left" or "right". The default is "left".isPercent – Boolean. It allows to format data as percentage. The behavior is the same as in Excel. The default is false. Set isPercent to true and numbers will be multiplied by 100 and % symbol will be added. For example, 0.56 will be changed to 56%. Please note, if % is set as currencySymbol, setting isPercent to true will not multiply numbers by 100.nullValue – String. It defines how to show null values in the grid. The default is "".infinityValue – String. It defines how to show infinity values in the grid. The default is "Infinity".divideByZeroValue – String. It defines how to show divided by zero values in the grid. The default is "Infinity".textAlign – String. The alignment of formatted values in cells on the grid: "right" or "left". The default is "right".Flexmonster component has a built-in default number format which is applied to all measures by default. It is composed of the default values of the number format properties. The default format can be overridden in a report.
If you want to override the default number format for a report, please define a number format with an empty string name property in a report, as follows:
{
dataSource: {
filename: "data.csv"
},
formats: [
{
name: "",
thousandsSeparator: " ",
decimalSeparator: ".",
decimalPlaces: -1,
maxDecimalPlaces: -1,
maxSymbols: 20,
currencySymbol: "",
currencySymbolAlign: "left",
isPercent: "false",
nullValue: "",
infinityValue: "Infinity",
divideByZeroValue: "Infinity",
textAlign: "right"
}
],
slice: {
rows: [
{ uniqueName: "Country" }
],
columns: [
{ uniqueName: "[Measures]" }
],
measures: [
{
uniqueName: "Price",
aggregation: "sum",
active: true
},
{
uniqueName: "Quantity",
aggregation: "sum",
active: true
}
]
}
}
See the example on JSFiddle.
A number format can be applied to a specific measure or measures. Each measure has only one format but a format can be applied to more than one measure.
For example, if you are visualizing financial data, you may want to apply currency formatting to some of the measures in addition to the default format. To apply the format to the specific measure, two things should be done:
When you define some properties in the default format they will be applied to all other formats. In the following example each measure with number formats currency and amount will have thousandsSeparator: ”,”, because it was defined in the default format:
{
dataSource: {
filename: "http://www.flexmonster.com/download/data.csv"
},
formats: [
{
name: "",
thousandsSeparator: ","
},
{
name: "currency",
currencySymbol: "$"
},
{
name: "amount",
decimalPlaces: 0,
currencySymbol: " pcs.",
currencySymbolAlign: "right"
}
],
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"
}
]
}
}
Open the example on JSFiddle.
Please note that a format can be defined for the measure(-s) even if they are not active (active property is false) in a default slice.
Please use Format > Format cells in Toolbar to change/define number formatting for measures in run time.
The number format will be applied to the measures and will be saved within the report.
API calls setFormat() and getFormat() are used to manipulate number formatting in run time.
If you already have formats defined for measures in OLAP cube and you want to use the formatted values from the cube, please set useOlapFormatting report property to true to enable this (it is turned off by default), as follows:
{
dataSource: {
dataSourceType: "microsoft analysis services",
proxyUrl: "http://olap.flexmonster.com/olap/msmdpump.dll",
cube: "Adventure Works",
catalog: "Adventure Works DW Standard Edition"
},
slice: {
rows: [
{uniqueName: "[Product].[Category]"},
{uniqueName: "[Reseller].[Business Type]"}
],
columns: [{uniqueName: "[Measures]"}],
measures: [{uniqueName: "[Measures].[Reseller Order Count]"}]
},
options: {
useOlapFormatting: true
}
}
Check out on JSFiddle.
Please note that useOlapFormatting is supported for Microsoft Analysis Services via both Accelerator and XMLA, and for Mondrian via Accelerator. It is not available for Mondrian via XMLA and for icCube.