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:
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:
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:
- Click the Filter toolbar button.
- Select a Data Field to filter by specific data elements. All elements are available regardless of whether they're included as table columns.
- Select an Operator to filter by these 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.
- Add a Value 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:
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.
Manage Formats
After you've added conditional formats, use the Manage Formats command to edit or delete a format.
To manage formats:
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.
-
Format - Select one of the common date formats from the list. The format is applied immediately to the table column. The formats are categorized by Date and Time, as shown in the following figure:
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:
- Select the data column or field in the layout.
- On the Properties pane, under the Data Formatting group select the Formatting Style. Supported styles are Oracle and Microsoft.
- 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:
Remove a Sort Order
You can remove the sorting applied to a column.
To remove a sort order applied to a column:
- Select the column.
- 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:
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}