Controlling the Size and Number of Unique Values in Pivot Tables

You can use the parameters listed in Table 3-3. Server configuration parameters for controlling pivot tables to control the size and number of unique values in a pivot table effectively.

Table 3-3. Server configuration parameters for controlling pivot tables

Parameter

Description

Default Value

dashboard.Pivotdataset-Max-Rows

Determines the maximum number of rows in the List display mode of a pivot table portlet. See Figure 3-6. List display mode of a pivot table portlet for more details.

25,000

dashboard.Pivotdataset-Max-Cells

Determines the maximum number of cells (number of rows * number columns) in the List display mode of a pivot table portlet. See Figure 3-6. List display mode of a pivot table portlet for more details.

120,000

dashboard.Pivotdataset-Max-Distinct-In-Column

Determines the maximum number of unique values in any column of a pivot table. See Figure 3-7. Pivot table display mode of a pivot table portlet for more details.

50

dashboard.Pivotdataset-Max-Distinct-Cells

Determines the maximum number of unique aggregations (product of numbers of unique values in each column) in a pivot table. See Figure 3-7. Pivot table display mode of a pivot table portlet for more details.

10,000

Note: The maximum number of rows in a pivot table is also affected by the PORTLET_MAX_ROWS_RETURNED parameter, which determines the maximum number of rows to display in a portlet. The default value for this parameter is 200, meaning that if you have more than 200 rows to display in a pivot table portlet, you must increase the value of the PORTLET_MAX_ROWS_RETURNED parameter accordingly.

Caution:

  • Performance issues may occur if the value you set for any parameter exceeds the corresponding default value.

  • Performance varies with hardware, software, and system configurations in your environment. If performance degrades when system load reaches the default values, we recommend that you lower the values of the parameters.

  • If a pivot table has a very large volume of data, we recommend that you NOT switch between columns and rows in the table.

Figure 3-6. List display mode of a pivot table portlet

In Figure 3-6. List display mode of a pivot table portlet, the number of rows in the List display mode of the pivot table portlet is five. If the value of Pivotdataset-Max-Rows is lower than five, you receive an error message, advising you to limit the number of rows.

In Figure 3-6. List display mode of a pivot table portlet, the number of cells in the List display mode of a pivot table portlet is 15 (5 * 3). If the value of Pivotdataset-Max-Cells is lower than 15, you receive an error message advising you to limit the number of cells.

Figure 3-7. Pivot table display mode of a pivot table portlet

In this pivot table, column WORK ITEM has eight unique values, which is the largest number of unique values among all columns. If the value of
Pivotdataset-Max-Distinct-In-Column is lower than eight, you receive an error message, advising you to limit the number of unique values in this column.

In this pivot table, column WORK ITEM has eight unique values, and column WORK ITEM TYPE has four unique values. Therefore, the number of unique aggregations is 32 (8 * 4). If the value of Pivotdataset-Max-Distinct-Cells is lower than 32, you receive an error message, advising you to limit the number of aggregations in this table.

Note that if a pivot table is larger than 2 MB, the table cannot be exported to Excel. This is caused by a default setting in Apache Tomcat. In Apache Tomcat, the maxPostSize attribute determines the maximum size in bytes of the POST, which is to be handled by the container FORM URL parameter parsing. If not specified, this attribute is set to 2097152 (2 MB), which limits the size of the pivot table.

To work around this limitation, edit the Tomcat's server.xml file to add the maxPostSize attribute to the Connector element, and then set the attribute to a larger value in bytes. Alternatively, set the attribute to 0, which disables the size check.