8 Formatting Analyses, Views, and Dashboard Pages
Topics
This chapter includes the following sections:
What Can be Formatted?
After you create and run an analysis, default formatting rules are applied to the analysis' results.
Default formatting rules are based on cascading style sheets and XML message files. You can create additional formatting to apply to specific results. Additional formats help you to highlight blocks of related information and call attention to specific data elements. You can also use additional formatting to customize the general appearance of analyses and dashboards.
You can apply formatting to the following:
-
Columns in analyses. See Applying Formatting to Columns in Analyses
-
Views. See Applying Formatting to Views
-
Dashboard pages. See Applying Formatting to Dashboard Pages
Applying Formatting to Columns in Analyses
As you work with columns in an analysis, you can specify their formatting.
The formatting that you apply is visible when the column is displayed in views such as tables and pivot tables. See Formatting Columns in Analyses.
Applying Formatting to Views
There are multiple ways to apply formatting to views.
You can apply formatting to views in the following ways:
-
In the View editor: When you edit a view in its editor, you can generally display various dialogs that allow you to format either the view itself or its pieces, as applicable. Formatting options are unique to the view type. Formatting that you specify in the view editor overrides any formatting that was specified for columns.
-
In the Compound Layout: When you use the Compound Layout, you can click a button on the toolbar of the container for each view to display a formatting dialog. Use that dialog to apply formatting to the view's container, such as to place a green background on the container for a table.
Applying Formatting to Dashboard Pages
You can apply formatting to the page layout columns and sections of dashboard pages.
When you edit a dashboard page, you can edit the properties of its columns and sections and apply cosmetic formatting. For information, see What is Cosmetic Formatting? and Building and Using Dashboards.
Formatting Columns in Analyses
Applying specific formatting to columns helps bring attention to data when it meets criteria.
When you build an analysis, you can edit properties for columns to control their appearance and layout. You can also specify formatting to apply only if the contents of the column meet certain conditions. By default, the specifications for a column apply only to the current analysis.
If your account has the appropriate privileges, then you can save the user-specified column properties as the systemwide default settings to use every time that data from that column, or columns of that particular data type, is displayed in results. The properties that you can save as defaults include formatting ones and other properties such as those that affect interactions and data write back.
Because groups and calculated items are simply members of their respective columns, they inherit the formatting from the column and follow the same precedence order as for other members, unless you specifically apply formatting to the groups or calculated items.
This section contains the following topics:
What Kinds of Formatting Can I Apply?
You can use the tabs in the Column Properties dialog to customize how content is displayed for columns in views. Hierarchy levels provide an additional detail of formatting for hierarchical columns.
The following list provides examples of the kinds of formatting that you can apply:
-
Apply cosmetic formatting to the column or hierarchy level, such as font family and size.
-
Assign alternate folder and column heading names and apply custom formatting to them.
-
Control the display of duplicate data, such as repeating column labels.
-
Override the default formatting for columns and data values with custom formatting.
-
Apply conditional formatting to the column or hierarchy level, which enables different formatting to be applied based on the values. For example, values greater than $1 million can be formatted with a green color.
Applying Formatting to a Column
You can set default formatting for columns.
You can override the default settings by formatting columns in editors for data views, such as tables.
- Open the analysis in which you want to edit formatting and behavior in the Criteria tab of the Analysis editor, and then:
- Add or locate the column or hierarchy level to modify. (Expand a hierarchical column to see its levels.)
- Click the Options button to the right of the column name or hierarchy level name in the Selected Columns pane, and click Column Properties or Hierarchy Level Properties.
- In the Column Properties dialog, click the Style tab and specify how you want each cell and its contents to be displayed in the analysis. For example, change the cell border to red and the cell content to be displayed in a 14-point Arial font.
- In the Column Properties dialog, click the Column Format tab to specify various properties such as those for heading names and value suppression.
- Click the Data Format tab to specify how you want to override the data's default display characteristics. The options that display on this tab depend upon the data type (text, date and time zone, or numeric).
- Click the Conditional Format tab to add conditional formatting to a column or hierarchy level.
- If you accessed the Column Properties dialog from the Criteria tab, you can optionally click Save as Default and click the appropriate option to restore or to save defaults, if you have the appropriate privileges.
- Click OK to save your changes.
Saving Formatting Defaults
You can override column formatting defaults using the provided style sheet.
When you use the tabs in the Column Properties dialog to customize how content is displayed for columns in views, you override the Oracle Business Intelligence style sheet and the system defaults for the column or hierarchy level.
You have the option of saving the modified properties for just the column or hierarchy level within the analysis, of saving the properties as systemwide defaults for the data type, or of saving the properties as systemwide defaults for the column or hierarchy level so that its formatting and behavior is the same no matter in which analysis it displays. Anyone who uses this column or hierarchy level or a column or hierarchy level of this data type in subsequent analyses therefore uses the settings in the Column Properties dialog by default. To save systemwide properties, you must have the appropriate privileges.
The ability to set a systemwide default can provide consistency and save time within your organization. For example, suppose that your organization had decided to use Arial as the font family for all text columns in all analyses. Suppose that a decision is later made to switch to Times New Roman for all text columns. You can simply save Times New Roman as the systemwide default for text columns. All existing analyses that contain text columns in which the font family is specified as Default (Arial) are updated automatically.
What is Cosmetic Formatting?
Cosmetic formatting affects the visual appearance of data in columns and hierarchy levels, views, and columns and sections on dashboard pages.
You can apply cosmetic formatting, copy and paste cosmetic formatting attributes, and save a formatted analysis to use with the columns of another analysis.
You can display various dialogs that provide access to and control over various cosmetic attributes. Depending on the object that you are formatting, the dialog displays different options, such as font, cell, and border controls, background color, additional formatting options such as cell padding, and custom CSS style options for HTML.
Using a Saved Analysis to Modify the Cosmetic Appearance of Other Analyses
You can import the formatting from a saved analysis into other analyses.
After you have customized the cosmetic appearance of an analysis using the Criteria tab and the Results tab and have saved it, you can import the formatting from the saved analysis and its views to new or existing analyses. Some views, such as gauge, graph, heat matrix, performance tile, and treemap do not support formatting by importing. This section contains the following topics:
Where is the Import Formatting From Another Analysis Button?
You can use a saved analysis to modify the cosmetic appearance of other views by clicking the Import formatting from another analysis button.
This button is available on the following toolbars:
-
The Compound Layout toolbar — When you click this button, the formatting is applied to all applicable views in the layout. See Applying Formatting from Containers.
-
The view editor toolbar — When you click this button, the formatting is applied only to the view that you are editing. See Which View Types Support Imported Formatting?.
Which View Types Support Imported Formatting?
Some view types support formatting imported from other views.
The following list describes the components that support the use of a saved analysis for formatting other views:
-
View Types — You can import formatting from the following view types:
-
Legend — Imports formatting for the legend title, the caption, and the legend container. Textual content is not imported.
-
Narrative — Imports only the text font color. Textual properties such as prefix, postfix, and narrative texts are not imported.
-
Pivot table, table, and trellis — Imports formatting for the columns, green bar specifications, sub-totals and grand totals, and section properties.
-
Static Text — Imports only the text font color. Textual content is not imported.
-
Title — Imports formatting for the title, logo, subtitle, start time, and help URL.
-
View Selector — Imports only the caption formatting.
-
-
Compound Layout — You can import formatting that was specified after clicking the Format Container button for the view in the Compound Layout.
How is Imported Formatting Applied?
Imported formatting is applied differently than local formatting.
Formatting is applied slightly differently depending on whether you are importing formatting that was applied to columns, to views, or to view containers in the Compound Layout.
Applying Formatting from Columns
This functionality works best for views when attribute, measure, or hierarchical column numbers align precisely or for a single column template that can be applied to all columns.
If a single column exists in the saved analysis, then its formatting is applied to all columns in the tables, pivot tables, and trellises of the target analysis.
For multiple columns, formatting is applied positionally, from left to right for column headings and values. A column that exceeds the number in the saved analysis gets the closest column's format repeated. For example, suppose the saved analysis contains four columns formatted with these colors in this order: red, green, blue, yellow. In the target analysis, its six columns would acquire these colors in this order: red, green, blue, yellow, yellow, yellow.
Applying Formatting from Views
Formatting is applied to a view only if a view of that type exists in the target analysis.
The imported formatting applies to all views of that type in the target analysis. For example, suppose that a saved analysis contains a legend to which you have customized formatting. If you import that formatting into a target analysis that contains three legends, then all three legends inherit that formatting.
Applying Formatting from Containers
In the Compound Layout, you can specify formatting properties (such as background color, borders, and padding) for view containers.
When you import that container formatting using a saved analysis, the views in the target analysis inherit the exact container formatting properties as the views in the saved analysis.
The layout of the views in the two analyses need not be exactly the same for container formatting to work correctly. If the target analysis contains more views than the source analysis, then the extra views inherit the imported formatting. For example, suppose a saved analysis contains two tables that are stacked on top of each other in the Compound Layout. Suppose the target analysis contains four tables that are laid out two by two. The source analysis has only one layout column of two table views. The two tables in each of the first and second layout columns of the target analysis inherit the applied formatting.
Importing Formatting Using a Saved Analysis
Using a saved analysis to import formatting helps ensure consistency across analyses.
You can use the format of a saved analysis like a template.
- Display either the analysis in which you want to import formatting in the Results tab of the Analysis editor, or display the view in its editor.
- Click the Import formatting from another analysis button.
- In the Select Analysis dialog, navigate to the saved analysis and click OK.
Applying Conditional Formatting to Tables, Pivot Tables, Performance Tiles, Graphs, Heat Matrixes, and Trellises
In tables, pivot tables, performance tiles, graphs, heat matrixes, and trellises conditional formatting helps direct attention to a data element if it meets a certain condition.
For example, you can show below-quota sales figures in a certain color, or display an image such as a trophy next to the name of each salesperson who exceeds quota by a certain percent.
This section describes how to apply conditional formatting in tables, pivot tables, performance tiles, heat matrixes, and trellises. See Graph Formatting Based on Columns. This section contains the following topics:
How is Conditional Formatting Applied?
You apply conditional formatting by selecting one or more columns or hierarchy levels in the analysis to use, specifying the condition to meet, and then making specifications for font, cell, border, and style sheet options to apply when the condition is met.
The conditional formats can include colors, fonts, images, and so on, for the data and for the cell that contains the data. Your specifications apply only to the contents of the columns or hierarchy levels in the tables and pivot tables for the analysis with which you are working.
You can add multiple conditions so that the data is displayed in one of several formats, based upon the value of the data. For example, below-quota sales can be displayed in one color, and above-quota sales can be displayed in another color. When you specify multiple conditions, all the conditions are verified and the formats are merged for the conditions that are true. In the event of a conflict when trying to merge multiple formats, the condition that is last verified as true affects the format that is displayed.
Conditional formatting is not supported for direct database requests.
Can Conditional Formats Be Based on Another Column?
You can create conditional formats to apply to one column based on the values of a second column, for display in tables, pivot tables, and trellises.
For example, you can create a conditional format to color the D50 Region column green when values of the 1 - Revenue column are greater than $9 million, as shown.
You can create a condition using a column that is not displayed in views for the analysis, if you use the Hide option on the Column Format tab of the Column Properties dialog. If you place the column in the Excluded drop target of the Layout pane, then you cannot create a condition using that column.
Conditional formatting is applied based on the underlying value, even if you select the Show Data As options in the Layout pane to show the data as percentages or indexes.
Performance tiles cannot inherit conditional formatting based on another column, nor do performance tiles have an Excluded drop target.
What Factors Affect Conditional Formats?
Conditional formats are affected by a variety of factors.
The way that conditional formats are applied to columns depends on the factors that are described in the following list:
Layout, Order, and Conditional Formats
The layout of the columns in the view affects the conditional formatting of the values of one column when the conditional format is based on another column.
Changing the layout of the columns in the view can change the display of the conditional formats. For example, suppose that you specify a conditional format on the D50 Region column where T05 Per Name Year is 2009. If D50 Region and T05 Per Name Year are on opposite edges of the pivot table, then no conditional formatting is visible.
The order of the columns as they are displayed in the view also affects conditional formatting. The order in the view affects the "grain" at which the values are displayed. You can think of "grain" as a level of aggregation. The conditional format applies when the column to format is displayed at a finer grain or the same grain as the column on which the format is based. If the column being formatted is of a higher grain than the column on which the format is based, then the format applies only if the condition is based on a measure column. If the format is based on an attribute column and the column to format is displayed at a higher grain, then the conditional format does not apply.
For example, consider the table in the illustration that follows. A conditional format has been specified to color the D50 Region when T05 Per Name Year is 2011. Notice that no light blue coloring is visible because D50 Region is on a different edge than T05 Per Name Year. See Value Suppression and Conditional Formats.
Suppose that you change the order of the columns so that T05 Per Name Year is the first column in the table. Then the T05 Per Name Year column is displayed at a higher grain, and the appropriate conditional formatting is applied to the D50 Region column. The illustration that follows shows a pivot table with the appropriate conditional formatting.
Conditional formats can be displayed on any edge of the table, pivot table, or trellis. On the Prompts drop target (also known as the "page edge"), the conditional format applies only to the column member that is currently selected for that target. For example, suppose that you have a D50 Region column that has the members AMERICAS, APAC, and EMEA. Suppose the D50 Region column is on the Prompts drop target for a pivot table and the conditional format is set to color the region name green if 1 - Revenue is greater than $6.5 million. If AMERICAS is the only region that meets that condition, then AMERICAS is colored green only when it is selected for the Prompts drop target.
Value Suppression and Conditional Formats
For tables, the value suppression setting in the Column Format tab of the Column Properties dialog affects conditional formatting.
If you set value suppression to Repeat, then the column that you are formatting is displayed at the detail grain of the view. No aggregation is needed on the column on which the format is based for applying the conditional format.
For example, consider the table in the first illustration. A conditional format has been applied to color the D50 Region column light blue when T05 Per Name Year is 2011. Notice that no light blue coloring is visible, because the value suppression is set to Suppress, which does not allow for repeating column values for the members of D50 Region.
If the value suppression is set to Repeat, then column members are repeated and the appropriate conditional formatting is applied. The following illustration shows a table with repeat value suppression.
Applying Conditional Formatting
You can apply specified properties to analyses when specific conditions are met.
Conditional formatting allows you to bring attention to data based on specified conditions, such as making a negative number red bold text.
Example of Conditional Formatting for Ranking
This example describes how conditional formatting can be applied to results to show ranking.
Suppose an analysis includes ten ranking categories, with a value of 1 in the column indicating the worst ranking, and a value of 10 indicating the best ranking. You could create three conditional formats to show the following:
-
One image to indicate a low rank for columns that contain 1, 2, or 3.
-
Another image to indicate an average rank for columns that contain 4, 5, 6, or 7.
-
A third image to indicate a high rank for columns that contain 8, 9, or 10.
In the Graphics dialog, selecting the Images Only image placement option would cause the columns to be displayed with only images, and not the ranking numbers, in the results.
Storing Custom Files Locally and Using the fmap Function to Reference Them
You can store custom files, such as images and help files, locally in your Oracle Business Intelligence environment and then reference them using the fmap function.
For example, you can store an image locally to display in a title view. Then, when you specify the image for the title view in the Title editor, you can use the fmap function to reference it.
ORACLE_HOME\bi\bifoundation\web\appv2
See Approach 2: Deploying Using Shared Folders.You can store files directly in this directory or its subdirectories. Because the analyticsRes directory gets replicated for every instance that is populated, you must replicate the custom files in each instance. It is strongly recommended that you use ASCII characters for names of custom files that you reference using the fmap syntax.
Note:
Store logo images and graphics are located in theanalyticsRes directory in the same folder.analyticsRes directory, use the following fmap syntax:
fmap:location/file_name
where:
-
location/ is the subdirectory path in the
analyticsResdirectory in which the custom file resides, if the custom file resides in a subdirectory ofanalyticsRes(for example:Images/). -
file_name is the name of the custom file (for example:
newlogo.gif).
The following are examples of fmap commands:
fmap:mylogo.gif fmap:Images/newlogo.gif
If you are trying to save an object that uses the fmap syntax, then you must have the Save Content with HTML Markup privilege. See Security Guide for Oracle Business Intelligence Enterprise Edition for information on privileges.
Custom Format Masks
A custom format mask provides additional options for formatting a column that contains numeric data (for example, revenue or billed quantity).
To set a custom format mask for a column that contains numeric data, you use the Treat Numbers As field in conjunction with the Custom Numeric field. See the Data Format tab of the Column Properties dialog for additional information.
The following table shows the mask character and a description. The third column is a specific example of a numeric field that uses the mask for the United States with a language-base of English.
| Mask Character | Description | Example |
|---|---|---|
|
# |
Use a pound sign or hash mark for an optional digit (preceding and trailing zeroes will be removed) |
Specifying #,###,##0.## as the mask for 12345.67, yields 12,345.67. |
|
0 |
Use a zero for a required digit |
Specifying #0.##0 as the mask for 12.34, yields 12.340. |
|
" " |
Use double quotations to display anything verbatim If you use the double quotations mask character to represent numeric data, you must remove the explicit mask character prior to exporting the results of the analysis to Excel because Excel cannot translate the mask character. |
Specifying "++++" as part of a mask, yields ++++. |
|
; |
Use a semicolon to separate positive, negative, and null formats |
Specifying "+"#0.000;"-"#0.000;"nulls" as the mask for 467482.18, yields +467482.18. See the illustration that follows. |
|
[$] |
Use as a locale-specific currency symbol |
Specifying [$]#,##0.## as the mask for 5000.48, yields $5,000.48 for a user having English-United States selected as the locale, and £5,000.48 if English-United Kingdom is selected as the locale. |
The decimal and thousands separator characters that you use in the Custom Numeric field to define the format mask are always the:
-
Period — Used to separate the whole part from the fractional part of a number
-
Comma — Used to separate thousands
However, the separator characters that are actually displayed in analyses and dashboards are dependent upon the country and language in which you are running Oracle BI EE.
The following table shows examples for specific countries and language combinations. The examples shown in this table are not all inclusive, and are correct at the time of writing.
| Format to Display | Use This Format Mask in BI EE | Pre-defined Country (Language) Separator Character | Typically Used in This Country (Language) |
|---|---|---|---|
|
Decimal: 123,45 Thousands: 6.789 |
Decimal: ###.## Thousands: #,### |
Decimal: , (comma) Thousands: . (period) |
Argentina (Spanish), Denmark (Danish), Ecuador (Spanish), Germany (German), Greece (Greek), Italy (Italian), and Liechtenstein (German) |
|
Decimal: 123.45 Thousands: 6,789 |
Decimal: ###.## Thousands: #,### |
Decimal: . (period) Thousands: , (comma) |
Australia (English), Egypt (Arabic), Guatemala (Spanish), Israel (Hebrew), Japan (Japanese), Singapore (Chinese), and the United States (English) |
|
Decimal: 123.45 Thousands: 6'789 |
Decimal: ###.## Thousands: #,### |
Decimal: . (period) Thousands: ' (apostrophe) |
Switzerland (French, German, and Italian) |
|
Decimal: 123,45 Thousands: 6 789 |
Decimal: ###.## Thousands: #,### |
Decimal: , (comma) Thousands: (space) |
Finland (Finish), Poland (Polish), and Sweden (Swedish) |
A custom format mask allows you to override both the negative and null format by providing separate masks that are separated by semicolons. A fully defined mask might appear as positivemask;negativemask;nullmask. See the example shown.
-
If you omit the null mask, then a blank displays for a null value.
-
If you omit the negative mask, then the positive mask displays for the negative mask.
-
You must use a negative mask, if you want to use a null mask.
-
If you use all #'s as the mask, the value zero (0) displays as blank.
-
In Excel, a fully defined mask might appear as positivemask;negativemask;zeromask;text. When you export a column that uses a null mask, the null mask is dropped because Excel does not support it. In addition, Excel's zero mask is not used as Oracle BI EE does not support a zero mask.
The example illustration shows a table of revenue that includes a year ago revenue calculation and a quarterly compounded yearly growth calculation by year and quarter for the Americas. The same custom format mask was used for all measure columns, and the text nullwas set to display for the measure calculation. See Understanding Null Suppression. The custom format mask used in this table for the three columns is: "+"#0.000;"-"#0.000;"null".
-
A plus sign with a two decimal position mask displays for a positive number.
-
A minus sign with a two decimal position mask displays for a negative number.
-
If the value of the measure is null, the word null displays.
-
Open the analysis in which you want to edit formatting in the Criteria tab of the Analysis editor.
-
Add or locate the column to modify.
-
Click the Options button to the right of the column name in the Selected Columns pane.
-
Click Column Properties. The Column Properties dialog is displayed.
-
Click the Data Format tab of the Column Properties dialog.
-
Select the Override Default Data Format check box.
-
Select Custom from the Treat Numbers As list.
-
Enter the mask for the numeric field in the Custom Numeric Format text box and click the OK button.
Custom Format Strings
Custom format strings provide additional options for formatting columns or hierarchy levels that contain time stamps, dates, and times.
To set custom format strings to a column or hierarchy level, you use the Custom Date Format field (for date and time fields) or the Custom Numeric Format field (for numeric fields) in the Data Format tab of the Column Properties dialog.
This section contains the following topics:
Custom Format Strings for Date and Time Fields
You can set custom format strings to a column or hierarchy level in the Custom Date Format field in the Data Format tab of the Column Properties dialog.
This section contains the following topics:
General Custom Format Strings
You can create custom time or date formats using these strings.
The table shows the general custom format strings and the results that they display. These allow the display of date and time fields in the user's locale.
| General Format String | Result |
|---|---|
|
[FMT:dateShort] |
Formats the date in the locale's short date format. You can also type [FMT:date]. |
|
[FMT:dateLong] |
Formats the date in the locale's long date format. |
|
[FMT:dateInput] |
Formats the date in a format acceptable for input back into the system. |
|
[FMT:time] |
Formats the time in the locale's time format. |
|
[FMT:timeHourMin] |
Formats the time in the locale's time format but omits the seconds. |
|
[FMT:timeInput] |
Formats the time in a format acceptable for input back into the system. |
|
[FMT:timeInputHourMin] |
Formats the time in a format acceptable for input back into the system, but omits the seconds. |
|
[FMT:timeStampShort] |
Equivalent to typing [FMT:dateShort] [FMT:time]. Formats the date in the locale's short date format and the time in the locale's time format. You can also type [FMT:timeStamp]. |
|
[FMT:timeStampLong] |
Equivalent to typing [FMT:dateLong] [FMT:time]. Formats the date in the locale's long date format and the time in the locale's time format. |
|
[FMT:timeStampInput] |
Equivalent to [FMT:dateInput] [FMT:timeInput]. Formats the date and the time in a format acceptable for input back into the system. |
|
[FMT:timeHour] |
Formats the hour field only in the locale's format, such as 8 PM. |
|
YY or yy |
Displays the last two digits of the year, padded to the left with zero, if necessary, for example, 01 for 2001. |
|
YYY or yyy |
Displays the four- digit year, padded to the left with zero, if necessary, for example, 0523. |
|
YYYY or yyyy |
Displays the four- digit year, for example, 2011 |
|
M |
Displays the numeric month, for example, 2 for February. |
|
MM |
Displays the numeric month, padded to the left with zero for single-digit months, for example, 02 for February. |
|
MMM |
Displays the abbreviated name of the month in the user's locale, for example, Feb. |
|
MMMM |
Displays the full name of the month in the user's locale, for example, February. |
|
D or d |
Displays the day of the month, for example, 1. |
|
DD or dd |
Displays the day of the month, padded to the left with zero for single-digit days, for example, 01. |
|
DDD or ddd |
Displays the abbreviated name of the day of the week in the user's locale, for example, Thu for Thursday. |
|
DDDD or dddd |
Displays the full name of the day of the week in the user's locale, for example, Thursday. |
|
DDDDD or ddddd |
Displays the first letter of the name of the day of the week in the user's locale, for example, T for Thursday. |
|
h |
Displays the hour in 12-hour time, for example 2. |
|
H |
Displays the hour in 24-hour time, for example, 23. |
|
hh |
Displays the hour in 12-hour time, padded to the left with zero for single-digit hours, for example, 01. |
|
HH |
Displays the hour in 24-hour time, padded to the left with zero for single digit hours, for example, 23. |
|
m |
Displays the minute, for example, 7. |
|
mm |
Displays the minute, padded to the left with zero for single-digit minutes, for example, 07. |
|
s |
Displays the second, for example, 2. You can also include decimals in the string, such as s.# or s.00 (where # means an optional digit, and 0 means a required digit). |
|
ss |
Displays the second, padded to the left with zero for single-digit seconds, for example, 02. You can also include decimals in the string, such as ss.# or ss.00 (where # means an optional digit, and 0 means a required digit). |
|
t |
Displays the first letter of the abbreviation for ante meridiem or post meridiem in the user's locale, for example, a. |
|
tt |
Displays the abbreviation for ante meridiem or post meridiem in the user's locale, for example, pm. |
|
gg |
Displays the era in the user's locale. |
ODBC Custom Format Strings
You can create custom format strings using ODBC.
The table shows the ODBC standard type custom format strings and the results that they display. These strings display date and time fields according to the ODBC standard.
| ODBC Format String | Result |
|---|---|
|
[FMT:dateODBC] |
Formats the date in standard ODBC yyyy-mm-dd format (4-digit year, 2-digit month, 2-digit day). |
|
[FMT:timeODBC] |
Formats the time in standard ODBC hh:mm:ss format (2-digit hour, 2-digit minute, 2-digit second). |
|
[FMT:timeStampODBC] |
Equivalent to typing [FMT:dateODBC] [FMT:timeStampODBC]. Formats the date in yyyy-mm-dd format, and the time in hh:mm:ss format. |
|
[FMT:dateTyped] |
Displays the word date and then shows the date, in standard ODBC yyyy-mm-dd format. The date is shown within single quote characters ('). |
|
[FMT:timeTyped] |
Displays the word time and then shows the time, in standard ODBC hh:mm:ss format. The time is shown within single quote characters ('). |
|
[FMT:timeStampTyped] |
Displays the word timestamp and then the timestamp, in standard ODBC yyyy-mm-dd hh:mm:ss format. The timestamp is shown within single quote characters ('). |
Custom Format Strings for Numeric Fields
Setting custom format strings for numeric fields ensures data is interpreted accurately.
You can set custom format strings to a column or hierarchy level in the Custom Numeric Format field in the Data Format tab of the Column Properties dialog.
When applying a format from a different data type—for example, applying a date type format to a numeric field—if the actual value is out of bounds, then the format is not applied and instead the original value is displayed. As an example, take the format [MMMM]. If the original value to be converted is 13, then that value is out of bounds because there are only 12 months in a year. The original value, 13, is displayed without being converted to a month name. If the original value is 12, however, then applying the custom format converts the value to the 12th month, that is December.
This section contains the following topics:
Custom Format Strings for Integral Fields
You can display month and day values using custom format strings.
The table shows the custom format strings that are available when working with integral fields. These allow the display of month and day names in the user's locale.
Integral fields hold integers that represent the month of the year or the day of the week. For months, 1 represents January, 2 represents February, and so on, with 12 representing December. For days of the week, 1 represents Sunday, 2 represents Monday, and so on, with 7 representing Saturday.
| Integral Field Format String | Result |
|---|---|
|
[MMM] |
Displays the abbreviated name of the month in the user's locale. |
|
[MMMM] |
Displays the full name of the month in the user's locale. |
|
[DDD] |
Displays the abbreviated name of the day of the week in the user's locale. |
|
[DDDD] |
Displays the full name of the day of the week in the user's locale. |
Custom Format Strings for Conversion into Hours
You can convert a variety of time periods into hour formats.
The table shows the custom format strings that you can use to format data into hours. You use these strings on the following kinds of fields:
-
Fields that contain integers or real numbers that represent the time that has elapsed since the beginning of the day (12:00 AM).
-
Fields where the output is in [FMT:timeHour] format. (This format displays the hour field only in the locale's format, such as 8 PM.)
| Data Conversion Format String | Result |
|---|---|
|
[FMT:timeHour] |
This assumes that the value represents the number of hours that have elapsed since the beginning of the day, and formats the number of hours into an hh display, where hh is the number of hours. Fractions are dropped from the value. For example, a value of 2 is formatted as 2 AM, and a value of 12.24 as 12 PM. |
|
[FMT:timeHour(min)] |
This assumes that the value represents the number of minutes that have elapsed since the beginning of the day, and formats the number of minutes into an hh display, where hh is the number of hours. Fractions are dropped from the value. For example, a value of 2 is formatted as 12 AM, and a value of 363.10 as 06 AM. |
|
[FMT:timeHour(sec)] |
This assumes that the value represents the number of seconds that have elapsed since the beginning of the day, and formats the number of seconds into an hh display, where hh is the number of hours. Fractional hours are dropped from the value. For example, a value of 600 is formatted as 12 AM, a value of 3600 as 1 AM, and a value of a value of 61214.30 as 5 PM. |
Custom Format Strings for Conversion into Hours and Minutes
You can convert a variety of time periods into hour and minute formats.
The table shows the custom format strings that you can use to format data into hours and minutes. You use these strings on fields that contain integers or real numbers that represent the time that has elapsed since the beginning of the day (12:00 AM).
They can also be used where the output is in [FMT:timeHourMin] format, described in the table. (This format displays the time in the locale's time format, but omits the seconds.)
| Data Conversion Format String | Result |
|---|---|
|
[FMT:timeHourMin] |
This assumes that the value represents the number of minutes that have elapsed since the beginning of the day, and converts the value into an hh:mm display, where hh is the number of hours and mm is the number of minutes. Fractions are dropped from the value. For example, a value of 12 is formatted as 12:12 AM, a value of 73 as 1:13 AM, and a value of 750 as 12:30 PM. |
|
[FMT:timeHourMin(sec)] |
This assumes that the value represents the number of seconds that have elapsed since the beginning of the day, and converts the value into an hh:mm display, where hh is the number of hours and mm is the number of minutes. Fractions are dropped from the value. For example, a value of 60 is formatted as 12:01 AM, a value of 120 as 12:02 AM, and a value of 43200 as 12:00 PM. |
|
[FMT:timeHourMin(hour)] |
This assumes that the value represents the number of hours that have elapsed since the beginning of the day, and converts the number of hours into an hh:mm display, where hh is the number of hours and mm is the remaining number of minutes. For example, a value of 0 is formatted as 12:00 AM, a value of 1.5 as 1:30 AM, and a value of 13.75 as 1:45 PM. |
Custom Format Strings for Conversion into Hours, Minutes, and Seconds
You can convert a variety of time periods into hour, minute, and second formats.
The table shows the custom format strings that you can use to format data into hours, minutes, and seconds. You use these strings on fields that contain integers or real numbers that represent time.
They can also be used where the output is in [FMT:time] format. SeeGeneral Custom Format Strings. (This format displays the time in the locale's time format.)
| Data Conversion Format String | Result |
|---|---|
|
[FMT:time] |
This assumes that the value represents the number of seconds that have elapsed since the beginning of the day, and converts the value into an hh:mm:ss display, where hh is the number of hours, mm is the number of minutes, and ss is the number of seconds. For example, a value of 60 is formatted as 12:01:00 AM, a value of 126 as 12:02:06 AM, and a value of 43200 as 12:00:00 PM. |
|
[FMT:time(min)] |
This assumes that the value represents the number of minutes that have elapsed since the beginning of the day, and converts the value into an hh:mm:ss display, where hh is the number of hours, mm is the number of minutes, and ss is the number of seconds. For example, a value of 60 is formatted as 1:00:00 AM, a value of 126 as 2:06:00 AM, and a value of 1400 as 11:20:00 PM. |
|
[FMT:time(hour)] |
This assumes that the value represents the number of hours that have elapsed since the beginning of the day, and converts the value into an hh:mm:ss display, where hh is the number of hours, mm is the number of minutes, and ss is the number of seconds. For example, a value of 6.5 is formatted as 6:30:00 AM, and a value of 12 as 12:00:00 PM. |
Custom Format Strings for Displaying Duration
You can convert a variety of time periods to describe a duration of time.
The table shows the custom format strings that you can use to format duration data into days, hours, minutes, and seconds. You use these strings on fields that contain integers or real numbers that represent time.
This allows the display of duration, such as the length of time for a telephone call. You can format a numeric field as DD:HH:MM:SS.
You can add literal values in the string to make the output more meaningful, as in the following example:
- [duration][opt:dd"d":][opt:hh"h":]mm"m":ss"s"
- When input = 15000, output is: 04h:10m:00s
In this way, the literal values h, m, and s represent hours, minutes, and seconds in the output.
Adding [opt:...] anywhere in the string makes the field optional, in that it is not displayed if it has no value. In the example above, [opt:dd] means a value for days is not displayed because it has no value.
| Data Conversion Format String | Result |
|---|---|
|
[duration(sec)][opt:dd]:hh:mm:ss |
Formats the total of seconds as duration. For example, a duration value of 16500.00 is displayed as 04:35:00, meaning a duration of four hours, 35 minutes, and zero seconds. opt:dd displays the number of days, but if opt has no value, it is not displayed. Second is the default unit of time. |
|
[duration(min)][opt:dd]:hh:mm:ss |
Formats the total of minutes as duration. For example, a duration value of 275.00 is displayed as 04:35:00, meaning a duration of four hours, 35 minutes, and zero seconds. opt:dd displays the number of days, but if opt has no value, it is not displayed. |
|
[duration(hour)][opt:dd]:hh:mm:ss |
Formats the total of hours as duration. For example, a duration value of 4.58 is displayed as 04:35:00, meaning a duration of four hours, 34 minutes, and 48 seconds. opt:dd displays the number of days, but if opt has no value, it is not displayed. |
|
[duration(day)][opt:dd]:hh:mm:ss |
Formats the total of days as duration. For example, a duration value of 2.13 is displayed as 02:03:07:12, meaning a duration of two days, three hours, seven minutes, and twelve seconds. opt:dd displays the number of days, but if opt has no value, it is not displayed. |