A pivot table displays a grid of data with rows and columns. Figure 27-1 shows a pivot table with multiple attributes nested on its rows and columns.

Pivot table data cells support other data display components such as sparkcharts, gauges, and graphs. Figure 27-2 shows a pivot table with sparkcharts illustrating data trends over time in a data cell.

Figure 27-3 shows a pivot table with graphs stamped in data cells.

Header and data cells in pivot tables can be customized to display image, icons or links, and to display stoplight and conditional formatting. Figure 27-4 shows a pivot table with conditional formatting to display levels of sales performance.

A pivot filter bar is a component that can be associated with a pivot table to provide the user with a way to filter pivot table data in layers not displayed in the row or column edges of the pivot table. Users can also drag and drop these layers between the pivot filter bar and the associated pivot table to change the view of the data. Figure 27-5 shows a pivot filter bar associated with a pivot table.

A pivot filter bar can also be used to change the display of data in a graph associated with the pivot table. Figure 27-6 shows a filtered view of quarterly sales data displayed simultaneously in a pivot table and on a bar graph.

The ADF Data Visualization pivot table component provides a range of features for end users, such as pivoting, sorting columns, and selection of one or more rows, columns, or cells, and then executing an application defined action on the selection. It also provides a range of presentation features, such as unlimited layers of hierarchically nested row header and column header cells.

You may find it helpful to understand other ADF Faces features before you implement your pivot table component. Additionally, once you have added a pivot table component to your page, you may find that you need to add functionality such as validation and accessibility.

Following are links to other functionality that pivot table components can use:

• You may want a pivot table to refresh a header cell, a data cell, or the entire pivot table to show new data based on an action taken on another component on the page. For more information, see Rerendering Partial Page Content.

• Personalization: If enabled, users can change the way the pivot table displays at runtime, and those values will not be retained once the user leaves the page unless you configure your application to allow user customization. For information, see Allowing User Customization on JSF Pages.

• Accessibility: Pivot table and pivot filter bar components are accessible, as long as you follow the accessibility guidelines for the component. See Developing Accessible ADF Faces Pages.

• Touch devices: When you know that your ADF Faces application will be run on touch devices, the best practice is to create pages specific for that device. For additional information, see Creating Web Applications for Touch Devices Using ADF Faces.

• Skins and styles: You can customize the appearance of pivot table and pivot filter bar components using an ADF skin that you apply to the application or by applying CSS style properties directly using a style-related property (styleClass or inlineStyle). For more information, see Customizing the Appearance Using Styles and Skins.

• Content Delivery: You can configure your pivot table and pivot filter bar to fetch data from the data source immediately upon rendering the components, or on a second request after the components have been rendered using the contentDelivery attribute. For more information, see Content Delivery.

• Automatic data binding: If your application uses the Fusion technology stack, then you can create automatically bound pivot tables based on how your ADF Business Components are configured. JDeveloper provides a wizard for data binding and configuring your pivot table. For more information, see the "Creating Databound Pivot Table and Pivot Filter Bar Components" chapter of Developing Fusion Web Applications with Oracle Application Development Framework.

  Note:

  If you know the UI components on your page will eventually use ADF data binding, but you need to develop the pages before the data controls are ready, then you should consider using placeholder data controls, rather than manually binding the components. Using placeholder data controls will provide the same declarative development experience as using developed data controls. For more information, see the "Designing a Page Using Placeholder Data Controls" chapter of Developing Fusion Web Applications with Oracle Application Development Framework.

Additionally, data visualization components share much of the same functionality, such as how data is delivered, automatic partial page rendering (PPR), image formats, and how data can be displayed and edited. For more information, see Common Functionality in Data Visualization Components.

You can use any row set (flat file) data collection to supply data to a pivot table. The pivot table component uses a data model to display and interact with data.

The pivot table (pivotTable) component has two child components, a header cell (headerCell) and a data cell (dataCell). The pivot filter bar (pivotFilterBar) is a sibling component that can be associated with the pivot table. These components are defined by several configurable elements.

The prefix dvt: occurs at the beginning of each pivot table and pivot filter bar component name indicating that the component belongs to the ADF Data Visualization Tools (DVT) tag library.

Pivot tables display data in a grid layout with unlimited layers of hierarchically nested row header cells and column header cells. Figure 27-26 shows a pivot table and its associated pivot filter bar displaying the sales of electronic equipment.

Pivot table and pivot filter bar components are defined by the following terms using the Electronic Sales Pivot Table in Figure 27-26:

• Edges: The axes in pivot tables, including:

  • Row edge: The vertical axis to the left or right for right-to-left display of the body of the pivot table. In Figure 27-26, the row edge contains two layers, Product Category and Product, and each row in the pivot table represents the combination of a particular category and a particular product.

  • Column edge: The horizontal axis above the body of the pivot table. In Figure 27-26, the column edge contains two layers, Measure and US State, and each column in the pivot table represents the combination of a particular measure value (Sales or Units), and a particular geographic location (US State).

  • Page edge: The edge represented by the pivot filter bar, whose layers can be filtered or pivoted with the layers in the row and column edges.

• Layers: Nested attributes that appear in a single edge. In Figure 27-26, the following two layers appear in the column edge: Measure and Geography (Sales and US State). The following two layers appear in the row edge: Category and Product (Product Category and Product).

• Header cell: The labels that identify the data displayed in a row or column. Row header cells appear on the row edge, and column header cells appear on the column edge. In the sample, header cells include Cell Phones, iPod Speakers, Sales, and Colorado.

• 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 1,499.99.

• QDR (Qualified Data Reference): A fully qualified data reference to a row, a column, or an individual cell. For example, in Figure 27-26, the QDR for the first data cell in the pivot table must provide the following information:

  • Category=Audio Video

  • Product=iPod Nano 1Gb

  • Measure=Sales

  • Geography=Colorado

  Likewise, the QDR for the first row in the pivot table, which is also the QDR of the "iPod Nano 1Gb" header cell, contains the following information:

  • Category=Audio Video

  • Product=iPod Nano 1Gb

  Finally, the QDR for the "Sales" header cell contains the following information:

  • Measure=Sales

When you are designing your page using simple UI-first development, you use the Components window to add a pivot table to the page. Once the pivot table is added to your page, you can use the Properties window to specify data values and configure additional display attributes for the pivot table.

1. In the ADF Data Visualization page of the Components window, from the Pivot Table panel, drag and drop a Pivot Table onto the page.
2. In the Properties window, view the attributes for the pivot table. Use the help button to display the complete tag documentation for the pivotTable component.
3. Expand the Data section. Use this section to set the following attributes:

   • Value: Specify an EL expression for the object to which you want the pivot table to be bound. Can be an instance of oracle.adf.view.faces.bi.model.DataModel.

   • Var and VarStatus: Use to specify a variable to access cell data in stamped dataCell and headerCell components. For more information, see Using var and varStatus Properties.

4. Expand the Appearance section. Use this section to set the following attributes:

   • LayerLabelMode: Use to configure the pivot table to always display the labels that appear above each row header layer and beside each column header layer. To configure persistent display of the row and column header labels for the pivotTable component, set the attribute to rendered. The default value is hidden.

   • PivotLabelVisible: Specify whether or not to display the labels on the pivot handles. The default value is true.

   • PreferredColumnHeaderHeight and PreferredRowHeaderWidth: As desired, use to specify the column header height and the row header width in percentages, for example 25% or 33.3%.

   • Sizing: Use to specify how the pivot table's size in width and height is determined. The default value is fixed where the pivot table is sized based on the parent component if the parent stretches its children, or the width and height CSS properties in its default skin, style class, or inline style if the parent does not stretch its children.

     You can also set the attribute to auto where if the parent stretches its children, the pivot table is sized in the same way as the fixed attribute. If the parent does not stretch its children, the pivot table will shrink to the size of its content if the content is smaller than the pivot table, specifically:

     width=min(content width, default width) and

     height=min(content height, default height)

     where "default width" and "default height" are the width and height CSS properties in its default skin, style class, or inline style.

     Note:

     When this attribute is set to auto, the pivot table frame will initially be displayed with the default size of the pivot table and then readjusted to fit its contents. This can cause the layout of the page displaying the pivot table to change after the page is initially displayed.

   • StatusBarRendered: Use to specify whether or not the pivot table status bar is display. The default value is false.

   • EmptyText: Enter the text to use to describe an empty pivot table. If the text is enclosed in an HTML tag, it will be formatted.

   • Summary: Enter a statement of the pivot table's purpose and structure for use by screen readers.

   • DataFormat and HeaderFormat: While a less preferred strategy to declaratively styling header and data cell stamps, you can use these attributes to create formatting rules to customize content in data and header cells. For more information, see Formatting Pivot Table Cell Content With CellFormat.

5. Expand the Behavior section. Use this section to set the following attributes:

   • PivotEnabled: Specify whether or not to allow the end user to reposition the view of the data in the pivot table. The default value is true.

   • ColumnFetchSize and RowFetchSize: Use to specify the number of columns and rows in a data fetch block. The default value for columns is 10 and the default value for rows is 25, which can be modified.

   • ContentDelivery: Use to specify how content will be delivered from the data source to the pivot table. The data can be delivered to the pivot table either by default as soon as the data is available (whenAvailable), immediately upon rendering (immediate), or lazily fetched after the shell of the component has been rendered (lazy). For more information about content delivery to pivot tables, see Content Delivery.

6. In the Structure window, right-click the dvt:pivotTable node and choose Insert Inside Pivot Table > Header Cell.
7. In the Structure window, right-click the dvt:pivotTable node and choose Insert Inside Pivot Table > Data Cell.

You can configure the pivot table, pivot filter bar, header cell and data cell's size and style using the inlineStyle or styleClass attributes.

Both attributes are available in the Style section in the Properties window for the dvt:pivotTable, dvt:pivotFilterBar, dvt:headerCell, or dvt:dataCell component. Using these attributes, you can customize stylistic features such as fonts, borders, and background elements.

You can also configure header cell and data cell child components using their styling attributes. For example, you can use custom CSS styling with inlineStyle and contentStyle attributes of a data cell af:outputText and af:inputText respectively:

<dvt:pivotTable id="goodPT"
                value="#{richPivotTableModel.dataModel}"
                var="cellData"
                varStatus="cellStatus">     	
  <dvt:dataCell id="dc1"> 
    <af:switcher id="sw1" facetName="#{richPivotTableModel.stampFacet}">
      <f:facet name="outputText">		
        <af:outputText id="ot1" value="#{cellData.dataValue}"
                       inlineStyle="#{myBean.textStyle}"/>    
      </f:facet>  
      <f:facet name="inputText">  
        <af:inputText id="ot2" value="#{cellData.dataValue}"
                      contentStyle="#{myBean.textStyle}" />   
      </f:facet>
        </af:switcher>
    </dvt:dataCell>
  </dvt:pivotTable>

Pivot tables and pivot filter bars also support skinning to customize many aspects of the display of data and header cells and labels, and pivoting and sorting icons.

For the complete list of pivot table skinning keys, see the Oracle Fusion Middleware Data Visualization Tools Tag Reference for Oracle ADF Faces Skin Selectors. For additional information about customizing your application using skinning and styles, see Customizing the Appearance Using Styles and Skins.

The page containing the pivot table may also impose limitations on the ability to change the size or style. For more information about page layouts, see Organizing Content on Web Pages.

When a pivot table component is inserted into a JSF page using the Components window, a basic pivot table tag is added to the source code as follows:

<dvt:pivotTable id="pt1"/>

If you have not already done so, you can then use the Components window to insert a header cell and data cell. Configure the cell content through stamping. For more information, see Configuring Header and Data Cell Stamps.

A Create Pivot Table wizard provides declarative support for data-binding and configuring the pivot table. In the wizard pages you can:

• Specify the initial layout of the pivot table

• Associate and configure a pivot filter bar

• Specify alternative labels for the data layers

• Configure insert or filter drilling

• Define aggregation of data values

• Configure category and data sorting

For more information, see the "Creating Databound Pivot Tables" chapter of Developing Fusion Web Applications with Oracle Application Development Framework.

For the complete list of pivot table skinning keys, see the Oracle Fusion Middleware Documentation Tag Reference for Oracle Data Visualization Tools Skin Selectors. To access the list from JDeveloper, from the Help Center, choose Documentation Library, and then Fusion Middleware Reference and APIs. For additional information about customizing your application using skins, see Customizing the Appearance Using Styles and Skins.

1. In ADF Data Visualization page of the Components window, from the Pivot Table panel, drag and drop a Header Cell or Data Cell onto the pivot table in the visual editor.
2. In the Structure window, right-click the dvt:headerCell or dvt:dataCell and choose insert inside Header Cell or insert inside Data Cell > ADF Data Visualization Components or ADF Faces.
3. In the Insert Item dialog, select the component you wish to stamp in the header or data cell.
4. In the Structure window, select the component you inserted, and in the Properties window, set the component attributes.

For the complete list of pivot filter bar skinning keys, see the Oracle Fusion Middleware Documentation Tag Reference for Oracle Data Visualization Tools Skin Selectors. To access the list from JDeveloper, from the Help Center, choose Documentation Library, and then Fusion Middleware Reference and APIs. For additional information about customizing your application using skins, see Customizing the Appearance Using Styles and Skins.

Selection in a pivot table 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. For example, to get the currently 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();
}

At runtime, selecting a data cell highlights the cell, as shown in Figure 27-11.

You can update pivot tables, data cells, and header cells by using partial page rendering (PPR). For example, you may display totals in a pivot table when triggered by a checkbox. PPR allows individual components on a page to be rerendered without the need to refresh the entire page. For more information about PPR, see About Partial Page Rendering.

For a component to be rerendered 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.

For example, you can update a pivot table by displaying the totals when a checkbox is triggered using the triggering component ID as the partialTriggers value.

<dvt:pivotTable id="goodPT"
    value="#{richPivotTableModel.dataModel}"
    partialTriggers="showTotals"/>

  <af:selectBooleanCheckbox id="showTotals" autoSubmit="true" label="Show Totals"
    value="#{richPivotTableModel.totalsEnabled}"/>

You can export the data from a pivot table to a Microsoft Excel spreadsheet. You create an action source, such as a button or link, add a exportPivotTableData component, and associate it with the data you wish to export. You can configure the component so that the entire pivot table will be exported, or so that only the rows, columns, or data cells selected by the user will be exported. For example, Figure 27-34 shows a pivot table that includes button components that allow users to export the data to an Excel spreadsheet.

At runtime, when the user clicks the button, by default all the rows and columns are exported in an Excel format written to the file specified in the filename attribute of the component. Alternatively, you can configure the exportPivotTableData component so that only user selections are exported, by setting the exportedData attribute to selected. For example, for the Export to Excel buttons:

<dvt:pivotTable id="pivotTableToExport"
               binding="#{editor.component}"
               contentDelivery="immediate"
               value="#{pivotTableExport.dataModel}" summary="pivot table"/>
 
<h:panelGrid id="pfl" columns="2" cellpadding="3">
  <af:button text="Export All" id="exportAll">
    <dvt:exportPivotTableData exportedId="pivotTableToExport" type="excelHTML"
                              exportedData="all" filename="all.xls"
                              title="All pivotTable data"/>
  </af:button>
  <af:button text="Export Selected" id="exportSelected">
    <dvt:exportPivotTableData exportedId="pivotTableToExport" type="excelHTML"
                              exportedData="selected" filename="selected.xls"
                              title="Selected pivotTable data"/>
  </af:button>
</h:panelGrid>

Figure 27-35 shows the resulting Excel spreadsheet when the Export All button is clicked.

ADF Faces allows you to output your JSF page from an ADF Faces web application in a simplified mode for printing. For example, you may want users to be able to print a page (or a portion of a page), but instead of printing the page exactly as it is rendered in a web browser, you want to remove items that are not needed on a printed page, such as scrollbars and buttons. For information about creating simplified pages for these outputs, see Using Different Output Modes .

When a pivot table and pivot filter bar is displayed on a JSF page to be output in printable pages:

• All data cells in the pivot table are displayed.

• Limited client interactivity including cell select and row or column resizing is supported.

• Pivoting, drilling, and sorting operations are not supported.

• Context menus including the ability to resize rows or columns is not supported.

• If configured, the pivot table data filter displayed in the pivot filter bar will be displayed, although the contents cannot be changed.

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.

You can apply header and data cell formatting styles to emphasize aspects of the data displayed in the pivot table. Figure 27-36 shows a pivot table with sales totals generated for products and for product categories. In the rows that contain totals, this pivot table displays text against a shaded background, a style change. This change shows 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 Configuring Stoplight and Conditional Formatting Using CellFormat .

For example, you can use this code to produce the required custom formats for the sales totals, but not for the stoplight formatting:

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.setStyle("background-color:#C0C0C0");
      }
     else if (product != null && product.toString().equals("Sales Total")
      {
       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");
      }
    }
    return null;
  }

The example includes the code for method expressions for both the dataFormat attribute and the headerFormat attribute of the dvt:pivotTable tag. If you want to include stoplight formatting in the pivot table, you might want to include the code from Configuring Stoplight and Conditional Formatting Using CellFormat .

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 follows:

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

For example, you can use this code to performs stoplight formatting in a pivot table that does not display totals:

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;
}

If you want to do stoplight formatting for a pivot table that displays totals, then you might want to combine the code from Specifying a Cell Format (which addresses rows with totals) with the code for stoplight and conditional formatting.