24 Using Pivot Table Components

This chapter describes how to display data in pivot tables using the ADF Data Visualization pivotTable and pivotFilterBar components. If your application uses the Fusion technology stack, then you can use data controls to create pivot tables. For more information, see the "Creating Databound Pivot Tables" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

This chapter includes the following sections:

24.1 About the Pivot Table Component

Pivot tables display data in a grid layout with unlimited layers of nested rows and columns. Similar to spreadsheets, pivot tables provide the option of automatically generating subtotals and totals for grid data. The power of the pivot table's interactive capability is based in its display of multiple nested attributes on row and column headers. Users can dynamically change the layout of these attributes using drag-and-drop operations. A pivot table lets you pivot or reposition data labels and the associated data layer from one location on the row or column edge to another to obtain different views of your data, supporting interactive analysis.

A pivot filter bar is a component that can be added to a pivot table to provide the user with a way to filter pivot table data in layers not displayed in one of 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.

24.1.1 End User and Presentation Features of Pivot Table Components

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

24.1.1.1 Pivot Filter Bar

The data filtering capacity in a pivot table can be enhanced with an optional pivot filter bar. Zero or more layers of data not already displayed in the pivot table row edge or column edge are displayed in the page edge. Figure 24-22 shows a pivot filter bar with Quarter and Month layers that can be used to filter the data displayed in the pivot table.

Figure 24-1 Pivot Filter Bar with Data Layer Filters

Pivot filter bar with data layer filters.

24.1.1.2 Pivoting

You can drag any layer in a pivot table to a different location on the same edge, to the opposite edge, or to the associated pivot filter bar (if present), to change the view of the data in the pivot table. Any layer in a pivot filter bar can be dragged to a different location within the pivot filter bar, or to the row or column edge of the pivot table. This operation is called pivoting and is enabled by default.

When you move the mouse over a layer, the layer's pivot handle and an optional pivot label are displayed. If you move the mouse over the pivot handle, the cursor changes to a four-point arrow drag cursor. You can then use the handle to drag the layer to the new location. If you move the mouse over a layer on the row edge, the pivot handle appears above the layer, as shown in Figure 24-2.

Figure 24-2 Display of Pivot Handle on the Row Edge

Display of pivot handle on the row edge.

If you move the cursor over a layer in the column edge, the pivot handle appears to the left of the layer, as shown in Figure 24-3.

Figure 24-3 Display of Pivot Handle on the Column Edge

Display of pivot handle on the column edge.

If, in Figure 24-2, you drag the pivot handle of the Time (Year) layer from the row edge to the column edge between the Measure (Sales) layer and the Channel layer, the pivot table will change shape as shown in Figure 24-4.

Figure 24-4 Sales Pivot Table After Pivot of Year

Sales pivot table after pivot of year.

You can customize pivoting to disable pivot labels and pivoting. If both are disabled, the pivot handle does not display when mousing over the layer.

24.1.1.3 Editing Data Cells

Pivot tables can contain both read-only and editable data cells. Editable cells are those containing an input component, for example, af:inputText. When a pivot table containing editable cells is initially displayed, the first data cell is selected and the pivot table is open for editing. Users can initiate editing anywhere in the pivot table through a single click to overwrite the value in a cell, or double-click to edit the cell. Double-clicking in editable cells enables the user to identify a specific location within the cell, and then navigate within that cell using the arrow keys. Any edit performed on an editable cell can be reverted by pressing Esc.

While in editing mode, you can navigate through pivot table data cells using Tab, Enter or the arrow keys. To quickly navigate to the cell below or above the currently selected cell, use the arrow keys. When using the Enter key to navigate, an active link will automatically be launched for a cell containing an active link. When using Tab or Shift+Tab to navigate, data cells containing multiple editable components, as in the case of both an af:inputDate and date picker in the same cell, the Tab highlights each editable component in turn. When tabbing through the last column of the pivot table, the first column of the next row is highlighted, and when Shift-Tabbing through the first column in the pivot table, the last column of the previous row is highlighted. When using arrow keys to navigate, you can press F2 to navigate within a cell containing an af:inputText component, and Esc to return to arrow key data cell navigation. Any edits to a cell can be discarded by pressing Esc.

Once editing mode is initiated, users can navigate through read-only data cells to editable data cells, maintaining the editing mode. While an editable cell is selected, you can select other cells using Ctrl or Shift+click without enabling editing in the new cells and maintaining editing in the original cell.

To support rapid data entry, pivot tables also support pattern navigation using a single click or arrow key. For example, if a user clicks a cell for editing and then tabs through additional cells in a row, pressing Enter highlights the cell below the initial cell in the next row.

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.

24.1.1.4 Sorting

Pivot tables support sorting of data within the pivot table. When sorting is enabled, ascending and descending sort icons are displayed as the user hovers the mouse over the innermost layer of the column header. By default, the sortMode attribute of the pivotTable component is set to grouped, effectively sorting the data grouped by the second-to-innermost layer of the row edge. Figure 24-5 shows the sort icons in the World Sales column of the pivot table, where the data is grouped by Year, the second-to-innermost layer of the row edge.

Figure 24-5 Ascending and Descending Sorting Icons in a Pivot Table

Ascending and descending sorting icons in pivot table

24.1.1.5 Drilling

Pivot tables support two types of drilling including insert drilling, and filter drilling. With insert drilling. the expand operation reveals the detail data while preserving the sibling and aggregate data. With filter drilling, the expand operation displays the detail data only, filtering out sibling and aggregate data.

For example, Figure 24-6 and Figure 24-7 illustrate how drilling is used to display product data within each year; revealing that the 2007 total sales number of 52,500 is composed of 25,500 for tents and 27,000 for canoes. This total contributes to the aggregated total of all sales for all years of 128,172. Figure 24-6 shows a pivot table using insert drilling with the total number of 52,500 displayed alongside the detail numbers. The data for other years and the aggregated total for all years is also available.

Figure 24-6 Pivot Table with Insert Drilling Enabled

pivot table with insert drilling

Figure 24-7 shows a pivot table using filter drilling with only the detail numbers are displayed. The numbers for other years, and the aggregated total for all years is filtered out.

Figure 24-7 Pivot Table with Filter Drilling Enabled

pivot table with filter drilling enabled.

At runtime, a drill icon is enabled in the parent attribute display label for both types of drilling.

24.1.1.6 On Demand Data Scrolling

Pivot tables support on-demand data scrolling for in order to support large data sets while maintaining performance. Only the data that is scrolled into view in the pivot table is loaded. As the user scrolls vertically or horizontally, data is fetched for the portion of the pivot table that has scrolled into view, and data that is no longer needed is discarded. Figure 24-8 shows a pivot table with a large data set using on-demand data scrolling.

Figure 24-8 On-Demand Data Scrolling in a Pivot Table

on-demand data scrolling in a pivot table

24.1.1.7 Sizing

The default size of a pivot table is a width of 300 pixels and a height of 300 pixels. The pivot table autosizes rows, columns, and layers within the space allowed when the pivot table is initially displayed. At runtime, you can change the size of rows, columns, or layers by dragging the row, column, or layer separator to a new location. You position the cursor in the row or column header on the separator between the row, column, or layer you want to resize and the next row, column, or layer. When the cursor changes to a double-sided arrow, click and drag the row, column, or layer separator to the desired location.

When you resize rows, columns, or layers, the new sizes remain until you perform 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 (Metadata Services) customization.

24.1.2 Pivot Table and Pivot Filter Bar Component Use Cases and Examples

A pivot table display a grid of data with rows and columns.Figure 24-9 shows a pivot table with multiple attributes nested on its rows and columns.

Figure 24-9 Sales Pivot Table with Multiple Rows and Columns

sales pivot table with rows and columns

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 24-10 Figure 24-10 shows a pivot filter bar associated with a pivot table.

Figure 24-10 Pivot Filter Bar Component Associated with Pivot Table

Pivot filter bar component

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

Figure 24-11 Pivot Table with Sparkcharts Stamped in Data Cells

Pivot table with stamped sparkcharts

Figure 24-12 shows a pivot table with gauges stamped in data cells.

Figure 24-12 Pivot Table with Gauges Stamped in Data Cells

pivot table with gauges stamped in data cells

Figure 24-13 shows a pivot table with graphs stamped in data cells.

Figure 24-13 Pivot Table with Graphs Stamped in Data Cells

Pivot table with graphs stamped in data cells

Pivot tables can support editable data cells. Editable cells are those containing an input component, for example, af:inputText. At runtime, editing can be initiated anywhere in the pivot table by single click for direct editing or double-clicking to edit in a cell. Figure 24-14 shows a pivot table data cell open for direct editing.

Figure 24-14 Data Cell Open for Direct Editing

Data cell open for direct editing.

Data cells selected for dropdown list editing are displayed as shown in Figure 24-15.

Figure 24-15 Data Cell Open for Dropdown List Editing

Data cell open for dropdown list editing.

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

Figure 24-16 Conditional Data Cell Formatting

Conditional Data Cell Formatting

24.1.3 Additional Functionality for the Pivot Table Component

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:

  • Partial page rendering: 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 Chapter 8, "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 Chapter 32, "Allowing User Customization on JSF Pages."

  • Accessibility: You can make your pivot table and pivot filter bar components accessible. For more information, see Chapter 30, "Developing Accessible ADF Faces Pages."

  • 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 Chapter 28, "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 Section 12.2.2, "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. For more information, see the "Creating Databound Pivot Tables" section of the Oracle Fusion Middleware Fusion Developer's Guide for 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 the Oracle Fusion Middleware Fusion Developer's Guide for 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 Section 21.2, "Common Functionality in Data Visualization Components."

24.2 Using the Pivot Table Component

The pivot table component uses a data model to display and interact with data. The specific model class used is oracle.adf.view.faces.bi.model.pivotTable.PivotTableModel.

Note:

If you are linking a pivot table and a graph to the same set of data, the model class oracle.adf.view.faces.bi.model.DataModel should be used

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 rows and columns, specifying drill operations at runtime, defining how to aggregate duplicate records, and setting up initial sort criteria.

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.

24.2.1 Configuring Pivot Tables

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. 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 nested rows and columns. Figure 24-17 shows a pivot table and its associated pivot filter bar displaying the sales of electronic equipment.

Figure 24-17 Electronic Sales Pivot Table

Electronic sales pivot table and pivot filter bar.

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

  • Edges: The axes in pivot tables, including:

    • Row edge: The vertical axis to the left of the body of the pivot table. In Figure 24-17, 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 24-17, 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 24-17, 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 24-17, 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

24.2.2 How to Add a Pivot Table to a Page

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

In the Property Inspector you can use the dropdown menu for each attribute field to display a property description and options such as displaying an EL Expression Builder or other specialized dialogs. Figure 24-18 shows the dropdown menu for a pivot table component value attribute.

Figure 24-18 Pivot Table Value Attribute Dropdown Menu

Pivot table value attribute dropdown menu

Note:

If your application uses the Fusion technology stack, then you can use data controls to create a pivot table and the binding will be done for you. JDeveloper provides a wizard for data binding and configuring your pivot table. For more information, see the "Creating Databound ADF Pivot Tables" chapter in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

Before you begin:

It may be helpful to have an understanding of how pivot table attributes and child tags can affect functionality. For more information, see Section 24.2.1, "Configuring Pivot Tables."

You may also find it helpful to understand functionality that can be added using other ADF Faces features. For more information, see Section 24.1.3, "Additional Functionality for the Pivot Table Component."

To add a pivot table to a page:

  1. In the Component Palette, from the ADF Data Visualizations page, in the Pivot Table panel, drag and drop a Pivot Table onto the page.

  2. In the Property Inspector, 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.pivotTable.PivotTableModel or
      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 Section 24.3.2, "What You May Need to Know About Using var and varStatus Properties."

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

    • DataFormat and HeaderFormat: Use to create formatting rules to customize content in data and header cells. For more information, see Section 24.5, "Customizing Pivot Table Cell Content."

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

    • 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 width and height CSS properties in its default style or inline style property.

      You can also set the attribute to auto where the height of the pivot table is determined by the size of the content that is being displayed by the pivot table. If the content is smaller than the default size of the pivot table, then the pivot table will shrink. If the content is larger than the default size of the pivot table, the pivot table will grow.

      You can control the maximum height and width of the pivot table by using the inlineStyle property as follows:

      max-width:400px, max-height:300px
      

      Once the maximum height or width is exceeded, then pivot table will display scrollbars as needed.

      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.

  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. If you choose to disallow pivoting, you should also set the PivotLabelVisible attribute to false.

    • 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. For more information about content delivery to pivot tables, see Section 21.2.1, "Content Delivery."

24.2.3 What Happens When You Add a Pivot Table to a Page

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

<dvt:pivotTable id="pt1"/>

You can then use the Component Palette to insert a header cell and data cell and then configure the cell content through stamping. For more information, see Section 24.3, "Using Stamping in Pivot Tables."

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

  • View a live data preview of the pivot table

For more information, see the "Creating Databound ADF Pivot Tables" chapter in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

24.3 Using Stamping in Pivot Tables

Each immediate child of a pivot table component must be either a headerCell or dataCell component. The pivot table can contain at most one headerCell and at most one dataCell component. These components make it possible to customize the cell content through stamping. When you use stamping, child components are not created for every header cell or data cell in a pivot table. Rather, the content of the component is repeatedly rendered, or stamped, once per cell, such as the rows in the pivot table.

Each time a header or data cell is stamped, the value for the current cell is copied into a var property, and additional data for the cell is copied into a varStatus property. These properties can be accessed in EL expressions inside the header or data cell component, for example, to pass the cell value to a stamped af:outputText component. Once the pivot table has completed rendering, the var and varStatus properties are removed, or reverted back to their previous values.

24.3.1 How to Configure Header and Data Cells as Stamps

Only certain types of child components are supported by header cells or data cells. For example, each header cell can contain read-only components. Each data cell can contain read-only or input components, including all components with no activity and most components that implement the EditableValueHolder or ActionSource interfaces.

Header cells and data cells should have only one child component. If multiple children are desired, they should be wrapped in another component. If no layout is desired, af:group can be used, which simply renders its children without adding layout, and is consequently lightweight. If layout is desired, a layout component like af:panelGroupLayout can be used instead. For more information, see Section 9.13, "Grouping Related Items."

Data cell editing is enabled by using an input component as the child component of dataCell. At runtime you can open the cell for editing by single or double-clicking the cell in the pivot table. For more information, see Section 24.1.1.3, "Editing Data Cells."

Example 24-1 shows a code sample for configuring header cell stamping using af:switcher to vary the type of stamped component by layer name, that is, a different content for Geography, Channel, and so on. The example also illustrates components that can be used as children of headerCell.

Example 24-1 Code Sample for Header Cell Stamping

<dvt:pivotTable id="goodPT"
                inlineStyle="width:100%;height:600px;"
                binding="#{editor.component}"
                contentDelivery="immediate"
                value="#{pivotTableHeaderCellDemo.dataModel}"
                headerFormat="#{pivotTableHeaderCellDemo.getHeaderFormat}"
                dataFormat="#{pivotTableHeaderCellDemo.getDataFormat}"
                var="cellData"
                varStatus="cellStatus"
                summary="pivot table">
  <dvt:headerCell id="goodHC>
    <af:switcher id="sw" facetName="#{cellData.layerName}" defaultFacet="Other">
      <f:facet name="Geography">
        <af:group id="g1">
          <af:icon id="idicon11" name="info" shortDesc="Icon" />
          <af:outputText  value="#{cellData.dataValue}" id="ot11"
                          shortDesc="#{cellData.dataValue}" />
        </af:group>
      </f:facet>
      <f:facet name="Channel">
        <af:group id="g2">
          <af:panelGroupLayout id="pgl2" layout="vertical">
          <af:commandImageLink shortDesc="Sample commandImageLink"
                   icon="/images/pivotTableCSVDemo/smily-normal.gif"
                   hoverIcon="/images/pivotTableCSVDemo/smily-glasses.gif"
                   id="cil1"/>
          <af:outputText  value="#{cellData.dataValue}" id="ot1" />
        </af:group>
        <af:commandButton text="Go to Tag Guide page" immediate="true"
                          action="guide" id="cb1"/>
          </af:panelGroupLayout>
      </f:facet>
      <f:facet name="Product">
        <af:panelGroupLayout id="pgl3" layout="vertical">
          <af:outputText value="#{cellData.dataValue}" id="ot12" />
          <af:commandButton text="Go to Tag Guide page" immediate="true"
                                                        action="guide" id="cb2"/>
        </af:panelGroupLayout>
      </f:facet>
      <f:facet name="Other">
        <af:commandLink text="#{cellData.dataValue}"
                        shortDesc="#{cellData.dataValue}" immediate="true"
                        action="guide" id="idcommandlink11"/>
      </f:facet>
    </af:switcher>
  </dvt:headerCell>
</dvt:pivotTable>

Figure 24-19 shows the resulting pivot table for the code sample.

Figure 24-19 Pivot Table Header Cell Stamps

Pivot table with customized header cell

Example 24-2 shows a code sample for configuring data cell stamping using af:switcher to vary the type of stamped component by measure, that is, a different content for Sales, Weight, and so on. The example also illustrates components that can be used as children of dataCell.

Example 24-2 Code Sample for Data Cell Stamping

<dvt:pivotTable id="goodPT" var="cellData" varStatus="cellStatus">
  <dvt:dataCell>
    <af:switcher id="sw" facetName="#{cellStatus.members.MeasDim.value}"
                 defaultFacet="Other">
      <f:facet name="Sales">
        <af:inputText id="idinputtext1" value="#{cellData.dataValue}"   />
      </f:facet>
      <f:facet name="Units">
        <af:inputText id="idinputtext2" value="#{cellData.dataValue}"   >
          <af:validateLength maximum="6" minimum="2" />
        </af:inputText>
      </f:facet>
      <f:facet name="Weight">
        <af:outputText id="idoutputtext1" value="#{cellData.dataValue}" />
      </f:facet>
      <f:facet name="Color">
        <af:selectOneChoice id="idselectonechoice"
                            value="#{cellData.dataValue}" label="Color">
          <af:selectItem label="red" value="red" shortDesc="shortDesc sample"/>
          <af:selectItem label="coffee" value="coffee"
                         shortDesc="Sample shortDesc text"/>
          <af:selectItem label="milk" value="milk"
                         shortDesc="Another shortDesc sample"/>                            
        </af:selectOneChoice>
      </f:facet>
      <f:facet name="Available">
        <af:selectBooleanCheckbox id="idselectbooleancheckbox"
                                  label="Availability" text="Item Available"
                                  autoSubmit="true"
                                  value="#{cellData.dataValue}"/>
      </f:facet>
      <f:facet name="Supply Date">
        <af:inputDate id="idinputdate1" value="#{cellData.dataValue}"
                      label="Change Date:" simple="true" >
          <af:validateDateTimeRange maximum="2020-12-31" minimum="1980-12-31" />
        </af:inputDate>
      </f:facet>
      <f:facet name="Link">
        <af:commandLink text="#{cellData.dataValue}" immediate="true"
                        action="guide" id="idcommandlink"/>
      </f:facet>
      <f:facet name="Size">
        <af:inputComboboxListOfValues label="Size"id="idInputComboboxListOfValues"
                                   value="#{cellData.dataValue}"
                                   searchDesc="Search Size"
                                   model="#{pivotTableEditBean.listOfValuesModel}"
                                   columns="3"   />
      </f:facet>
      <f:facet name="Other">
        <af:outputText id="idoutputtext2" value="#{cellData.dataValue}"   />  
      </f:facet>
    </af:switcher>
  </dvt:dataCell>
</dvt:pivotTable>

Figure 24-20 shows the resulting pivot table for the code sample.

Figure 24-20 Pivot Table Data Cell Stamps

Pivot table data cell stamps

You can also specify header and data cell CSS styling using the style attributes of their child components. shows custom CSS styling using inlineStyle and contentStyle attributes of af:outputText and af:inputText respectively.

Example 24-3 Code Sample for Data Cell CSS Styling

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

Before you begin:

It may be helpful to have an understanding of how pivot table attributes and child tags can affect functionality. For more information, see Section 24.2.1, "Configuring Pivot Tables."

You should already have a pivot table on your page. If you do not, follow the instructions in this chapter to create a pivot table. For more information, see Section 24.2.2, "How to Add a Pivot Table to a Page."

To add and configure a header or data cell stamp:

  1. In the Component Palette, from the ADF Data Visualizations page, in 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 dvt:headerCell or insert inside dvt:dataCell > 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 Property Inspector, set the component attributes.

24.3.2 What You May Need to Know About Using var and varStatus Properties

Pivot table var and varStatus properties are used to access cell data in stamped dataCell and headerCell components. The var property names the EL expression variable used to reference cell data within pivot table data cell stamps. In the stamped dataCell or headerCell component, the var property must be referenced and followed by a metadata keyword. The optional varStatus property names the EL expression variable used to provide contextual information about the state of the component. In stamped dataCell or headerCell components, the varStatus property must be referenced and followed by one of the following:

  • members: Valid only for the dataCell component. Provides access to the header cells corresponding to the same row or column as the current data cell.

  • model: Returns the DataModel for this component.

  • cellIndex: Returns the cell index for this component.

  • cellKey: Returns the cell key for this component.

Example 24-4 shows a code sample for using var and varStatus to access data from a stamped data cell.

Example 24-4 Code Sample for Data Cell Stamping

<dvt:pivotTable id="pt1" var="cellData" varStatus="cellStatus">
  <dvt:dataCell>
    <af:outputText id="ot1" value="#{cellData.dataValue}"/>
                   inlineStyle="color:#{(cellStatus.members.Product.dataValue == 'Canoes' ? 'red' : 'blue')};"/>
  </dvt:dataCell>
</dvt:pivotTable>

The code sample illustrates the syntax for using each data cell value property as follows:

  • var: [var property].[data cell metadata keyword]

    In the code sample, the value of af:outputText is set to #{cellData.dataValue}, the value of the current cell.

  • varStatus: [varStatus property].[members, model, cellIndex, or cellKey].[layer name].[header cell metadata keyword]

    The data cell component value references the pivot table varStatus (cellStatus) followed by members to access the header cells corresponding to the same row or column as the current data cell, followed by the name of the layer (Product) containing the desired header cell, followed by the header cell metadata keyword dataValue.

Table 24-1 shows the metadata keywords supported for data cells in a rowset data model.

Table 24-1 Supported Metadata Keywords for Data Cells

Keyword Description

dataValue

Most frequently useful keyword. Returns the data value Object for the current cell. To specify the object's accessible field through EL Expression, use the setting dataValue.fieldName.

dataCubeMax and dataCubeMin

Returns a number that is the maximum and minimum, respectively, for the measure of the cell across the values in the cube.

dataIsTotal

Returns a Boolean true if this cell is an aggregate.

dataAggregates

If the cell is an aggregate, returns a List<String,Object> of the column and value pairs representing the cells (non-aggregate) that make up the aggregation for the given cell.

aggregateCollection

If the cell is an aggregate, returns the List<String,Object> of the column and value pairs in the cube that make up the cell's aggregate value. Note that aggregateCollection is post-cube and dataAggregates is not.

dataRow

Returns a Map<String,Object> from attribute name to data Object in the original row mapping. Usage: dataRow.foo, where "foo" is one of the rowset attribute (column) names.

dataTypeColumn

Returns a String representing the name of the rowset attribute from which the value comes.

dataRowKey

Returns the row data model's ADF model row key,

dataKeyPath

Returns the ADF model key path object.


Table 24-2 shows the metadata keywords supported for header cells in a rowset data model.

Table 24-2 Supported Metadata Keywords for Header Cells

Keyword Description

dataValue

Most frequently useful keyword. Returns the data value Object for the current cell. To specify the object's accessible field through EL Expression, use the setting dataValue.fieldName.

value

Returns the String value of the header cell. Also available in cubic data models.

label

Returns the String label for the header cell. Also available in cubic data models.

isTotal

Returns a Boolean true if the header cell represents an aggregate.

drillState

Returns an Integer value representing the drill state of the current header cell, if applicable. 0 indicates "not drillable", 1 indicates "drillable", and 2 indicates "drilled". Also available in cubic data models

memberMetadataColumn

Returns the String attribute column of the header cell.

layerName

Returns a String representing the name of the layer containing the header cell.

layerLabel

Returns a String representing the label (if any) for the layer containing this header cell. May fall back to layerName.


Example 24-5 shows a code sample using sparkcharts stamped in data cells. The resulting pivot table is shown in Figure 24-11.

Example 24-5 Code Sample for Stamping Sparkcharts in Data Cells

<dvt:pivotTable id="pivotTable1"
                value="#{pivotTableSparkChart.dataModel}"
                var="cellData"
                varStatus="cellStatus">
  <dvt:dataCell>
    <af:switcher id="s2"
                 facetname="O___b_cellData_dataIsTotal__b__"
                 defaultFacet="false">
      <f:facet name="true">
        <dvt:sparkChart id="sc1" shortDesc="Spark Chart"
                        highMarkerColor="#008200"
                        lowMarkerColor="#ff0000">
          <af:iterator id="i1"
                       value="#{cellData.aggregateCollection}"
                       var="sparks" >
            <dvt:sparkItem id="si1"
                           value="#{sparks.dataValue}"/>
          </af:iterator>
        </dvt:sparkChart>
      </f:facet>
      <f:facet name="false">
        <af:outputText id="ot1" value="#{cellData.dataValue}"/>
      </f:facet>
    </af:switcher>
  </dvt:dataCell>
 
  <dvt:headerCell>
    <af:switcher id="s3"
                 facetname="O___b_cellData_isTotal__b__"
                 defaultFacet="false">
      <f:facet name="true">
        <af:outputText id="ot2" value="Trend"/>
      </f:facet>
      <f:facet name="false">
        <af:outputText id="ot3" value="#{cellData.dataValue}"/>
      </f:facet>
    </af:switcher>
  </dvt:headerCell>
</dvt:pivotTable>

Example 24-6 shows a code sample for using gauges in data cells. The resulting pivot table is displayed in Figure 24-13.

Example 24-6 Code Sample for Stamping Gauges in Data Cells

<dvt:pivotTable
                id="pivotTable2"
                value="#{pivotTableGauge.dataModel}"
                var="cellData"
                varStatus="cellStatus">
  <dvt:dataCell>
    <dvt:gauge id="g1" shortDesc="Gauge"
               imageWidth="80" imageHeight="80" imageFormat="PNG_STAMPED"
               value="#{cellData.dataValue}"
               minValue="#{cellData.dataCubeMin}"
               maxValue="#{cellData.dataCubeMax}"/>
  </dvt:dataCell>
</dvt:pivotTable>

Example 24-7 shows a code sample for formatting based on pivot table header cells.

Example 24-7 Code Sample for Formatting Based on Header Cells

<dvt:pivotTable
                id="pivotTable3"
                value="#{pivotTableMemberFormatting.dataModel}"
                var="cellData"
                varStatus="cellStatus">
  <dvt:headerCell>
    <af:switcher 
                 facetname="O___b_cellData_layerName__b__"
                 defaultFacet="Other">
      <f:facet name="Product">
        <af:outputText id="ot1"
                       value="#{cellData.dataValue}"
                       inlineStyle="color:#{(cellData.dataValue == 'Canoes' ? 'red' : 'blue')};"/>
      </f:facet>
      <f:facet name="Other">
        <af:outputText id="ot2" value="#{cellData.dataValue}"/>
      </f:facet>
    </af:switcher>
  </dvt:headerCell>
 
  <dvt:dataCell>
    <af:outputText id="ot3" value="#{cellData.dataValue}"
                   inlineStyle="color:#{(cellStatus.members.Product.dataValue == 'Canoes' ? 'red' : 'blue')};"/>
  </dvt:dataCell>
</dvt:pivotTable>

Figure 24-21 shows the pivot table resulting from the code sample for formatting based on header cells.

Figure 24-21 Formatting Based on Header Cells

Formatting based on header cells

24.4 Using a Pivot Filter Bar with a Pivot Table

You can enhance the data filtering capacity in a pivot table by adding a pivot filter bar. Zero or more layers of data not already displayed in the pivot table row edge or column edge are displayed in the page edge. Figure 24-22 shows a pivot filter bar with Quarter and Month layers that can be used to filter the data displayed in the pivot table.

Figure 24-22 Pivot Filter Bar with Data Layer Filters

Pivot filter bar with data layer filters.

You can also change the display of data in the pivot table by pivoting layers between the row, column, or page edges. Use the pivot handle to drag the layers between the edges as desired. Figure 24-23 shows the modified pivot table and pivot filter bar when the Channel data layer is pivoted to the page edge.

Figure 24-23 Pivot Table and Pivot Filter Bar After Pivot

Pivot table and filter bar after pivot.

24.4.1 How to Associate a Pivot Filter Bar with a Pivot Table

You associate a pivot filter bar component, pivotFilterBar, to work with a pivot table component, pivotTable, by configuring the data model and associated properties to work with both components. Example 24-8 shows a code sample for associating a pivot filter bar with a pivot table.

Example 24-8 Code Sample for Pivot Filter Bar

<dvt:pivotFilterBar id="pf1" value="#{binding.pt.pivotFilterBarModel}"
     modelName="pt1Model"/>
<dvt:pivotTable id="pt1" value="#{binding.pt.dataModel}"  modelName="pt1Model"
     partialTriggers="pf1"/>

You can associate a pivot filter bar with a pivot table in any of the following ways:

  • Create a pivot table using the Data Controls Panel.

    When you drag a data collection from the Data Controls Panel to create a pivot table on your page, the Select Display Attributes page of the Create Pivot Table wizard provides the option to create a pivot filter bar to associate with the pivot table. You can choose to specify zero or more attributes representing data layers in the page edge. The data model and associated properties are automatically configured for you. For detailed information, see the "Creating Databound ADF Pivot Tables" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

  • Add a pivot filter bar to a pivot table bound to data.

    From the ADF Data Visualizations page of the Component Palette, Pivot Table panel, you can drag a pivotFilterBar element adjacent to a pivotTable element that has been bound to a data collection and the data binding will be done for you.

  • Add a pivot filter bar to a pivot table not bound to data.

    From ADF Data Visualizations page of the Component Palette, Pivot Table panel, you can drag a pivotFilterBar element adjacent to a pivotTable element that has not been bound to a data collection. In this instance, you must configure the data model and associated properties in order for the pivot filter bar to work with the pivot table.

24.5 Customizing Pivot Table Cell Content

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 pivotTable component:

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

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

An instance of a CellFormat object lets you specify an argument to change the CSS style of a cell. For example, you might use this argument to change the background color of a cell.

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

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.

24.5.2 How to Change Cell Format

You can apply header and data cell formatting styles to emphasize aspects of the data displayed in the pivot table. Figure 24-24 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 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 Section 24.5.3, "How to Create Stoplight and Conditional Formatting in a Pivot Table."

Figure 24-24 Sales Data Per Product Category

Sales data per product category

Example 24-9 shows sample code that produces the required custom formats for the sales totals, but not for the stoplight formatting. 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 Example 24-10.

Example 24-9 Sample Code to Change 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.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", 
                              "font-weight:bold");
      }
    }
    return null;
  }

24.5.3 How to Create 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 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 24-24 shows data cells with stoplight formatting for minimum, acceptable, and below standards sales for States.

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

Example 24-10 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;
}

24.6 Using Selection in Pivot Tables

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. Example 24-11 shows sample code for getting the currently selected header cells.

Example 24-11 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();
}

At runtime, selection in a data cell highlights the cell, as shown in Figure 24-25.

Figure 24-25 Selected Data Cell

Selected data cell

24.7 Updating Pivot Tables with Partial Page Rendering

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 re-rendered without the need to refresh the entire page. For more information about PPR, see Chapter 8, "About Partial Page Rendering."

Note:

By default, ADF pivot tables support automatic PPR, where any component whose values change as a result of backend business logic is automatically rerendered. If your application uses the Fusion technology stack, you can enable the automatic partial page rendering feature on any page. For more information, see the "What You May Need to Know About Automatic Partial Page Rendering" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

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.

Example 24-12 shows sample code for updating a pivot table by displaying the totals when a checkbox is triggered. The triggering component uses the ID as the partialTriggers value.

Example 24-12 Partial Update of a Pivot Table

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

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

24.8 How to Export from a Pivot Table

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

Figure 24-26 Pivot Table with Export to Excel Command Buttons

pivot table with export to excel buttons

At runtime, when the user clicks the command 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 the rows the user selects are exported, by setting the exportedData attribute to selected. Example 24-13 shows the code sample for the Export to Excel command buttons.

Example 24-13 Code Sample for Export to Excel Command Button

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

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

Figure 24-27 Pivot Table Export to Excel Spreadsheet

pivot table export to excel spreadsheet

Note:

You may receive a warning from Excel stating that the file is in a different format than specified by the file extension. This warning can be safely ignored.