Specifying number formats
In Design View, right-click the cell or cells to format and select .
Click the Number Format tab.
The Text category
Text is a number category. It provides two number formats, @ and ~. The effects of these number formats depend on whether you apply them to a data cell, or to the key output cell of a hyperblock. In data cells, the formats affect the format of numerical values. In key output cells, the formats affect how element names are displayed.
You can specify different number formats in the database and apply them automatically. For example, if a report displays values for different elements of the Measures dimension, you can automatically format each value according to the type of measure: for example, apply a % format for percentage measures and a currency format for currency measures etc. Those formats can be automatically applied to all values in which those measures are referenced, such as values returned by CELLGET.
To apply automatic formatting, select as the number format. To enable manual formatting, select as the number format.
To implement automatic number formats, the different number formats must be stored in an attribute named Format_String on the Measures dimension.
If you apply the ~ number format to the key output cell of a hyperblock, element names are displayed according to the format that is specified in the Display Value column of the Format Hyperblock dialog. If you apply the @ format, the key output cell displays the unique names of the elements.
SeeHyperblocks.
User-defined formats
To define number formats, select from the Category list and define the format in the User-defined format instruction field. Formats are defined with format codes. To delete a user-defined format, select it in the Number formats list and click .
See format codes
User-defined formats can be used with cell graphics.
See Conditions.
Format codes
A user-defined format can have up to 4 parts, separated by semicolons. The parts specify the formats for positive numbers, negative numbers, null values, and for text.
For example, this format displays positive values in blue and negative values in red:
[blue]#,#;[red]#,#
You can also specify conditions. For example, this format displays values less than 5 in red and values less than 100 in blue:
[<5][red]0;[<100][blue]0
If you only specify two parts, the first is used for positive numbers and null values, the second for negative numbers. If you specify only one part, all numbers use that format. If you skip a part, include its closing semicolon.
Decimal places and digits
| Format Code | Function |
|---|---|
| # | Displays digits only. Null values are not displayed. |
| 0 (Null) | Displays null values if a number has fewer digits than there are nulls in the format. |
| ? | Inserts spaces for nulls on either side of the comma to align decimal numbers along the decimal comma, if a monospace font (e.g. Courier New) is used for formatting. The ? sign can also be used for fractions with a different number of digits. |
Examples:
| Displayed Format | Format Codes |
|---|---|
| 1234.59 as 1234.6 | ####.# |
| 8.9 as 8.900 | #.000 |
| 631 as 0.6 | 0.#, |
| 12 as 12.0 and 1234.568 as 1234.57 | #.0# |
| 44.398, 102.65 and 2.8 with aligned decimal places | ???.??? |
| 5.25 as 5 1/4 and 5.3 as 5 3/10 with aligned division sign | #,???/?? |
The thousands separator
| Displayed Format | Format Codes |
|---|---|
| 12000 as 12,000 | #,### |
| 12000 as 12.000 | #\.### |
| 12000 as 12 | #, |
| 12200000 as 12.2 | 0.0,, |
Colors
To specify a color for a part of the format, enter the color name in square brackets. The colors you can use are Black, Blue, Cyan, Green, Magenta, Red, White, Yellow. The color code must be the first element of the format definition.
Conditions
You can apply different formats when different conditions are met. The
condition consists of a comparison operator and a value. Enclose the condition
in square brackets. For example,
[Red]0[<=0];[Blue]£0.00[>0] displays numbers
less than or equal to zero in red, with no decimal places. It displays numbers
greater than zero in blue and as pounds sterling to two decimal places.
To display a graphic when a condition is met, use this syntax:
[<0][graphic standard_therm_red];[>0][graphic
standard_therm_yellow];[=0][graphic standard_therm_green]
| Name | Graphic |
|---|---|
|
Standard_arrow_down Standard_arrow_downright Standard_arrow_right Standard_arrow_upright Standard_arrow_up |
|
|
Standard_arrow_red_down Standard_arrow_yellow_downright Standard_arrow_yellow_right Standard_arrow_yellow_upright Standard_arrow_green_up |
|
|
Standard_arrow_red_up Standard_arrow_yellow_upright Standard_arrow_yellow_right Standard_arrow_yellow_downright Standard_arrow_green_down |
|
|
Standard_progressbar_red Standard_progressbar_yellow Standard_progressbar_green |
|
|
Standard_gauge_asc_0 Standard_gauge_asc_40 Standard_gauge_asc_50 Standard_gauge_asc_60 Standard_gauge_asc_100 |
|
|
Standard_gauge_desc_0 Standard_gauge_desc_40 Standard_gauge_desc_50 Standard_gauge_desc_60 Standard_gauge_desc_100 |
|
|
Standard_octagon_red Standard_octagon_yellow Standard_octagon_green |
|
|
Standard_emoticon_sad Standard_emoticon_neutral Standard_emoticon_happy |
|
|
Standard_trafficlight_red Standard_trafficlight_yellow Standard_trafficlight_green |
|
|
Standard_bubble_red Standard_bubble_yellow Standard_bubble_green |
|
|
Standard_therm_red Standard_therm_yellow Standard_therm_green |
|
|
Standard_checkbox_selected Standard_checkbox |
|
|
Standard_sort_ascending Standard_sort_descending |
|
|
Standard_lock Standard_unlock |
|
|
Standard_print Standard_empty Standard_error Standard_folder Standard_info Standard_kpi_icon Standard_minus |
|