1. SuiteAnalytics
  2. Workbook
  3. SuiteAnalytics Workbook Tutorial
  4. Pivot Your Dataset Query Results

Pivot Your Dataset Query Results

If you have not already done so, complete Step Four: Create a Workbook Based on Your Dataset.

The following steps show you how to pivot your dataset query results to create a pivot table.

To pivot your source data:

  1. On the Pivot tab, drag fields from the Fields list to the Rows, Columns, or Measures tabs in the Layout panel. Alternatively, drag the fields from the Fields list directly to the Pivot Table Viewer.

    Pivot tab showing how to create calculated measures.

    For the purposes of this tutorial, add the item and status fields to the pivot table Rows, add the amount (net) and quantity fields to the pivot table 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.

  2. (Optional) To created a calculated measure, click Create Calculated Measure in the Layout Panel. You can also click the Field Menu icon 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 Calculated measures icon. For more information, see Calculated Measures.

  3. 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 Field menu icon next to the field you want to format in the Layout panel.

    2. Select a summary type from the popup window.

      Summary Type dropdown list.

    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 in Datasets and Workbooks.

      Important:

      If you have fields with values in multiple currencies, you must convert the values to a single currency to perform arithmetic operations or other types of numeric manipulation.

    4. (Optional) Click Format... to customize the numeric values for a field.

      For more information about numeric formatting options, see Customizing Numeric Values.

    For the purposes of this tutorial, select the following summary types. No numeric formatting is required:

    • Amount (Net) (Sum)

    • Quantity (Sum)

  4. Add totals and grand totals to the pivot table.

    1. Click the Totaling icon 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.

      Totaling window

    3. Click OK.

    For the purposes of this tutorial, no totals are required.

  5. Click the Refresh icon Refresh report icon to generate the pivot table.

    Completed pivot

  6. (Optional) 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 underlying dataset or other workbook visualizations.

    1. Click the Field Menu icon 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, or if you click the Field menu icon from the Fields List or the Layout Panel, the following options are available:

      • 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.

    2. The results in the table are updated automatically.

      For more information, see Workbook Visualization Filters.

      For the purposes of this tutorial, no pivot table filters are required.

  7. (Optional) Apply conditional formatting to the pivot table measures to highlight your results.

    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 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.

    For the purposes of this tutorial, no conditional formatting is required.

  8. (Optional) Click the Export icon to save a CSV file of your pivot table.

Continue to Step Six: Connect a Second Dataset to Your Workbook.

Related Topics

General Notices