7 Formatting Analyses, Views, and Dashboard Pages

This chapter explains how to apply formatting to analyses, views, and dashboard pages and contains the following topics:

• "What Can be Formatted?"

• "Formatting Columns in Analyses"

• "What is Cosmetic Formatting?"

• "Applying Conditional Formatting to Tables and Pivot Tables"

• "Custom Date and Time Format Strings"

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, as described in "Applying Formatting to Columns in Analyses".

• Views, as described in "Applying Formatting to Views".

• Dashboard pages, as described in "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. For information, see "Formatting Columns in Analyses".

Applying 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 Chapter 4, "Building and Using Dashboards".

Formatting Columns in Analyses

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 system-wide 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 affects 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?"

• "Applying Formatting to a Column"

• "Saving Formatting Defaults"

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 use the following procedure to set default formatting for columns. You can override the default settings by formatting columns in editors for data views, such as tables.

To apply formatting to a column:

1. Open the analysis in which you want to edit formatting and behavior in the "Analysis editor: Criteria tab".

2. Add or locate the column or hierarchy level that you want to modify. (Expand a hierarchical column to see its levels.)

3. 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. The "Column Properties dialog" is displayed.

4. Click the "Column Properties dialog: Style tab" and specify how you want each cell and its contents to be displayed in the analyses. For example, change the cell border to red and the cell content to be displayed in a 14-point Arial font.

   For more information, see "What is Cosmetic Formatting?"

5. Click the "Column Properties dialog: Column Format tab" to specify various properties such as those for heading names and value suppression.

6. Click the "Column Properties dialog: 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).

   For more information, see "Custom Date and Time Format Strings".

7. Click the "Column Properties dialog: Conditional Format tab" to add conditional formatting to a column or hierarchy level.

   For more information, see "Applying Conditional Formatting to Tables and Pivot Tables".

8. Optionally, click Save as Default and click the appropriate option to restore or to save defaults, if you have the appropriate privileges.

9. Click OK to save your changes.

Saving Formatting Defaults

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 analyses, of saving the properties as system-wide defaults for the data type, or of saving the properties as system-wide defaults for the column or hierarchy level so that its formatting and behavior will be 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 will, therefore, use the settings in the "Column Properties dialog" by default. To save system-wide properties, you must have the appropriate privileges.

The ability to set a system-wide 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 system-wide default for text columns. All existing analyses that contain text columns in which the font family is specified as Default (Arial) will be 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

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. This section contains the following topics:

• "Where is the Import Formatting Button?"

• "Which View Types Support Imported Formatting?"

• "How is Imported Formatting Applied?"

• "Importing Formatting Using a Saved Analyses"

Where is the Import Formatting Button?

You can use a saved analysis to modify the cosmetic appearance of other views by clicking the Import Formatting 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?

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 and table — Imports formatting for the columns, greenbar 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?

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 in the case of 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 and pivot tables 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 already exists in the target analysis. The imported formatting applies to all views of that type in the target analyses. 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 Analyses

To use a saved analysis to modify the cosmetic appearance of another analysis:

1. Display either the analysis in which you want to import formatting in the "Analysis editor: Results tab", or display the view in its editor.

2. Click the Import Formatting toolbar button.

3. In the "Select Analysis dialog", navigate to the saved analysis and click OK.

Applying Conditional Formatting to Tables and Pivot Tables

In tables, pivot tables, and graphs, 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 and pivot tables. For information on graphs, see "Graph Formatting Based on Columns" This section contains the following topics:

• "How is Conditional Formatting Applied?"

• "Can Conditional Formats Be Based on Another Columns?"

• "What Factors Affect Conditional Formats?"

• "Applying Conditional Formatting"

• "Example of Conditional Formatting for Ranking"

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.

Can Conditional Formats Be Based on Another Columns?

You can create conditional formats to apply to one column based on the values of a second column, for display in tables and pivot tables. For example, you can create a conditional format to color the Region column green when values of the Sales column are greater than $30 million, as shown in Figure 7-1.

Figure 7-1 Conditional Formatting on the Region Column

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.

What Factors Affect Conditional Formats?

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"

• "Value Suppression and Conditional Formats"

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 Region column where Year is 1999. If Year and Region 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 will not apply.

For example, consider the table in Figure 7-2. A conditional format has been specified to color the Region column magenta when Year is 1999. Notice that no magenta coloring is visible, because Region is the first column in the table and so is displayed at a higher grain. (The Region column has its value suppression set to Default.) See "Value Suppression and Conditional Formats" for more information.

Figure 7-2 Columns with No Conditional Formatting Applied

Suppose that you change the order of the columns so that Year is the first column in the table. Then the Year column is displayed at a higher grain, and the appropriate conditional formatting is applied to the Region column. Figure 7-3 shows a table with the appropriate conditional formatting.

Figure 7-3 Conditional Formatting Based on Column Order

Conditional formats can be displayed on any edge of the table or pivot table. 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 Region column that has the members North, South, East, and West. Suppose the 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 Sales is greater than $10 million. If East and West are the only regions that meet that condition, then each one 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 Properties dialog: Column Format tab" 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 Figure 7-2. A conditional format has been applied to color the Region column magenta when Year is 1999. Notice that no magenta coloring is visible, because the value suppression is set to Default, which does not allow for repeating column values for the members of Region.

If the value suppression is set to Repeat, then column members are repeated and the appropriate conditional formatting is applied. Figure 7-4 shows a table with repeat value suppression.

Figure 7-4 Conditional Formatting for Repeat Value Suppression

Applying Conditional Formatting

To apply conditional formatting:

1. Open the analysis in which you want to edit formatting and behavior in the "Analysis editor: Criteria tab".

2. Add or locate the column or hierarchy level that you want to modify. Click the Options button to the right of the column name in the Selected Columns pane, and click Column Properties or Hierarchy Level Properties. The "Column Properties dialog" is displayed.

3. Click the "Column Properties dialog: Conditional Format tab".

4. Click Add Condition, then select the column to which to apply the condition.

5. Complete the "New Condition dialog". You can click the Edit Condition button to the right of the condition name to display the "Edit Condition dialog".

   
   

   Note: When you access the "New Condition dialog" from the Conditional Format tab, the dialog shows only the options that apply to conditional formats. For example, the Operator list shows the subset of operators that are used in conditional formats, and you can apply only presentation variables.

   
   

6. Complete the "Edit Format dialog" to apply formatting for the condition.

Example of Conditional Formatting for Ranking

The following 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.

Custom Date and Time Format Strings

Custom date and time format strings provide additional options for formatting columns or hierarchy levels that contain time stamps, dates, and times. Use the "Column Properties dialog: Data Format tab" to locate the Custom Format field where you can set custom date and time format strings to a column or hierarchy level.

This section contains the following topics:

• "General Custom Format Strings"

• "ODBC Custom Format Strings"

• "Custom Format Strings for Integral Fields"

• "Custom Format Strings for Conversion into Hours"

• "Custom Format Strings for Conversion into Hours and Minutes"

• "Custom Format Strings for Conversion into Hours, Minutes, and Seconds"

General Custom Format Strings

Table 7-1 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.

Table 7-1 General Custom Format Strings

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.

ODBC Custom Format Strings

Table 7-2 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.

Table 7-2 ODBC Custom Format Strings

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 Integral Fields

Table 7-3 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.

Table 7-3 Format Strings for Integral Fields

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

Table 7-4 shows the custom format strings that can be used to format data into hours. These can be used 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, as described in Table 7-1, "General Custom Format Strings". (This format displays the hour field only in the locale's format, such as 8 PM.)

Table 7-4 Format Strings for Conversion into Hours

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

Table 7-5 shows the custom format strings that can be used to format data into hours and minutes. These can be used 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 Table 7-1, "General Custom Format Strings". (This format displays the time in the locale's time format, but omits the seconds.)

Table 7-5 Format Strings for Conversion into Hours and Minutes

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

Table 7-6 shows the custom format strings that can be used to format data into hours, minutes, and seconds. These can be used on fields that contain integers or real numbers that represent time.

They can also be used where the output is in [FMT:time] format, described in "General Custom Format Strings". (This format displays the time in the locale's time format.)

Table 7-6 Format Strings for Conversion into Hours, Minutes, and Seconds

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.