Creating pivot tables (Java)

The following figure shows the basic structural components of a pivot table. For IBM® SPSS® Statistics version 23, the IBM SPSS Statistics - Integration Plug-in for Java supports pivot tables with one row dimension and one column dimension. Each dimension contains a set of categories that label the elements of the dimension--for instance, row labels for a row dimension.

Each cell in the table can be specified by a combination of category values. In the example shown here, the indicated cell is specified by a category value of Male for the Gender dimension and Custodial for the Employment Category dimension.

Figure 1. Pivot table structure
Pivot table structure

Pivot tables are created with the PivotTable class, as shown in the following example. This example assumes that you are creating pivot table output for an external Java application that will invoke IBM SPSS Statistics, so that the output is routed with OMS.

String[] command={
	"OMS SELECT TABLES",
	"/IF SUBTYPES=['pivotTableDemo']",
	"/DESTINATION FORMAT=HTML OUTFILE='/output/pivottable.html'."
};
StatsUtil.submit(command);
Object[] rowLabels = new Object[] {"row1", "row2"};
Object[] colLabels = new Object[] { "columnA", "columnB"};                      
Object[][] cells = new Object[][] {{"1A","1B"}, {"2A","2B"}};
String title = "Sample pivot table";
String templateName = "pivotTableDemo";
String outline = "";
String caption = "";
String rowDim = "Row dimension";
String columnDim = "Column dimension";
boolean hideRowDimTitle = false;
boolean hideRowDimLabel = false;
boolean hideColDimTitle = false;
boolean hideColDimLabel = false;
PivotTable table = new PivotTable(cells, rowLabels, colLabels,
                        title, templateName, outline, caption, rowDim,
                        columnDim, hideRowDimTitle, hideRowDimLabel,
                        hideColDimTitle, hideColDimLabel, FormatSpec.COEFFICIENT);
table.createSimplePivotTable();
StatsUtil.submit("OMSEND.");

Result

Figure 2. Sample pivot table
Sample pivot table
  • In this example, pivot table output is routed to an external HTML file using OMS (Output Management System). The OMS command specifies that only tables with a table subtype of pivotTableDemo (to be defined below) will be included in the output. It also specifies the path to the output file and that the output will be rendered in HTML.
  • The command syntax for the OMS command is submitted to IBM SPSS Statistics. This starts an OMS session. The session is closed with the OMSEND command, which then writes the output to the destination file.

    Note: As an alternative to routing output with OMS, you can also specify output formats and destinations using an overloaded form of the StatsUtil.start method that accepts a string with output specifications. OMS offers greater flexibility but, for simple applications, specifying output on the start method may be sufficient.

  • To create a pivot table, you create an instance of the PivotTable class. The arguments are as follows:

    cells. This argument specifies the values for the cells of the pivot table, and must be given as a 2-dimensional array, where each element of the array specifies the cells in a given row of the pivot table. Only Double and String objects can be specified in this array.

    rowLabels. A 1-dimensional array of categories for the row dimension. Only Double and String objects can be specified in this array.

    colLabels. A 1-dimensional array of categories for the column dimension. Only Double and String objects can be specified in this array.

    title. A string specifying the title of the table.

    templateName A string that specifies the OMS (Output Management System) table subtype for this table. This value must begin with a letter, and have a maximum of 64 bytes. The table subtype can be used on the SUBTYPES keyword of the OMS command, as done here, to include this pivot table in the output for a specified set of subtypes.

    Note: By creating the pivot table instance within a startProcedure-endProcedure block, you can associate the pivot table output with a command name, as for pivot table output produced by syntax commands. The command name is the argument to the startProcedure function and can used on the COMMANDS keyword of the OMS command to include the pivot table in the output for a specified set of commands (along with optionally specifying a subtype as discussed above).

    outline. A string that specifies an optional outline title for the table. When routing output to an SPV file with OMS, or generating output in the Viewer for an extension command implemented in Java, the pivot table will be nested under an item with the outline name. When output is routed to OMS in OXML format, the outline title specifies a heading tag that will contain the output for the pivot table.

    caption. A string that specifies a table caption.

    rowDim. A string specifying the label for the row dimension.

    columnDim. A string specifying the label for the column dimension.

    hideRowDimTitle. A boolean specifying whether to hide the row dimension title.

    hideRowDimLabels. A boolean specifying whether to hide the row labels.

    hideColDimTitle. A boolean specifying whether to hide the column dimension title.

    hideColDimLabels. A boolean specifying whether to hide the column labels.

    format. Specifies the format to be used for displaying numeric values, including cell values, row labels, and column labels. The argument is specified as a FormatSpec enum.