About Data Tables

The data table is a standard table that is shown in many layouts. It contains a header, data columns, and a total row. The table supports "group left" functionality (outlines) that merges fields with the same values as well as subtotals, grand totals, custom calculations, and running totals.

Once inserted, you can edit the table properties using the dynamic tabs or the Properties pane. The following dynamic tabs are available for the table components:

  • Table

  • Table Column Header

  • Column

  • Total Cell

This section contains the following topics about working with tables:

Insert a Data Table

Follow these steps to insert a data table and to add data columns to the table.

To insert a data table:
  1. From the Insert tab, select and drag the Data Table component to the design area.

    The following figure shows an example of an inserted, empty data table. Notice that the Table tab is now displayed.

  2. To add data columns to the table, select an element from the Data Source pane and drag it to the table in the layout.

    Note:

    You cannot include elements from multiple data models in report components unless the data models are linked.

    The following figure shows the columns being added to the table. Notice that when you drop a column on the table the sample data is immediately displayed.

  3. Continue to drag the elements from the Data Source pane to form the columns of the table. If you must reposition a column that you've already added, then select it and drag it to the correct position.

    The following figure shows a completed data table:

    Notice the following default behavior:

    • A total row is automatically inserted. By default it calculates the sum of the items in the column. You can remove this row or edit the display and calculation applied.

    • Default date formatting is applied.

    • Default number formatting and alignment is applied.

Set Alternating Row Colors

Some data tables are easier to read when the rows display alternating colors.

An example of alternating colors is shown in the following illustration.

To set an alternating row color:

  1. Select the table.
  2. Open the Properties pane.
  3. Click the value shown for Alternate Row Color to launch the color picker. The following illustration shows the Alternate Row Color option.
  4. Choose a color and click OK.

About the Table Tab

The Table tab defines the functions that you can perform to display a table in a customized manner.

The Table tab enables you to perform the following:

  • Set the number of rows displayed

  • Define filters for the data displayed in the table

  • Define conditions and formats to apply to rows that meet the conditions

  • Show or hide the total row for the table

The following figure shows the Table tab.

Set the Rows to Display Option

The Rows to Display property controls the number of rows of data displayed

The property is set as follows:

  • When designing the layout, this property sets the number of rows that are displayed for the table within the layout editor.

  • When viewing this layout in the report viewer in interactive mode, this property sets the size of the scrollable region for the table.

The default is 10 rows of data. You can select 10, 20, 30, 40, or All rows of data to be displayed. To set a custom value, open the Properties pane and enter the custom value for the Rows to Display property.

Note:

Displaying more rows of data can impact the performance of the Layout Editor.

About Filters

A filter refines the displayed items by a condition. This is a powerful feature that enables you to display only desired elements in the table in an interactive output without having to perform additional coding.

For example, you could add a filter to meet some of the following report conditions:

  • Display only the top 10 salaries

  • Display only the bottom 25 store sales

  • Display only employees in the IT department

  • Display only sales that are between $10,000 and $20,000 and in the Southern region

You can add multiple filters and manage the order in which they're applied to the table data.

Set Filters for a Table

You can use a filter to narrow table results.

To set a filter:

  1. Click the Filter toolbar button.
  2. Enter the fields to define a filter, as described in the following table.
    Field Description

    Data Field

    Select the data field to filter the table data by. All elements are available regardless of whether they're included as table columns.

    Operator

    Select from the following operators: is equal to, is not equal to, is less than, is greater than, is less than or equal to, is greater than or equal to, is between, is in top, is in bottom

    Value

    Enter the value or values appropriate for the operator selected. The value can be either a text entry, or an element from the data.

Manage Filters

After you've added filters, use the Manage Filters feature to edit, delete, or change the order that the filters are applied.

To manage filters:

  1. Click the Manage Filters toolbar button to launch the Manage Filters dialog, as shown in the following illustration.
  2. Hover the cursor over the filter to display the actions toolbar. Use the toolbar buttons to edit the filter, move the filter up or down in the order of application, delete, or add another filter.

About Conditional Formats

A conditional format changes the formatting of an element in the table based on a condition.

This feature is extremely useful for highlighting target ranges of values in the table. For example, you could create a set of conditional formats for the table that display rows in different colors depending on threshold values.

Apply Conditional Formats to a Table

This table describes the different fields in the Highlight dialog.

To apply a conditional format:

  1. Click the Highlight button. This launches the Highlight dialog, as shown in the following figure.
  2. Enter the fields to define a condition and format to apply, as described in the following table.
    Field Description

    Data Field

    Select the data field to apply the condition to. All elements are available regardless of whether they're included as table columns. For example, you may want to highlight in red all employees with salaries greater than $10,000, but not actually include the salary element in the table.

    Operator

    Select from the following operators: is equal to is not equal to is less than is greater than is less than or equal to is greater than or equal to is between

    Value

    Enter the value or values appropriate for the operator selected. The value can be either a text entry, or an element from the data.

    Note:

    If entering a date value, use one of the following XSL date or time formats: YYYY-MM-DD or YYYY-MM-DDTHH:MM:SS.

    Font Family

    Select the font to apply to the row of data that meets the condition. You can also apply bold, italic, or underline emphasis.

    Size

    Select the size of the font to apply to the row of data that meets the condition.

    Color

    Click the color box to open the Color Picker. Choose one of the predefined colors or click Custom Color to define a color to apply to the font.

    Background Color

    Click the color box to open the Color Picker. Choose one of the predefined colors or click Custom Color to define the background color to apply to the row.

    The following figure shows the table in the layout with the condition applied.

Manage Formats

After you've added conditional formats, use the Manage Formats command to edit or delete a format.

To manage formats:

  1. Click the Manage Formats button to launch the Manage Conditional Formats dialog, as shown in the following illustration.
  2. Hover the cursor over an item to display the actions toolbar. Use the toolbar buttons to edit the format, move the format up or down in the order of application, delete, or add another format. The order of the conditions is important because only the first condition that is met is applied.

Control the Display of the Total Row

By default, the layout editor inserts a total row in a table that sums numeric columns. To remove the total row, click the Show menu and select the table view without the highlighted total row.

The following figure shows the Show menu options:

The total row can be further customized using the Total Cell tab and the Properties pane.

About the Table Column Header Tab

The Table Column Header tab defines the functions that you can perform.

The following figure shows the Table Column Header tab.

The Table Column Header tab enables you to perform the following:

  • Edit the font properties of the table header column

  • Edit the cell properties of the table header including border weight, style, and color and background fill color

  • Set the vertical and horizontal alignment of the table header

  • Apply grouping

About Grouping

"Grouping" groups together elements in the data of the same value. In a table, applying grouping can make the table easier to read.

The Grouping option enables you to choose between "Group Left" or "Group Above". Group left maintains the "group by" element within the table. The following figure shows a table that has been grouped by Manager using Group Left.

Group above inserts a Repeating Section component, and extracts the grouping element from the table. The grouping element is instead displayed above the table and a separate table is displayed for each occurrence of the grouping element. The following figure shows a table that has been grouped by Manager using Group Above.

Example: Group Left

The illustration here shows an example where the table data has been grouped by the elements of the first two columns, Manager and Title.

Notice that there's only one entry per manager name and one entry for each job title under that manager name. This organizes the data rows more cleanly in the table.

Apply Subtotals

To further enhance a table, you can add a subtotal row to display for each grouped occurrence of the element.

Example: Group Above

The illustration here shows an example where the table data has been grouped by Manager.

Notice that in the design pane, the Data Table component has been replaced with a Repeating Element component that contains the data table. The Manager element is inserted above the table with a label.

The label is a text item. Edit the text by double-clicking the item to select it, then single-clicking to edit.

When you run the report, a separate table is created for each occurrence of the grouping element. In Interactive output mode, the grouping element displayed at the top of the table is displayed as a filter. Select the value that you want to view from the list, as shown in the below figure:

About the Column Tab

The Column tab is enabled when you select a specific column in a table. You can edit font and cell properties and apply them.

The Column tab allows you to perform the following actions:

  • Edit the font properties of the column including style, size, and color

  • Edit the cell properties of the column including border weight, style, and color and background fill color

  • Set the vertical and horizontal alignment of the column contents

  • Apply formatting to the column data (options depend on the data type)

  • Apply grouping

  • Apply a running total (or other formula) to the data

  • Apply sorting and sort precedence

  • Apply conditional formatting to the column

About the Data Formatting Options for Columns

The options available from the Data Formatting region of the tab depend on the data type of the column selected. The tab provides common options to choose from.

If an option isn't listed, you can enter a custom Oracle or Microsoft formatting mask in the Properties pane. You can also set a formatting mask dynamically by including the mask as an element in your data. These features are described in the following sections:

Apply Formatting to Numeric Data Columns

Follow these formatting options if the column contains numeric data.

  • Format - Select one of the common number formats from the list. The format is applied immediately to the table column. The formats are categorized by Number, Percent, and Currency, as shown in the following figure:

  • Decimal position - Click the Move Left or Move Right to increase or decrease the decimal positions displayed.

  • Show/Hide Grouping Separator - Click this button to hide the grouping separator (for example, 1,234.00 displays as 1234.00). To show the grouping separator, click the button again.

Apply Formatting to Date Type Data Columns

Use these formatting options if the column contains dates.

Custom and Dynamic Formatting Masks

You can apply any Microsoft or Oracle (recommended) format mask to a report data field. You can manually enter the mask in the Formatting Mask property on the Properties pane.

To enter a custom data formatting mask:

  1. Select the data column or field in the layout.
  2. On the Properties pane, under the Data Formatting group select the Formatting Style. Supported styles are Oracle and Microsoft.
  3. In the Formatting Mask field, manually enter the format mask to apply.

Formatting masks can also be applied dynamically by either including the mask in a data element of your report data, or as a parameter to the report. The mask is passed to the layout editor based on the value of the data element.

To enter a dynamic formatting mask, in the Formatting Mask field, choose the data element that defines the formatting mask. The following figure shows an example of setting a dynamic number format mask. For this example, a parameter called NumberFormat prompts the user to define a format mask when the report is submitted. The value is passed to the Formatting Mask property and applied to the data field in the layout.

If you use a parameter to pass the format mask ensure that you select the Include Parameter Tags option on the data model Properties page.

About the Formula Option

The options available from the Formula region of the Column tab depend on the data type of the column.

For more information about applying formulas, see Set Predefined or Custom Formulas.

About the Sort Option

To sort the data in a column, select the column, then under the Sort group click Ascending Order or Descending Order.

To sort by more than one column, select the column, the sort order, and then assign a Priority to each column. The priority list is a list of values beneath the sort order commands.

For example, in the employee salary table shown in the following figure, assume you want to sort ascending first by Title then sort descending by Annual Salary:

  1. Select the Title column.
  2. On the Column tab, under Sort, click the Ascending Order button.
  3. From the Priority list, select 1.

    The following figure shows the Priority list.

  4. Next select the Annual Salary column.
  5. On the Column tab, under Sort, click the Descending Order button.
  6. From the Priority list, select 2.

Remove a Sort Order

You can remove the sorting applied to a column.

To remove a sort order applied to a column:

  1. Select the column.
  2. From the Sort region on the Column tab, click the appropriate button of the sort order that has been applied. For example, to deselect the ascending order, click the Ascending Order button to undo the sort.

About the Total Cell Tab

The Layout Editor automatically inserts a grand total row when you insert a data table to the layout. As shown in the section on grouping, you can also insert subtotal rows within the table based on a grouping element.

To edit the attributes of the cells in a grand total or subtotal row, select the cell and use the options in the Total Cell tab shown in the following figure.

The Total Cell tab enables you to perform the following:

  • Edit the font properties of the total cell

  • Edit the cell properties of the total cell including border weight, style, and color and background fill color

  • Set the vertical and horizontal alignment of the table header

  • Apply formatting to the cell data

  • Apply a formula to the cell

  • Apply conditional formatting to the cell

Apply Data Formatting to a Total Cell

The section talks about applying data formatting to a total cell.

Apply a Formula

By default, the formula applied to a Total Cell within a numeric column is a sum of the column items. The Formula option enables you to apply a different formula.

Not all options available from the Formula region of the column tab are applicable to a Total Cell.

For more information about applying formulas, see Set Predefined or Custom Formulas.

Insert Dynamic Hyperlinks

The layout editor supports dynamic hyperlinks in tables.

To insert a dynamic hyperlink:

  1. Select the table column.
  2. Click Properties. The column properties include an option for URL, as shown in the following illustration.
  3. In the URL field, enter the static portion of the URL and embed the absolute path to the element that provides the dynamic portion of the URL within curly braces {}. For example:

    http://example.com/show_page?id={/DATA/GROUP1/ELEMENT_NAME}

    where http://example.com/show_page?id= is the static portion of the URL and {/DATA/GROUP1/ELEMENT_NAME} is the absolute path to the element in the data that supplies the dynamic portion.

For example, in the employee salary report, suppose each employee name should render as a hyperlink to the employee's person record. Assume the static portion of the URL to each person record is

https://people.hrserver.com/records/show_page?id=

The dynamic portion comes from the data element EMPLOYEE_ID. For this example, append the full path to the EMPLOYEE_ID element within curly braces and enter this in the URL field as follows:

https://people.hrserver.com/records/show_page?id={/ROWSET/ROW/EMPLOYEE_ID}

Apply Custom Data Formatting

Publisher supports the use of the Oracle and Microsoft format masks for custom data formatting. The results of the output depends on the selected locale.

  1. Select a data field or column.
  2. Click Properties. The Data Formatting options are displayed as shown in the following figure:
  3. From the Formatting Style drop-down list, select the Oracle or Microsoft formatting style. The Oracle formatting style is recommended.
  4. In the Formatting Mask field, enter a formatting mask. For example, for a column that contains product totals, you can use the Oracle formatting style, and the 9G999D99 formatting mask to display total values with two zeros to the right of the decimal place.