Workbook Pivot Tables

The Pivot tab is where you pivot your dataset query results to analyze different subsets of data. Within each table you can define multiple fields for each dimension, add measures and create calculated measures, or create filters unique to the table to customize your results. You can also change the appearance of your table by formatting the numeric values that are presented, renaming your table rows and columns, adding totals and grand totals, or applying sorting options and conditional formatting. Additionally, you can add your pivot tables to the Analytics portlet on any NetSuite dashboard and export your pivot table to a CSV file. If your table is connected to linked datasets, you can also compare data at different levels of aggregation based on the fields you select. For more information, see Dataset Linking in SuiteAnalytics Workbook.

Note:

The following formatting options are not exported to the CSV file: percentages, currency symbols, totals, and layout settings such as conditional formatting and compact mode.

You can only create a pivot table based on the fields that are included in the underlying datasets. On the Pivot tab, these fields appear in the Dataset Panel on the left. In pivot tables connected to linked datasets, a link icon next to a field denotes that it is a common key in the link definition. For more information about common keys, see Common Keys.

The Pivot Table Viewer on the right displays your completed table based on the fields you define as rows, columns, and measures in the Layout Panel. Each time you change or update your layout, you must click the Refresh icon Refresh report icon to update the table. You must also refresh your table if changes are made to the underlying datasets.

To create a pivot table:

Click the add icon from anywhere within the workbook and select Pivot.

Important:
By default, the pivot table is based on the most recently connected dataset in the workbook. To change the dataset that the table is based on, expand the dataset selector in the Dataset Panel and select a new dataset. Alternatively, if the dataset you want is not already used in the workbook you are in, click Connect Dataset to select from any of the datasets you have access to in your account, or create a new dataset.
On the Pivot tab, drag fields from the Dataset Panel to the Rows, Columns, or Measures tabs in the Layout panel. Alternatively, drag fields from the Dataset Panel directly to the Pivot Table Viewer.
(Optional) To created a calculated measure, click Create Calculated Measure in the Layout Panel. You can also click the Field Menu icon next to a measure in the Pivot Table Viewer and select Create Calculated Measure. Calculated measures are displayed with a calculator icon . For more information, see Calculated Measures.

Note:
If you add hierarchical fields to the table, you are prompted to select a display type for the field values. Depending on where you add the field and the display type you select, you can also add additional subtotals to the pivot table for each level in the hierarchy. For more information, see Hierarchical Fields.
(Optional) If you want to build your pivot table using two datasets, click the Dataset Menu icon and select Create Dataset Link. Depending on the common keys you select and where you place them in the table, your results will be aggregated. For more information, see Create Visualizations Based on Linked Datasets.
Select the summary type and format options for any date or numerical fields you add to the pivot table.
1. Click the Field Menu icon next to the field you want to format in the Layout panel.
2. Select a summary type from the popup window.
  
  The summary options vary depending on the type of field you select.
3. (Optional) Select Currency... to view the currency consolidation or conversion options for any fields with values in multiple currencies.
  
  For more information, see Currency Conversion from the User Interface.
4. (Optional) Click Format... to customize the numeric values for a field.
  
  For more information about numeric formatting options, see Customizing Numeric Values.
Add totals and grand totals to the pivot table.
1. Click the Totaling icon .
2. In the Totaling window, select where you want the totals or grand totals for each applicable field to appear. If there are multiple fields that can be totalled in the rows or columns, check the Set Individually box to select where the totals for each field will appear on the pivot table.
3. Click OK.
Click the Refresh icon to generate the pivot table.
Filter the data displayed in the pivot table.

Note:
Filter conditions created on the Pivot tab only impact the data displayed in the pivot table. No changes are made to the dataset that the workbook is based on.
1. Click the Field Menu icon next to the field you want to create a filter for. Depending on whether the field has been defined as a column, row, or measure, the following options are available. If you click the Field menu icon from the Fields List or the Layout Panel, it can also impact the available options:
  - Top 10: display only the top 10 rows or columns based on the measures defined for the table.
  - Bottom 10: display only the bottom 10 rows or columns based on the measures defined for the table.
  - Filter [Field Name] by...: enables you to define a custom measure-based filter for the selected row or column.
  - Filter [Field Name]: enables you to define a custom value-based filter based on specific values within the table results.
  - Add as Filter...: enables you to define a custom value-based filter based on specific values within the table results.
  - Filter Date: enables you to define a custom date filter based on date ranges that you choose.
2. The results in the table are updated automatically.
  
  For more information, see Workbook Visualization Filters.
Important:
If you build your pivot table using linked datasets, you must apply filters to the matching field in each dataset. If you do not, your results might include cells that you wanted to filter. For more information, see Create Visualizations Based on Linked Datasets.
Apply conditional formatting to the pivot table measures.

Important:
If the measure field you select has values in multiple currencies, you must convert or consolidate the values before you can apply conditional formatting. For more information, see Currency in Datasets and Workbooks.
1. Click the Field Menu icon next to the measure field you want to highlight in the Layout Panel and point to Conditional Formatting.
2. Click Manage Conditional Formatting, then select the operators, values, and colors or icons for the rule. You can click the add icon to create multiple rules and apply different colors or icons to the same measure or column. For more information, see Conditional Formatting.
  
  Note:
  To apply conditional formatting to percentage values, use decimal format when defining the values for the rule. For example, rather than greater or equal to 20%, the rule should be defined as greater or equal to 0.2.
3. Check Apply to subtotals and grand totals if you want to apply your rules to the measure subtotals and grand totals.
4. Click Apply.
5. Repeat steps A-D for each measure you want to highlight.
(Optional) Click the Export icon to save a CSV file of your pivot table.

After you create your pivot table, you can change the name of your pivot table or delete the pivot table tab. Hover over the Tab menu icon and select the Rename Tab or Delete Tab options. You can also rename your table view by double-clicking the tab name.

To create a pivot table:

Related Topics