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:
Inserting a Data Table
Follow these steps to insert a data table and to add data columns to the table.
- 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.
- 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. For more information, see Creating Element Level Links in Data Modeling Guide for Oracle Business Intelligence Publisher.
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.
- 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 have 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. See About the Total Cell Tab.
-
Default date formatting is applied. To change the default formatting, see About the Column Tab.
-
Default number formatting and alignment is applied. To change the default formatting, see About the Column Tab.
-
Setting 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:
- Click the value shown for Alternate Row Color to launch the color picker. The following illustration shows the Alternate Row Color option.
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.
Setting 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 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 are applied to the table data.
Managing Filters
After you have added filters, use the Manage Filters feature to edit, delete, or change the order that the filters are applied.
To manage filters:
- Click the Manage Filters toolbar button to launch the Manage Filters dialog, as shown in the following illustration.
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.
Applying Conditional Formats to a Table
This table describes the different fields in the Highlight dialog.
To apply a conditional format:
- Click the Highlight button. This launches the Highlight dialog, as shown in the following figure.
- 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 are 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 on 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.
Managing Formats
After you have added conditional formats, use the Manage Formats command to edit or delete a format.
To manage formats:
- Click the Manage Formats button to launch the Manage Conditional Formats dialog, as shown in the following illustration.
Controlling 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. For more information see About the Total Cell Tab.
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 is 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.
Applying 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 is not 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:
Applying 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:
To apply a format not available from this list, see Applying Custom Data Formatting.
-
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.
Applying 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.
For more information on Microsoft and Oracle format masks, see Formatting Numbers, Dates, and Currencies.
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 Setting 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:
To apply the sort order to this table:
- From the Priority list, select 1.
The following figure shows the Priority list.
Removing 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
Applying Data Formatting to a Total Cell
The section talks about applying data formatting to a total cell.
Applying 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 Setting Predefined or Custom Formulas.
Inserting Dynamic Hyperlinks
The layout editor supports dynamic hyperlinks in tables.
To insert a dynamic hyperlink:
- Click Properties. The column properties include an option for URL, as shown in the following illustration.
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}
Applying Custom Data Formatting
BI 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.
For more information on Microsoft format masks, see Using the Microsoft Number Format Mask.
For more information on Oracle format masks, see Using the Oracle Format Mask.
To apply custom data formatting:
- Click Properties. The Data Formatting options are displayed as shown in the following figure:
