Oracle® Fusion Middleware Web User Interface Developer's Guide for Oracle Application Development Framework 11g Release 1 (11.1.1) Part Number B31973-03 |
|
|
View PDF |
This chapter describes how to use a databound ADF pivot table component to display data, and the options for pivot table customization.
This chapter includes the following sections:
Section 26.1, "Introduction tothe ADF Pivot Table Component"
Section 26.2, "Understanding Data Requirements for a Pivot Table"
Section 26.7, "Connecting Pivot Tables with Other Components"
Section 26.8, "Customizing the Cell Content of a Pivot Table"
For information about the data binding of ADF pivot tables, see the "Creating Databound ADF Pivot Tables" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.
The ADF pivot table component displays a grid of data with rows and columns. Similar to spreadsheets, this component provides the option of automatically generating subtotals and totals for grid data. Unlike other tables and spreadsheets, the pivot table lets you switch data labels from one edge to another to obtain different views of your data, supporting interactive analysis.
The power of the pivot table's interactive capability is based in its display of multiple nested attributes on row and column headers. You can dynamically change the layout of these attributes using drag-and-drop operations.
Figure 26-1 shows a pivot table with multiple attributes nested on its rows and columns.
Pivot tables support on-demand data scrolling for large data sets. Only the data being viewed in the pivot table is loaded. As the user scrolls vertically or horizontally, data is fetched or discarded to fill the new pivot table view. Figure 26-2 shows a pivot table with a large data set using on-demand data scrolling.
The following list of pivot table terms uses Figure 26-1 as a Sales Pivot Table sample in its descriptions of terms:
Edges: The axes in pivot tables, such as:
Row edge: The vertical axis to the left of the rows in the body of the pivot table. In the sample, the row edge contains two layers, Year and Products, and each row in the pivot table represents a year and a product.
Column edge: The horizontal axis that appears above the columns in the body of the pivot table. In the sample, the column edge contains three layers, Measures, Channels, and Geography, and each column in the pivot table represents a measure value (Sales or Units), a channel indicator (All Channels), and a geographic location (World or Boston).
Layers: Nested attributes that appear in a single edge. In the sample, the following three layers appear in the column edge: Measures, Channels, and Geography. The following two layers appear in the row edge: Years and Products.
Header cell: The labels that identify the data displayed in a row or column. Row headers appear on the row edge and column headers appear on the column edge.
Data cell: The cells within the pivot table that contain data values, not header information. In the sample, the first data cell contains a value of 20,000.000.
QDR (Qualified Data Reference): An argument that maps a fully qualified data reference to a row, a column, or an individual cell. For example, in the sample Sales Pivot Table, the QDR for the first cell in the table must provide the following information:
Year=2007
Product=Tents
Measure=Sales
Channel=All Channels
Geography=World
The pivot table component uses a model to display and interact with data. The specific model class used is oracle.adf.view.faces.bi.model.DataModel
.
You can use any row set (flat file) data collection to supply data to a pivot table. During the data binding operation, you have the opportunity to drag each data element to the desired location on the row edge or column edge of the pivot table in the data binding dialog.
During data binding, you also have the option of specifying subtotals and totals for pivot table columns.
For information about the data binding of ADF pivot tables, see the "Creating Databound ADF Pivot Tables" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.
You can drag any layer in a pivot table to a different location on the same row or column edge or to a location on another row or column edge. For example, you can drag the geography layer in the column edge and drop it in any location on the row edge. Pivoting in a pivot table is enabled by default.
When you hover the cursor over a layer handle with a mouse, the layer's label is displayed and the cursor changes to a four-point arrow drag cursor. You can use this handle to drag the layer to the new location. If you hover the cursor over a layer on the row edge, then the handle appears above the layer, as shown in Figure 26-3. If you hover the cursor over a layer in the column edge, then the handle appears to the left of the layer, as shown in Figure 26-4.
If, in Figure 26-3, you drag the layer handle of the Time (Year) from the row edge to the column edge between the Measures (Sales) layer and the Channels layer, the pivot table will change shape as shown in Figure 26-5.
You can customize pivoting to disable pivot labels and pivoting.
To customize pivoting in a pivot table:
In the Structure window, right-click the dvt:pivotTable
component and select Go to Properties.
Optionally, in the Appearance category of the Property Inspector, in the PivotLabelVisible
field, select false from the dropdown list to disable the display of the label in the pivot handle.
Optionally, in the Behavior category of the Property Inspector, in the PivotEnabled
field, select false from the dropdown list to disable the pivoting.
Selection allows a user to select one or more cells in a pivot table. Only one of the three areas including the row header, column header, or data cells can be selected at one time.
An application can implement features such as displaying customized content for a context menu, based on currently selected cells. Example 26-1 shows sample code for getting the currently selected header cells.
Example 26-1 Sample Code to Get Selected Header Cells
UIPivotTable pt = getPivotTable() if (pt == null) return null; HeaderCellSelectionSet headerCells = null; if (pt.getSelection().getColumnHeaderCells().size() > 0) { headerCells = pt.getSelection().getColumnHeaderCells(); } else if (pt.getSelection().getRowHeaderCells().size() > 0) { headerCells = pt.getSelection().getRowHeaderCells(); }
Pivot tables support sorting of data within the pivot table. Ascending and descending sort icons are displayed as the user hovers the cursor over the column header when sorting is enabled. By default, the sortMode
attribute of the dvt:pivotTable
component is set to grouped
, effectively sorting the data grouped by the row edge outermost layer. Figure 26-6 shows the sort icons in the "World Sales" column of the pivot table, where the data is grouped by the "Year" row edge outermost layer.
When you create a pivot table, default settings determine the overall size of that pivot table. The pivot table also automatically sizes rows, columns, and layers within the space allowed for the overall size. You have the option of changing the overall size of the pivot table, resizing rows and columns, and resizing layers.
The default size of a pivot table is a width of 300 pixels and a height of 300 pixels. Instead of entering pixels for width and height, you have the option of specifying a percentage value for width, height, or both. This percentage value refers to the portion of the page that you want the pivot table to use.
To customize the default settings of a pivot table:
In the visual editor, display the page that contains the pivot table.
Click Source to display the XML code on the JSPX page.
Enter the following code for the inlineStyle
attribute of the pivotTable
tag, where value1
is an integer that represents either the number of pixels or the percentage of the page for the width of the pivot table and value2
is an integer that represents either the number of pixels or the percentage of the page for the height of the pivot table: inlineStyle="width:value1;height:value2"
Example 26-2 shows the setting of the inlineStyle
attribute that specifies the width of the table as 50 percent of the page size and the height of the table as 400 pixels.
The pivot table autosizes rows, columns, and layers during design. At runtime, you can change the size of rows, columns or layers by dragging the row, column, or layer separator to a new location.
To resize rows, columns, and layers at runtime:
If you want to resize a row, do the following:
Position the cursor in the row header on the separator between the row you want to resize and the next row.
When the cursor changes to a double-sided arrow, click and drag to the desired location.
If you want to resize a column, do the following:
Position the cursor in the column header on the separator between the column you want to resize and the next column.
When the cursor changes to a double-sided arrow, click and drag the column separator to the desired location.
If you want to resize a layer, do the following:
Position the cursor in the layer header on the separator between the layer you want to resize and the next layer.
When the cursor changes to a double-sided arrow, click and drag to the desired location.
When you resize rows, columns, or layers, the new sizes remain until you change the attributes of the row or column edge through a pivot operation. After a pivot operation, the new sizes are cleared and the pivot table rows, columns, and layers return to their original sizes.
If you do not perform a pivot operation, then the new sizes remain for the life of the session. However, you cannot save these sizes through MDS (Monitor and Display Station) customization.
You can connect pivot tables with other ADF components, for example, display the totals in a pivot table when triggered by a check box, using partial page rendering (PPR). PPR allows only certain components on a page to be re-rendered without the need to refresh the entire page. For more information about PPR, see Chapter 7, "Introduction to Partial Page Rendering".
For a component to be re-rendered based on an event caused by another component, it must declare which other components are the triggers. Use the partialTriggers
attribute to provide a list of IDs of the components that should trigger a partial update of the pivot table. The pivot table listens on the trigger components and if one of the trigger components receives an event that will cause it to update in some way, the pivot table is also updated.
Example 26-3 shows sample code for updating a pivot table by displaying the totals when a check box is triggered. The triggering component uses the ID as the partialTriggers
value.
All cells in a pivot table are either header cells or data cells. Before rendering a cell, the pivot table calls a method expression. You can customize the content of pivot table header cells and data cells by providing method expressions for the following attributes of the dvt:pivotTable
tag:
For header cells, use one of the following attributes:
headerFormat
: Use to create formatting rules to customize header cell content.
headerFormatManager
: Use only if you want to provide custom state saving for the formatting rules of the application's pivot table header cells.
For data cells, use one of the following attributes:
dataFormat
: Use to create formatting rules to customize data cell content.
dataFormatManager
: Use only if you want to provide custom state saving for the formatting rules of the application's pivot table data cells.
To specify customization of the content of a data cell, you must code a method expression that returns an instance of oracle.dss.adf.view.faces.bi.component.pivotTable.CellFormat
.
To create an instance of a CellFormat object for a data cell:
Construct an oracle.adf.view.faces.bi.component.pivotTable.DataCellContext
object for the data cells that you want to format. The DataCellContext
method requires the following parameters in its constructor:
model
: The name of the dataModel
used by the pivot table.
row
: An integer that specifies the zero-based row that contains the data cell on which you are operating.
column
: An integer that specifies the zero-based column that contains the data cell that you want to format.
qdr
: The QDR
that is a fully qualified reference for the data cell that you want to format.
value
: A java.lang.Object
that contains the value in the data cell that you want to format.
Pass the DataCellContext
to a method expression for the dataFormat
attribute of the pivot table.
In the method expression, write code that specifies the kind of formatting you want to apply to the data cells of the pivot table. This method expression must return a CellFormat
object.
An instance of a CellFormat
object lets you specify the following arguments:
Converter: An instance of javax.faces.convert.Converter
, which is used to perform number, date, or text formatting of a raw value in a cell.
CSS style: Used to change the CSS style of a cell. For example, you might use this argument to change the background color of a cell.
CSS text style: Used to change the CSS style of the text in a cell. For example, you might use this argument to set text to bold.
New raw value: Used to change the cell's underlying value that was returned from the data model. For example, you might choose to change the abbreviated names of states to longer names. In this case, the abbreviation NY might be changed to New York.
Figure 26-7 shows a pivot table with sales totals generated for products and for product categories. In the rows that contain totals, this pivot table displays bold text (which is a text style change) against a shaded background (which is a style change). These changes show in both the row header cells and the data cells for the pivot table. The row headers for totals contain the text "Sales Total".
The pivot table also shows stoplight and conditional formatting of data cells. For more information, see Section 26.8.4, "Creating Stoplight and Conditional Formatting in a Pivot Table".
Example 26-4 shows sample code that produces the required custom formats. The example includes the code for method expressions for both the dataFormat
attribute and the headerFormat
attribute of the dvt:pivotTable
tag.
Example 26-4 Sample Code to Change Style and Text Style in a Pivot Table
public CellFormat getDataFormat(DataCellContext cxt) { CellFormat cellFormat = new CellFormat(null, null, null); QDR qdr = cxt.getQDR(); //Obtain a reference to the product category column. Object productCateg = qdr.getDimMember("ProductCategory"); //Obtain a reference to the product column. Object product = qdr.getDimMember("ProductId"); if (productCateg != null && productCateg.toString().equals("Sales Total")) { cellFormat.setTextStyle("font-weight:bold") cellFormat.setStyle("background-color:#C0C0C0"); } else if (product != null && product.toString().equals("Sales Total") { cellFormat.setTextStyle("font-weight:bold"); cellFormat.setStyle("background-color:#C0C0C0"); } return cellFormat; } public CellFormat getHeaderFormat(HeaderCellContext cxt) { if (cxt.getValue() != null) { String header = cxt.getValue().toString(); if (header.equals("Sales Total")) { return new CellFormat(null, "background-color:#C0C0C0", "font-weight:bold"); } } return null; }
Stoplight and conditional formatting of the cells in a pivot table are examples of customizing the cell content. For this kind of customization, an application might prompt a user for a high value and a low value to be associated with the stoplight formatting. Generally three colors are used as follow:
Values equal to and above the high value are colored green to indicate they have no issues.
Values above the low value but below the high value are colored yellow to warn that they are below the high standard.
Values at or below the low value are colored red to indicate that they fall below the minimum acceptable level.
Figure 26-7 shows data cells with stoplight formatting for minimum, acceptable, and below standards sales for States.
Example 26-5 shows code that performs stoplight formatting in a pivot table that does not display totals. If you want to do stoplight formatting for a pivot table that displays totals, then you might want to combine the code from Example 26-4 (which addresses rows with totals) with the code for stoplight and conditional formatting.
Example 26-5 Sample Code for Stoplight and Conditional Formatting
public CellFormat getDataFormat(DataCellContext cxt) { //Use low and high values provided by the application. double low = m_rangeValues.getMinimum().doubleValue() * 100; double high = m_rangeValues.getMaximum().doubleValue() * 100; CellFormat cellFormat = new CellFormat(null, null, null); // Create stoplight format if (isStoplightingEnabled()) { String color = null; Object value = cxt.getValue(); if (value != null && value instanceof Number) { double dVal = ((Number)value).doubleValue(); if (dVal < low) { color = "background-color:" + ColorUtils.colorToHTML(m_belowColor) + ";"; } else if (dVal > low && dVal < high) { color = "background-color:" + ColorUtils.colorToHTML(m_goodColor) + ";"; } else if (dVal > high) { color = "background-color:" + ColorUtils.colorToHTML(m_aboveColor) + ";"; } } cellFormat.setStyle(color); } return cellFormat; }
The content in a pivot table data cell can be customized using the dvt:dataCell
child component to place a read-only or input component in each data cell. Instead of creating a child component for every data cell in the pivot table, each data cell is repeatedly rendered, or stamped, once per data attribute, such as the rows in a pivot table. Only certain types of components are supported, including all components with no activity and most components that implement the EditableValueHolder
or ActionSource
interfaces. You can also use stamping to specify custom CSS styles for the data cell.
Each time a child component is stamped, the data for the current cell is copied into a var
property used by the data cell component in an EL Expression. Once the pivot table has completed rendering, the var
property is removed, or reverted back to its previous value.
Data cell editing is enabled by using an input component as the child component of dvt:dataCell
. At runtime you can open the cell for editing by double-clicking the cell in the pivot table.
Example 26-6 shows sample code for data cell stamping.
Example 26-6 Data Cell Stamping Sample Code
<dvt:pivotTable var="cellData" varStatus="cellStatus"> <!--This is an example of using an input component in a data cell. At runtime double-click to edit the cell.--> <dvt:dataCell dataAttribute="Sales"> <af:inputText value="#{cellData.dataValue}" /> </dvt:dataCell> <!--This is an example of using custom CSS styling in a data cell.--> <dvt:dataCell dataAttribute="Weight"> <af:outputText value="#{cellData.dataValue}" inlineStyle="#{cellStatus.cellFormat.textStyle}"/> </dvt:dataCell> <!-- This is an example of using the dataAttribute attribute to determine the type of input component inside data cell based on different measures.--> <dvt:dataCell dataAttribute="Available"> <af:selectBooleanCheckbox id="idselectbooleancheckbox" label="Availability" text="Item Available"autoSubmit="true" value="#{cellData.dataValue}"/> </dvt:dataCell> <!-- This is the default data cell that will be used for data attributes other than the ones specified before--> <dvt:dataCell> <af:outputText value="#{cellData.dataValue}" /> </dvt:dataCell> </dvt:pivotTable>
Figure 26-8 shows the resulting pivot table.
Note:
In order to temporarily or permanently write values back to a set of cells within a cube, called a writeback, the pivot table must be bound to a data control or data model that supports writeback operations. A row set based data control is transformed into a cube and therefore cannot support writeback operations.With data cell stamping you can use the dvt:dataCell
tag to specify a custom image for a data cell using af:image
, af:icon
, or af:commandImageLink
as a child tag. Example 26-7 shows sample code for using an af:commandImageLink
as a custom image in a pivot table data cell.
Example 26-7 Using a Custom Image for a Data Cell
<dvt:pivotTable var="cellData" varStatus="cellStatus"> <!-- This is the default data cell that will be used for all data attributes--> <dvt:dataCell> <af:commandImageLink text="Go" icon="/images/go.gif" actionListener="#{pivotTableBean.imageLinkClick}"/> <af:outputText value="#{cellData.dataValue}" /> </dvt:dataCell> </dvt:pivotTable>
Actions associated with the image are handled through a registered listener, actionListener
. In a bean class you specify the method to be called when the image link is clicked, for example:
public void imageLinkClick (javax.faces.event.ActionEvent.action)