The numberFormat property specifies the format of numeric labels using one of the following:
Use numberFormat: 'auto' to let the charting engine choose the number format based on the chart data. Prefix and suffix characters can be added to the chosen format by enclosing 'auto' in two sets of braces. For example, the following indicates automatic number formatting with a tilde (~) and a dollar sign ($) to the left of the number, and a plus sign (+) to the right:
numberFormat: '~${{auto}}+'
The following request uses automatic number formatting for the y-axis and the data text. The y-axis numbers are displayed prefixed with a dollar sign ($), and the data text labels are displayed prefixed with a tilde and dollar sign (~$) and followed by a plus sign (+):
GRAPH FILE WFLITE SUM COGS_US GROSS_PROFIT_US ACROSS PRODUCT_CATEGORY ON GRAPH HOLD FORMAT JSCHART ON GRAPH SET LOOKGRAPH VBAR ON GRAPH SET STYLE * *GRAPH_JS yaxis: {labels: {font: 'bold 10pt Sans-Serif', color: 'red'}, numberFormat: '${{auto}}' }, dataLabels: {visible:true,font: 'bold 8pt Sans-Serif', color: 'teal', numberFormat: '~${{auto}}+'}, series: [{series:'all',showDataValues:true}, {series: 0, color: 'burlywood'}, {series: 1, color: 'lightblue'}, {series: 2, color: 'lightgreen'} ] *END ENDSTYLE END
The output is:
The JSON object includes the following properties. You can omit properties for which you want to accept the default value:
numberFormat: { mode: 'string', thousandSep: 'string', decimalSep: 'string', decimalPlaces: number, grouping: 'string', prefix: 'string', suffix: 'string',
}
where:
Identifies the type of number. Valid values are:
Specifies a character to separate values above and in multiples of one thousand, for example, a comma (,), as in 1,000,000. The default depends on your number format settings.
Specifies a character to separate decimals (for example, a decimal point (.), as in 1.00). The default depends on your number format settings.
Specifies the number of decimal places (number of digits to show to the right of the decimalSep character). The default is 2 for currency and scientific, and zero for numeric and percent.
Specifies how to group large numbers if you do not want to display all of the digits. Valid values are:
Specifies one or more characters to add to the front of the label.
Specifies one or more characters to add to the end of the label.
The following request formats the y-axis labels as currency, grouped by thousands, with no decimal places, so they display with an automatic dollar sign ($) and the letter K:
GRAPH FILE WFLITE
SUM COGS_US GROSS_PROFIT_US
ACROSS PRODUCT_CATEGORY
ON GRAPH HOLD FORMAT JSCHART
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET STYLE *
*GRAPH_JS
yaxis: {labels: {font: 'bold 10pt Sans-Serif', color: 'red'},
numberFormat: {mode: 'currency',decimalPlaces: 0, grouping: 'K'}
},
series: [
{series: 0, color: 'burlywood'},
{series: 1, color: 'lightblue'},
{series: 2, color: 'lightgreen'}
]
*END
ENDSTYLE
END
The output is:
How to: Reference: |
You can specify the format of numeric labels using a format string:
numberFormat: 'string'
The string must be in the format defined by the Custom Numeric Format Strings defined at:
http://msdn.microsoft.com/en-us/library/0c899ak8(vs.71).aspx.
A number formatting string can consist of from one to three sections. If it has one section, the formatting string is used to format all numbers. If it has two sections, the first is used to format positive numbers and zeros, and the second is used to format negative numbers. If it has three sections, the first is used to format positive numbers, the second is used to format negative numbers, and the third used to format zeros.
The sections are separated by semicolons (;). If the second section is empty (there are two consecutive semicolons between the first and third sections), the first section is used to format all non-negative numbers.
If a negative value is rounded and becomes zero, it is formatted as a zero value.
The following are valid within a format string:
Zero (0) or double zero (00)
If the value being formatted has a digit in the position where the 0 appears in the format string, then that digit is copied to the result string. Otherwise, a zero is placed in that position. The position of the leftmost 0 before the decimal point and the rightmost 0 after the decimal point determine the range of digits that are always present in the result string.
The 00 placeholder causes the value to be rounded to the nearest digit preceding the decimal. Rounding is always in the direction away from zero. For example, formatting 34.5 with '00' would result in the value 35.
Pound sign (#) or double pound sign (##).
If the value being formatted has a digit in the position where the # appears in the format string, that digit is copied to the result string. Otherwise, nothing is stored in that position in the result string. Note that this specifier never displays the 0 character if it is not a significant digit, even if 0 is the only digit in the string.
The ## format string causes the value to be rounded to the nearest digit preceding the decimal, where rounding is always away from zero. For example, formatting 34.5 with '##' would result in the value 35.
Depends on the number formatting settings. for American English, it is a decimal point (.).
The first decimal separator character in the format string determines the location of the decimal separator in the formatted value. Any additional decimal separator characters are ignored.
Depends on the number formatting settings. for American English, it is a comma (,).
The thousands separator character serves two purposes. First, if the format string contains a thousands separator character between two digit placeholders (0 or #) and to the left of the decimal separator, if one is present, then the output will have thousand separators inserted between each group of three digits to the left of the decimal separator.
Second, if the format string contains one or more thousand separator characters immediately to the left of the decimal point, then the number will be divided by the number of those characters, multiplied by 1000 before it is formatted. For example, the following format string format the number 100,000,000 as simply 100.
0,,
Use of the thousand separator character to indicate scaling does not include thousand separators in the formatted number. Thus, to scale a number by 1 million and insert thousand separators you would use the following format string :
#,##0,,
E0, E+0, E-0, e, e+0, or e-0
If any of these strings are present in the format string and are followed immediately by at least one 0 character, then the number is formatted using scientific notation with an 'E' or 'e' inserted between the number and the exponent. The number of 0 characters following the scientific notation indicator determines the minimum number of digits to output for the exponent. The E+ and e+ formats indicate that a sign character (plus or minus) should always precede the exponent. The E, E-, e, or e- formats indicate that a sign character should only precede negative exponents.
Depends on the number formatting settings. for American English, it is a percent symbol (%).
The presence of a percentage placeholder character in a format string causes a number to be multiplied by 100 before it is formatted. The appropriate symbol is inserted in the number itself at the location where the percentage placeholder character appears in the format string.
Characters enclosed in single or double quotation marks. For example, 'ABC'.
Characters enclosed in single or double quotation marks are copied to the result string literally.
In addition to the format specifiers defined in this section, you can also use the following characters:
The following request uses a number format string for the y-axis labels and for the data labels. The series object enables data labels. The y-axis object specifies a format string that changes the thousands separator to a point (.) and the decimal separator to a comma (,) using the {t} and {d} syntax. The dataLabels object makes the labels visible and uses the default number format options:
GRAPH FILE WFLITE
SUM COGS_US GROSS_PROFIT_US
ACROSS PRODUCT_CATEGORY
ON GRAPH HOLD FORMAT JSCHART
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET STYLE *
*GRAPH_JS
series: [ {series: 'all', showDataValues: true} ],
yaxis: {labels: {font: 'bold 10pt Sans-Serif', color: 'red'},
numberFormat: '{t.}{d,}#,#.#'},
dataLabels: {visible: true, position:'top',font: 'bold 8pt Sans-Serif', color: 'teal'}
*END
ENDSTYLE
END
The output is:
You can make number formats conditional by defining number formats for ranges of values, using the following relational operators:
Each range definition must be enclosed within a left bracket ([) and a right bracket (]).
If you use the string format to specify the range of data to which the number format is applied, make sure the format string includes the entire range of data. Separate each range and format from the next with a semicolon (;). For example, the following defines the format:
numberFormat:'[<=9]#,#;[<0]-#.#;[=0]#;[<0]-#.#;[>9]#.#'
The following request computes a fraction. The series object enables making the data labels visible. The dataLabels object makes the data labels visible and uses red (the default) for negative values. It also sets number formats as follows. If the number is:
GRAPH FILE WFLITE
SUM DAYSDELAYED NOPRINT
AND COMPUTE
COMPUTE PCT1 = IF DAYSDELAYED NE 763 THEN (500-DAYSDELAYED)/100 ELSE 0;
ACROSS PRODUCT_CATEGORY
ON GRAPH HOLD FORMAT JSCHART
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET STYLE *
*GRAPH_JS
series: [{series: 'all', showDataValues: true} ],
dataLabels: {visible: true, useNegativeColor: true,
font: 'bold 10pt Sans-Serif', color: 'navy',
numberFormat: '[>=0]#.##%;[<0]-#.#%;[=0]0'}
*END
ENDSTYLE
END
The output is:
The function takes one argument (the number to format) and returns a string. For example:
// Return number with a '$' in front numberFormat = function(n){ return '$' + n; };
The following request uses a function to divide the yaxis label by one thousand and return it prefixed with a dollar sign ($) and followed by the letter K:
GRAPH FILE WFLITE
SUM COGS_US GROSS_PROFIT_US
ACROSS PRODUCT_CATEGORY
ON GRAPH HOLD FORMAT JSCHART
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET STYLE *
*GRAPH_JS
yaxis: {
numberFormat: function(n){ return '$' + n/1000 + 'K';}
}
*END
ENDSTYLE
END
The output is:
How to: |
The autoNumberFormats properties control the number formatting of data labels, stacked total labels, pie total labels, and tooltips.
autoNumberFormats: { object: 'numberfmt', ... }
where:
Is one of the following objects.
Multiple objects can be included, separated by commas.
Is a valid number format.
The following request generates a treemap.
GRAPH FILE WFLITE SUM GROSS_PROFIT_US COGS_US BY PRODUCT_CATEGORY BY BUSINESS_REGION ON GRAPH HOLD FORMAT JSCHART ON GRAPH SET LOOKGRAPH TREEMAP ON GRAPH SET STYLE * *GRAPH_JS legend: {visible:true}, *END ENDSTYLE END
The default tooltip number format is shown in the following image.
The following version of the request specifies a number format for the tooltips that includes a dollar sign, a thousands separator, and a decimal separator with two decimal places.
GRAPH FILE WFLITE
SUM GROSS_PROFIT_US COGS_US
BY PRODUCT_CATEGORY
BY BUSINESS_REGION
ON GRAPH HOLD FORMAT JSCHART
ON GRAPH SET LOOKGRAPH TREEMAP
ON GRAPH SET STYLE *
*GRAPH_JS
legend: {visible:true},
autoNumberFormats:{tooltip:'$#,###.##'}
*END
ENDSTYLE
END
The new tooltip number format is shown in the following image. Note that decimal places are not displayed if they have the value zero.
Information Builders |