Print      Open PDF Version of Online Help

Reports > Step 1: Defining Criteria > Editing Column Properties

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 to apply only if the contents of the column meet certain conditions. By default, your selections for a column apply only to the current report.

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 dialog box to override several default settings, such as the font and font size to use. Your selections apply only to the contents of the column for the report with which you are working.

To edit the appearance of column contents

1. In Oracle CRM On Demand Answers, in the Define Criteria page, click the Column Properties button for the column.
2. In the Column Properties dialog box, click the Style tab.
3. Make your choices for font, cell, border, image, and advanced style formatting options.

   For more information, see Formatting Column Content in this topic.

4. In the Data Format tab, you can control the way the data is displayed by selecting the Override Default Data Format check box.

   This option allows you to override the default display characteristics. The selections that you see vary based on the data type. For example, if the column contains numeric data, you can select how you want the numbers treated, such as percentages, month names, or dates. You can select the number of decimal places to display, how to display negative numbers, the number of digits to show, and the thousands separator to use. 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.

   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. 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]) means 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

5. Click OK when you are done.

Editing the Layout of Column Contents

Use the Column Format tab of the Column Properties dialog 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 results, such as a column used in creating a filter.

• Assign alternate table and column headings and apply custom formatting to them.
• 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 those customers are in, the region data could be repeated for each customer row. You can select to display duplicate data only once, or display it for every row. Displaying repeating or duplicated data only once can make a table somewhat easier to read, and can make distinctions in that data more apparent.

• Specify the interaction that should occur when users work with the results, such as navigation to another report.

You can also use functions and conditional expressions to format results in a variety of ways.

Your selections apply only to the contents of the column for the report with which you are working.

To hide a column in results

1. In Oracle CRM On Demand Answers, in the Define Criteria page, click the Column Properties button for the column.
2. In the Column Properties dialog box, click the Column Format tab.
3. Select the Hide this column check box, if it is not already selected.

To create custom table and column headings

1. In Oracle CRM On Demand Answers, click Define Criteria.
2. Click the Column Properties button for the column.

   If you have combined results from multiple reports, then the Column Properties button is at the result-column level. For more information about reports, see Combining Results from Multiple Reports Using Set Operations.

3. In the Column Properties dialog box, click the Column Format tab.
4. In the Headings area, select the Custom Headings check box.

   This allows you to change the heading text and the heading formatting.

5. To change the text of the table or column heading, enter a new heading into the appropriate text box.

   The heading will be used in this report instead of the default heading.

   NOTE: You can also specify navigation to another report in the Column Heading Interaction area.

6. To change the format of the table or column heading, click the Edit Format button next to the text box.

   The Edit Format dialog 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

1. In Oracle CRM On Demand Answers, in the Define Criteria page, click the Column Properties button for the column.
2. In the Column Properties dialog box, click the Column Format tab.
3. In the Value Suppression area, select the appropriate option:
   • Select Suppress to display repeating data only once 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 the column

1. In Oracle CRM On Demand Answers, in the Define Criteria page, click the Column Properties button for the column.
2. In the Column Properties dialog box, click the Column Format tab.
3. To specify what should happen when users click the column, select the appropriate option in the Column Heading Interaction area:
   • Default - Restores the default interaction.
   • Drill - Allows you to drill down, so that you can view more information.
   • Navigate - Allows navigation to another analysis. Click the Add Navigation Target button, browse to a target report or dashboard, click OK, and enter a caption for the target in the Caption text box. Repeat this step for each navigation target you want to add. Click the Remove button beside the Caption text box to remove that navigation target.
   • No interaction - Disables drilling or navigation. Interactions are defined separately for a column heading and the data in a column.

To specify what happens when users click a value

1. In Oracle CRM On Demand Answers, in the Define Criteria page, click the Column Properties button for the column.
2. In the Column Properties dialog box, click the Column Format tab.
3. To specify what should happen when users click a value, select the appropriate option in the Value Interaction area:
   • Default - Restores the default interaction.
   • Drill - Allows you to drill down, so that you can view more information.
   • Navigate - Allows navigation to another analysis. Click the Add Navigation Target button, browse to a target report or dashboard, click OK, and enter a caption for the target in the Caption text box. Repeat this step for each navigation target you want to add. Click the Remove button beside the Caption text box to remove that navigation target.
   • No interaction - 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 color, 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 use, specifying the condition to meet, and then making selections 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 table cell that contains the data. The steps 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 upon the value of the data. For example, low revenue sales can be displayed in one color, and high revenue sales can be displayed in another color.

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 color of a region name based on the sales in that region has no effect in a pivot table. However, setting the color of the sales data based on the value of the sales data is reflected in a pivot table, as is setting the color of the region name based on the actual name; for example, displaying a value of Eastern Region in bold colored 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 percents or indexes.

Your selections apply only to the contents of the column for the report with which you are working.

To add conditional formatting to a column in a report

1. In Oracle CRM On Demand Answers, in the Define Criteria page, click the Column Properties button for the column.
2. In the Column Properties dialog box, click the Conditional Format tab.
3. Click the Add Condition button and select the desired column in the report for use in constructing the condition.

   The Create/Edit Filter dialog box opens. Select the operator and value for the condition.

   NOTE: When you access the Create/Edit Filter dialog box from the Conditional Format tab, the dialog box shows only the options that apply to conditional formats. For example, the Operator drop-down list shows the subset of operators that are used in conditional formats.

4. Click OK in the Create/Edit Filter dialog box.

   The Edit Format dialog box appears.

5. 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.

6. Click OK when you are done to return to the Column Properties dialog box.

   The Column Properties dialog box shows the condition, and the conditional formatting to apply.

   Columns are evaluated in the order that they are listed.

   • 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 it.
7. You can specify another condition to include, or click OK if you are done.

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.

In the Graphics dialog box, select the Images Only image placement option. In the results display, the columns appear with only the images, and not the ranking numbers.

Formatting Column Content

Use the Edit Format dialog box (and the options on the Style tab of the Column Properties dialog 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 apply only if the condition is met.

To use the Edit Format dialog box or the Style tab of the Column Properties dialog box

1. In Oracle CRM On Demand Answers, in the Define Criteria page, click the Column Properties button for the column.
2. In the Column Properties dialog box, click the Style tab.
3. In the Font area, make your selections for font family, size, color, style (such as bold), and any effects to apply (such as underlining).
4. In the Cell area, make your selections for alignment, background color, 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 Center to center 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 Center 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 Graphics dialog 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 distributed with Oracle CRM On Demand Answers, 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 you want to use.
     • To specify the location of the image within the cell, make a selection from the Image Placement drop-down list:

       Default - Displays any images in the default position, which is usually to the left of the column data or heading.

       Images Left - Displays any images to the left of the column data or heading.

       Images 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.

5. In the Border area, make your selections for the border position, color, and style of the cell.
6. In the Additional Formatting Options area, make your selections for column width, height, indent (left padding), right padding, top padding, and bottom padding.
7. In the Custom CSS Style Options (HTML Only) area, you can override style and class elements specified in Oracle CRM On Demand Answers 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 check box beside the settings 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:center;color:red

8. Click OK when you are done to close the dialog 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

1. In Oracle CRM On Demand Answers, in the Define Criteria page, click the Column Properties button for a column that contains a timestamp, a date, or a time.
2. In the Column Properties dialog box, select the following option in the Data Format area: Override Default Data Format check box.
3. In the Date Format field, select Custom from the drop-down list.
4. 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 into 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 they display. These allow the display of date/time fields in the user’s locale.

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.

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.

Custom Format Strings for Conversion into Hours

The following table 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. (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 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 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. (This format displays the time in the locale’s time format, but omits the seconds.)

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.)

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 datestamp 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.