Workbook Pivot Tables

The Pivot tab lets you pivot your dataset results to look at different data subsets. In each table, you can add multiple fields for each dimension, create measures and calculated measures, or set up table-specific filters to customize your results. You can also change how your table looks by formatting numbers, renaming rows and columns, adding totals and grand totals, or using sorting and conditional formatting. You can add your pivot tables to the Analytics portlet on any NetSuite dashboard, and export them as a CSV file. If your table is linked to other datasets, you can compare data at different levels based on the fields you pick. For more information, see Dataset Linking in SuiteAnalytics Workbook.

Note:

The following formatting won't be exported to CSV: percentages, currency symbols, totals, and layout options like conditional formatting and compact mode.

You can only make a pivot table with fields included in the underlying datasets. On the Pivot tab, you'll see them in the Dataset Panel on the left. In pivot tables with linked datasets, a link icon next to a field shows it's a common key. For more information about common keys, see Common Keys.

The Pivot Table Viewer on the right shows your table based on the fields you set as rows, columns, and measures in the Layout Panel. Every time you update the layout, click the Refresh icon Refresh report icon to update the table. You'll also need to refresh the table if the datasets change.

To create a pivot table:

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

Important:
By default, the pivot table uses the most recently connected dataset in the workbook. To switch datasets, open the dataset selector in the Dataset Panel and pick a different one. Or, if the dataset you want isn't in this workbook yet, click Connect Dataset to select from your account or create a new one.
On the Pivot tab, drag fields from the Dataset Panel to the Rows, Columns, or Measures tabs in the Layout panel. Or, drag fields straight into 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'll be prompted to pick how to show the field values. Depending on where you add the field and which display type you pick, you can also add more subtotals to the pivot table for each hierarchy level. For more information, see Hierarchical Fields.
(Optional) If you want to build your pivot table with two datasets, click the Dataset Menu icon and select Create Dataset Link. Depending on the common keys you pick 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 formatting options for any date or numeric 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, choose where you want totals or grand totals for each field. If you have multiple fields to total in the rows or columns, check Set Individually to pick where each field's totals will show up 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 filter. The options you'll see depend on whether the field is set as a column, row, or measure. If you click the Field Menu icon from the Fields List or Layout Panel, that can also change what options you get:
  - 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 with linked datasets, be sure to apply filters to the matching field in each dataset. If you don't, you might see cells in your results that you meant to filter out. For more information, see Create Visualizations Based on Linked Datasets.
Apply conditional formatting to your pivot table measures.

Important:
If the measure field you pick has values in multiple currencies, you'll need to convert or consolidate them before adding conditional formatting. For more information, see Currency in Datasets and Workbooks.
1. Click the Field Menu icon next to the measure you want to highlight in the Layout Panel and go to Conditional Formatting.
2. Click Manage Conditional Formatting, then pick the operators, values, and colors or icons for your rule. You can click the add icon to make more rules, using different colors or icons for the same measure or column. For more information, see Conditional Formatting.
  
  Note:
  If you're formatting percentage values, use decimal format for your 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 every measure you want to highlight.
(Optional) Click the Export icon to save your pivot table as a CSV file.

After you make your pivot table, you can rename it or delete the tab. Hover over the Tab menu icon and select the Rename Tab or Delete Tab , or double-click the tab name to rename it.

To create a pivot table:

Related Topics