Skip Headers
Oracle® Fusion Middleware Web User Interface Developer's Guide for Oracle Application Development Framework
11g Release 1 (11.1.1)

Part Number B31973-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

25 Using ADF Pivot Table Components

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:

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.

25.1 Introduction tothe ADF Pivot Table Component

The ADF pivot table component displays a grid of data with row and column edges. Similar to spreadsheets, this component provides the option of automatically generating subtotals and totals for grid data. Unlike other tables and spreadsheets, the ADF 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 25-1 shows a pivot table with multiple attributes nested on its rows and columns.

Figure 25-1 Sales Pivot Table with Multiple Row and Column Layers

Pivot table with multiple row and column layers

25.1.1 Pivot Table Elements and Terminology

The following list of pivot table terms uses Figure 25-1 as a Sales Pivot Table sample in its descriptions of terms:

  • Edges: The axes in pivot tables, such as:

    • Row: The vertical axis to the left of the rows in the body of the pivot table. In the sample, the row edge contains rows for years and products.

    • Column: The horizontal axis that appears above the columns in the body of the pivot table. In the sample, the column edge contains columns for measure values (sales and units), channel indicator (all channels), and geographic locations (World and Boston).

  • Headers: 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.

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

  • Layer members: Values of the attribute represented in a layer. In the sample, World and Boston are members of the geography layer in the column edge. Similarly, 2007, 2006, and 2005 are members of the years layers in the row edge.

  • Data body: The cells within the pivot table that contain data values, not header information. In the sample, the first data body cell contains 20,000.000 value.

  • QDR (Qualified Data Reference): An argument that maps a fully qualified data reference to 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

25.1.2 Drilling Down in a Pivot Table

Drilling down in a pivot table changes the query and provides a more detailed view of data. Figure 25-2 shows a pivot table with the First Quarter data expanded to display the details for each of the months in that quarter while the Second Quarter data is summarized.

Figure 25-2 Pivot Table with Expanded and Collapsed Data

Pivot table with drillable data.

What happens during drilling in a specific pivot table depends on the pivot table's data model and on the data's drill model. Drilling down in a pivot table requires a cubic data control not provided by ADF business components.

25.1.3 Pivot Layer Handles

You can drag any layer in a pivot table to a different location on the same edge or to a location on another edge. For example, you can drag the geography layer in a column edge and drop it in any location on the row edge.

When you hover the cursor over a layer with a mouse, the layer's handle is displayed. 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 25-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 25-4.

Figure 25-3 Display of Pivot Layer Handle on a Row Edge

Display of pivot layer handle on a row edge

Figure 25-4 Display of Pivot Layer Handle on a Column Edge

Display of pivot layer handle on a column edge

If, in Figure 25-3, you drag the layer handle of the Year from the row edge to the column edge between the Measures layer and the Channels layer, the pivot table will change shape as shown in Figure 25-5.

Figure 25-5 Sales Pivot Table After Pivot of Year to Column Edge

Sales table after pivot of year to column edge

25.2 Understanding Data Requirements for a Pivot Table

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.

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.

25.3 Sizing in a Pivot Table

When you create a pivot table, default settings determine the overall size of that pivot table. The pivot table also autosizes rows and columns within the space allowed for the overall size. You have the option of changing the overall size of the pivot table and resizing its rows and columns.

25.3.1 How to Set the Overall Size of a Pivot Table

The default size of a pivot table is a width of 300 pixels and a length of 300 pixels. Instead of entering pixels for width and length, you have the option of specifying a percentage value for width, length, 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:

  1. In the visual editor, display the page that contains the pivot table.

  2. Click Source to display the XML code on the JSPX page.

  3. 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 25-1 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.

Example 25-1 XML Code for Customizing Pivot Table Size

<dvt:pivotTable
.
.
.
   inlineStyle="width:50%;height:400px";
</dvt:pivotTable>

25.3.2 How to Resize Rows and Columns

The pivot table autosizes rows and columns during design. At runtime, you can change the size of rows or columns by dragging the row separator or the column separator to a new location.

To resize rows and columns at runtime:

  1. If you want to resize a row, do the following:

    1. Position the cursor in the row header on the separator between the row you want to resize and the next row.

    2. When the cursor changes to a double-sided arrow, click and drag to the desired location.

  2. If you want to resize a column, do the following:

    1. Position the cursor in the column header on the separator between the column you want to resize and the next column.

    2. When the cursor changes to a double-sided arrow, click and drag the column separator to the desired location.

25.3.2.1 What You May Need to Know About Resizing Rows and Columns

When you resize rows or columns, 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 and columns 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.

25.4 Customizing the Cell Content of a Pivot Table

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:

25.4.1 How to Create a CellFormat Object for a Data Cell

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:

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

  2. Pass the DataCellContext to a method expression for the dataFormat attribute of the pivot table.

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

25.4.2 Constructing 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.

25.4.3 Changing Format and Text Styles

Figure 25-6 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 spotlight and conditional formatting of data cells. For more information, see Section 25.4.4, "Creating Stoplight and Conditional Formatting in a Pivot Table".

Figure 25-6 Sales Data Per Product Category

Sales data per product category

Example 25-2 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 25-2 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;
  }

25.4.4 Creating Stoplight and Conditional Formatting in a Pivot Table

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 25-6 shows data cells with stoplight formatting for minimum, acceptable, and below standards sales for States.

Example 25-3 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 25-2 (which addresses rows with totals) with the code for stoplight and conditional formatting.

Example 25-3 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;
}