Modifying Pivot Tables

When you first create a pivot table, the default settings specified in the data source configuration are used to create the table.

Using the Configure Pivot Table and Chart Style sidebars, you can alter the rows, columns, metrics and metric layouts, and horizontal scroll bar used in a pivot table. In addition, you can sort the data in the table and rearrange the table fields.

You can use Keyboard Controls on the Configure Pivot Table sidebar instead of a mouse.

See the following topics:

Modifying Pivot Table Rows

  1. Select Configure on the chart drop-down menu. The Configure Pivot Table sidebar appears showing the current settings for the table.

  2. To modify the rows in the pivot table:

    • Click in the Rows area of the Configure Pivot sidebar. The sidebar changes to show all the possible rows for the table.
    • Select the rows you want to add and clear the ones you want to remove. If you want to select all of the rows, click Select All. Use the search bar at the top of the sidebar to search for a field. When you click on the search bar, a filter drop-down menu appears so you can list only fields of a specific type (Number, Attribute, or Time).
    • Click OK.

    If the field you select is a time field, you can modify its granularity and indicate whether or not blanks (even time intervals) should be included in the time field values.

    If more than one row is listed, you change their order by dragging and dropping them into the order you want. Click the

  3. Click Apply to apply your changes to the pivot table.

  4. Save the dashboard.

Modifying Pivot Table Columns

  1. Select Configure on the chart drop-down menu. The Configure Pivot Table sidebar appears showing the current settings for the table.

  2. To modify the columns in the pivot table:

    • Click in the Columns area of the Configure Pivot sidebar. The sidebar changes to show all the possible fields for the table.
    • Select the columns you want to add and clear the ones you want to remove. If you want to select all of the columns, click Select All. Use the search bar at the top of the sidebar to search for a field. When you click on the search bar, a filter drop-down menu appears so you can list only fields of a specific type (Number, Attribute, or Time).
    • Click OK.

    If the field you select is a time field, you can modify its granularity and indicate whether or not blanks (even time intervals) should be included in the time field values.

  3. Click Apply to apply your changes to the pivot table.

  4. Save the dashboard.

Modifying Pivot Table Metrics and Metric Layout

The metric layout can be rows or columns. It identifies the direction in which you want the metric heading (as rows or as columns). It changes how the data is presented in the table.

To modify the layout of metric used for the table:

  1. Select Configure on the chart drop-down menu. The Configure Pivot Table sidebar appears showing the current settings for the table.

  2. Select the metric layout you want in the Metrics as drop-down menu. Options are Columns or Rows.

  3. Optionally modify the metrics in the pivot table:

    • Click in the Metrics area of the Configure Pivot sidebar. The sidebar changes to show all the possible metrics for the table.
    • Select the metrics you want to add and clear the ones you want to remove. If you want to select all of the columns, click Select All. Use the search bar at the top of the sidebar to search for a field. When you click on the search bar, a filter drop-down menu appears so you can list only fields of a specific type (Number, Attribute, or Time).
    • Click OK.
    You can modify the method by which the metric is aggregated (AVG, MIN, MAX, SUM, or LAST VALUE). See Available Metric Functions.
  4. Click Apply to apply your changes to the pivot table.

  5. Save the dashboard.

Rearranging Pivot Table Fields

You can rearrange the fields in a pivot table. After you save the dashboard or chart, the field rearrangement is retained when you close the dashboard. It is also retained when you share or export the dashboard or chart.

To rearrange the fields in a pivot table:

  1. Select Configure on the chart drop-down menu. The Configure Pivot Table sidebar appears showing a list of columns in the table.

  2. To move a field, click on its name and then drag it up or down in the list, as appropriate. You can drag fields between the Row and Column lists on the sidebar, in addition to rearranging fields within their own lists. You cannot drag fields in or out of the Metrics list to the other lists; you can only rearrange fields within the Metrics list.

  3. Click Apply to apply your changes to the pivot table.

  4. Save the dashboard.

Changing Pivot Table Column Widths

You can enlarge or decrease the size of pivot table columns while it is open for viewing. After you save the dashboard, your customized column widths are saved when you close the table and when you share or export it.

To change the column widths of a pivot table:

  • Click and drag the separator between two columns in the appropriate direction. Save the dashboard to save the column widths.

Sorting Data in a Pivot Table

The data in the table can be sorted . After you save the dashboard or chart, the sort settings are retained when you close it. The sort settings are also retained when you export and share the table.

To sort the rows in a table by the data in a single column:

  • To sort the data in ascending order, click the up arrow in the heading.
  • To sort the data in descending order, click the down arrow in the heading.

Remember to save the dashboard if you want to retain the sort order.

Controlling the Horizontal Scroll Bar

If your pivot table displays a lot of data horizontally, you may need to unfreeze the horizontal scroll bar. By default, this scroll bar is frozen. In addition, you may want to control whether the Totals column appearing in the rightmost column of the table is frozen on the page (not affected by horizontal scrolling) or unfrozen (only visible when you have scrolled all way to the right).

To control the horizontal scroll bar and the Totals column placement:

  1. Select Chart Style on the chart drop-down menu. The Chart Style sidebar appears showing the current settings for the table.

  2. By default the horizontal scroll bar is frozen. To unfreeze the scroll bar, slide the Rows toggle under Settings to the left (off). The horizontal scroll bar will appear and be useable.

  3. By default, the Totals column is frozen on the pivot table (it appears regardless of scrolling actions). To unfreeze the Totals column (so you must scroll all the way to the right to see it), slide the Totals toggle under Settings to the left (off). The Totals column will no longer be visible unless you scroll the table all the way to the right.

  4. Save the dashboard.