| Print Open PDF Version of Online Help

#### Editing Column Properties You can edit properties for a column to control the appearance and layout of a column and its contents. You can also specify formatting that is only applied if the contents of the column meet certain conditions. By default, your selections for a column apply to the current report only. ### Editing the Appearance of Column Contents The default appearance of column contents in results is based on cascading style sheets and XML message files. You can use the Style tab of the Column Properties dialogue box to override several default settings, such as the font and font size to be used. Your selections only apply to the contents of the column for the report on which you are working. To edit the appearance of column contents In the Criteria tab, pull down the menu for the desired field and choose Column Properties. In the Column Properties dialogue box, click the Style tab. Make your choices for font, cell, border, image and advanced style formatting options. For more information, see Formatting Column Content in this topic. In the Data Format tab, you can control the way the data is displayed by selecting the Override Default Data Format checkbox. This option allows you to override the default display characteristics. The selections that you see vary according to the data type. For example, if the column contains numeric data, you can select how you want the numbers to be treated, such as percentages, month names or dates. You can select the number of decimal places to be displayed, how to display negative numbers, the number of digits to be shown and the thousands separator to be used. If your column contains text, you can select how to treat the text, such as plain text, HTML or a link. Based on your selection, the Custom Text Format text box displays the applicable HTML string used to display the data. NOTE: If your user role does not include the Analytics Scripting privilege, then only the two plain text options are available for you to select. To use a custom format for text, select Custom Text Format from the Treat Text As drop-down list and then enter the custom format. If your user role includes the Analytics Scripting privilege, then you can enter HTML calls that provide special formatting. You can also enter any valid HTML string, including JavaScript, VBScript and so on. For example, the following example HTML sets the column width and height in tables and pivot tables. In the example, the text html enclosed in brackets ([html]) indicates to use HTML, and the At sign character (@) represents the dynamic contents of the column. [html]<span style="width:200px" style="height:50px">@</span> If you do not know HTML, JavaScript, VBScript and so on, you can consult a third-party reference. To create a custom numeric format, you can use the number sign (#) to include significant digits, and the number zero (0) to include as many digits as specified, even if the number does not contain that detail. These are examples: Specifying ##.# shows 12.34 as 12.3 Specifying ##.000 shows 12.34 as 12.340 Click OK when you have finished. ### Editing the Layout of Column Contents Use the Column Format tab in the Column Properties dialogue box to perform the following tasks: Specify whether the column should appear in results. Columns are usually visible in results by default. However, you may want to include a column in your report that you do not want displayed in the results, such as a column used for creating a filter. Control the display of duplicate data such as repeating column values. The data in one column of a table may be repeated in relation to rows of data in other columns in the table. For example, if one column lists customer names and another column lists the regions in which those customers are based, the region data could be repeated for each customer row. You can select to display duplicate data once only, or display it for every row. Displaying repeating or duplicated data once only can make a table somewhat easier to read, and can make distinctions in the data more apparent. Use the Data Format tab to format results in a variety of ways. Use the Conditional Format tab to use functions and conditional expressions to format results conditionally. Use the Interaction tab to specify the interaction that should occur when users work with the results, such as drilling, navigation to another report using Action Links, or sending master-detail events, Your selections only apply to the contents of the column for the report on which you are working. To hide a column in results In the Criteria tab, pull down the menu for the desired column and choose Column Properties. In the Column Properties dialogue box, click the Column Format tab. Select the Hide check box, if it is not already selected. To create custom table and column headings In the Criteria tab, pull down the menu for the desired column and choose Column Properties. If you have combined results from multiple analyses, then the Column Properties button is at the result-column level. For more information about combining analyses, see Combining Analyses Using Set Operations. In the Column Properties dialogue box, click the Column Format tab. In the Headings area, select the Custom Headings checkbox. This allows you to change the heading text and the heading formatting. To change the text of the table or column heading, enter a new heading in the appropriate text box. The heading will be used in this report instead of the default heading. To change the format of the table or column heading, click the Edit Format button next to the text box. The Edit Format dialogue box appears. Make your choices for font, cell, border and advanced style formatting options. For more information, see "Formatting Column Content" below. To suppress duplicate data In the Criteria tab, pull down the menu for the desired column and choose Column Properties. In the Column Properties dialogue box, click the Column Format tab. On the Value Suppression area, select the appropriate option: Select Suppress to display repeating data once only and suppress duplicate rows. Select Repeat to display repeating data for every row. Select Default to retain the default display characteristics. To specify what happens when users click a value In the Criteria tab, pull down the menu for the desired column and choose Column Properties. In the Column Properties dialogue box, click the Interaction tab. To specify what should happen when users click a value, select the appropriate option in the Primary Interaction field: Default (Drill).Restores the default interaction. Drill. Allows you to drill down, so that you can view more information. Action Links. Specify action links to a target report or dashboard. Send Master-Detail Events. Use the Specify Channel field to identify where to send the data. None. Disables drilling or navigation. Interactions are defined separately for a column heading and the data in a column. ### Applying Conditional Formatting to the Column Contents In tables and pivot tables, conditional formatting helps direct attention to a data element if it meets a certain condition. For example, you can show high revenue sales figures in a certain colour, or display an image such as a trophy next to the name of each salesperson who exceeds revenue by a certain percent. You do this by selecting one or more columns in the report to be used, specifying the condition to meet, and then making selections for font, cell, border and style sheet options to be applied when the condition is met. The conditional formats can include colours, fonts, images, and so on, for the data and for the table cell that contains the data. The steps required to specify a condition are very similar to those used to create filters. You can add multiple conditions so that the data and the table cell are displayed in one of several formats, based on the value of the data. For example, low revenue sales can be displayed in one colour, and high revenue sales can be displayed in another colour. Several subtle conditional formatting differences exist between traditional tables and pivot tables. Conditional formats that format one column based on the value of another column are not reflected in a pivot table, but are reflected in a standard table. For example, setting the colour of a region name based on the sales in that region has no effect in a pivot table. However, setting the colour of the sales data based on the value of the sales data is reflected in a pivot table, as is setting the colour of the region name based on the actual name; for example, displaying a value of Eastern Region in bold coloured text. NOTE: In pivot tables, conditions are evaluated against the values as calculated or aggregated by the pivot table. Conditional formatting is applied based on the underlying value, even if you select the Show As options to show the data as percentages or indexes. Your selections only apply to the contents of the column for the report on which you are working. To add conditional formatting to a column in a report In the Criteria tab, pull down the menu for the desired column and choose Column Properties. In the Column Properties dialogue box, click the Conditional Format tab. Click Add Condition and select the desired column in the report for use in constructing the condition. The New Condition dialogue box opens. Select the operator and value for the condition. Click OK to close the New Condition dialogue box. The Edit Format dialogue box opens. Make your choices for font, cell, border, image, and advanced style-formatting options. For more information, see Formatting Column Content in this topic. NOTE: If you specify an image as part of the conditional formatting, it appears conditionally in the results. When you have finished, click OK to return to the Column Properties dialogue box. The Column Properties dialogue box shows the condition, and the conditional formatting to be applied. Conditions are evaluated in the order that they are listed and the last condition that gets evaluated to true determines which formatting is applied. To reorder a column, click the Move Up or Move Down buttons. To delete a column from the condition, click the Delete button. To edit a condition or a conditional format, click the corresponding icon. You can specify another condition to be included, or click OK if you are finished. The following example describes how conditional formatting can be applied to results. Suppose a report 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 apply conditional formatting 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. To display only the images and not the ranking numbers, pull down the Position drop-down menu and choose Images Only. ### Formatting Column Content Use the Edit Format dialogue box (and the options on the Style tab of the Column Properties dialogue box) to select font, cell, border and advanced style formatting options for column data that is shown inside a cell in a tabular format, such as a table or pivot table. You can also select settings for table and column headings. If you are overriding the default formatting properties for a column, your selections are static. If you are specifying conditional formatting properties for a column, your selections only apply if the condition is met. To use the Edit Format dialogue box or the Style tab of the Column Properties dialogue box In the Criteria tab, pull down the menu for the desired column and choose Column Properties. In the Column Properties dialogue box, click the Style tab. In the Font area, make your selections for font family, size, colour, style (such as bold), and any effects to apply (such as underlining). In the Cell area, make your selections for alignment, background colour, and an image to display inside the cell. The selections for horizontal and vertical alignment are similar to text justification selections in word processors. Vertical alignment does not have any impact unless the column spans multiple rows of other columns. For horizontal alignment: Select Left to left-justify the data. This is the most common justification for text data. Select Right to right-justify the data. This is the most common justification for numeric data. Select Centre to centre the data. To retain the default data alignment associated with this column, select Default. For vertical alignment: Select Top to align the data to the top of the table cell. Select Bottom to align the data to the bottom of the table cell. Select Centre to align the data to the middle of the table cell. To retain the default vertical alignment associated with this column, select Default. When you click the Image button, the Select Image dialogue box opens. Select the No Image option if you do not want to include an image. To include a custom image, select the Custom Image option and specify the appropriate path in the text box. The image should be one that is accessible to all users who will view the results. Custom images can be used for both conditional and unconditional results. To include an image use the graphics selection window. The window shows images that are useful in conditional formatting, such as meters and trend arrows. The left pane shows the categories of images. When you click on an image category, the right pane shows the images in that category. Make your selection by selecting the radio button next to the image that you want to use. To specify the location of the image within the cell, make a selection from the Position drop-down menu: Default. Displays any images in the default position, which is usually to the left of the column data or heading. Left. Displays any images to the left of the column data or heading. Right. Displays any images to the right of the column data or heading. Images Only. Displays only the image, and not the column data or heading. In the Border area, make your selections for the border position, colour, and style of the cell. In the Additional Formatting Options area, make your selections for column width, height, indent (left padding), right padding, top padding and bottom padding. In the Custom CSS Style Options (HTML Only) area, you can override style and class elements specified in style sheets. This capability is for users who know how to work with cascading style sheets. Expand the Custom CSS Style Options (HTML Only) area by clicking the + icon. Click the checkbox beside the settings that you want to use, and then provide the location of the class, style or style sheet. For example, for Use Custom CSS Style you can enter valid CSS style attributes, separated by semicolons, such as: text-align:centre;colour:red When you have finished, click OK to close the dialogue box. ### Using Custom Date/Time Format Strings Custom date/time format strings provide additional options for formatting columns that contain timestamps, dates and times. To enter a custom date/time format string In the Criteria tab, pull down the menu for the desired column (one that contains a timestamp, a date, or a time) and choose Column Properties. In the Column Properties dialog box, click the Data Format tab. Select the Override Default Data Format check box. In the Date Format field, select Custom from the drop-down list. In the Custom Date Format field, type the custom format string exactly as shown in the following tables, including left and right bracket characters ([ ]). NOTE: You must type the custom format string in the Custom Date Format field. Custom format strings are not available for selection from the drop-down list. #### General Custom Format Strings The following table describes some general custom format strings and the results that they display. These allow the display of date/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]. This 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]. This 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]. This 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 The following table shows the ODBC standard typed custom format strings and the results they display. These display date/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]. This 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 The following 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 month name in the user's locale.
[MMMM] | Displays the full month name in the user's locale.
[DDD] | Displays the abbreviated day of the week in the user's locale.
[DDDD] | Displays the full day of the week in the user's locale.
- #### Custom Format Strings for Conversion into Hours The following table shows the custom format strings that can be used to format data in hours. These can be used for the following types 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 in 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 in 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 in 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 61214.30 as 5 PM.
#### Custom Format Strings for Conversion into Hours and Minutes The following table shows the custom format strings that can be used to format data in hours and minutes. These can be used for 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. (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 to 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 to 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 to 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 The following table 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 the topic General 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 to 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 to 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 to 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.
### Advanced Custom Formats In addition to the formats previously described, you can format dates and times by creating your own format using some of the common date and time formats described in the following table. For example, using the following formats, you can create the format: dddd - h:mm tt which formats the date stamp fields in the following way: Monday - 4:03 PM NOTE: When using these formats, do not include the square brackets ([ ]) that are required with other formats. While the following formats do provide greater formatting flexibility, they do not allow date formats to change according to the user's locale setting, which is possible with many of the previous formats. Some common date and time formats are described in the following table.
Format | Result
--- | ---
d | Number of the day of the month (for example: 1 to 31). Single-digit numbers do not have a leading zero (0).
dd | Like d, but single-digit numbers begin with a 0.
ddd | Three-letter abbreviation for the day of the week (for example: Sun, Mon).
dddd | Full name of the day of the week (for example: Sunday, Monday).
M | Month number (for example, 1, 12). Single-digit numbers do not have a leading 0.
MM | Similar to M, but single-digit numbers begin with a 0.
MMM | Three-letter abbreviation for the month (for example: Jan, Feb).
MMMM | Full name for the month (for example: January, February).
yy | Two-digit number for the year (for example: 06).
yyyy | Four-digit number for the year (for example: 2006)
h | Hour in a 12-hour format. Single-digit numbers do not have a leading 0.
hh | Similar to h, but single-digit numbers begin with a 0.
H | Hour in 24-hour format. Single-digits do not have a leading 0.
HH | Similar to H, but single-digit numbers begin with a 0.
m | Number of minutes. Single-digit numbers do not have a leading 0.
mm | Similar to m, but single-digit numbers begin with a 0.
s | Number of seconds. Single-digit numbers do not have a leading 0.
ss | Similar to s, but single-digit numbers begin with a 0.
tt | AM and PM indicator. Use with h and hh formats.

Published August 2022 | Copyright © 2005, 2022, Oracle and/or its affiliates. Legal Notices.