Zoomdata Version

Pivot Tables

Pivot tables are supported by all data sources except Cloudera Search. They are supported by Solr v5.2 and higher. If needed, you can add custom metrics to your metrics.

Pivot tables are tables of summarized statistics. They are useful for finding unique values for a field.

Configuring the Default Settings

To configure pivot table settings:

  1. On the Charts tab (for your selected data source), under the Standard tab, select Pivot Table.

  2. Configure the settings as described in the following table:

    Setting Description
    Metrics Select the default metrics and corresponding functions, if required, that should be used.  By default Volume is selected.
    Metric Direction Select Rows if you want the metrics listed in rows. Select Columns if you want the metrics listed in columns.

    Row Attributes

    Select the default row attributes on which the table is based.
    Column Attributes Select the column attributes
    Rows per Page Specify the number of rows per page.
  3. Click Save.

Creating a Pivot Table Chart

To view your data in a pivot table:

  • Create a chart, as described in Creating Dashboards, but select Pivot Table for the chart type. A pivot table chart is created using the default settings for the selected data source.

You can now modify the pivot table and use it to explore and interact with the data.

Modifying the Pivot Table

When you first create a pivot table, the default settings for the data source used to create the table are used. Using the Configure Pivot Table sidebar, you can customize the default fields selected, arrange the metric layout (column or row) and metric, and sort the data by the column headings.

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

Modifying the 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 deselect 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 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 the 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 deselect 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 should be included in the time field values.
  3. Click Apply to apply your changes to the pivot table.

  4. Save the dashboard.

Modifying the Metric Layout and Metrics

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 deselect 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).
  4. Click Apply to apply your changes to the pivot table.

  5. Save the dashboard.

Rearranging the Table Fields

You can rearrange the fields in a pivot table. After you save the dashboard or chart, the field rearrangement is retained when 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.

Sorting Data in the 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.

Was this topic helpful?