Add grand totals to the top and bottom of pivot tables—ICRW

Columns in a report organize and make sense of the data. You can add formulas and functions to Interactive Custom Report Writer (ICRW) report columns to enhance data analysis. For large reports, it can be helpful to show grand totals both at the top and bottom of a report, which you can do with a pivot table.

This topic demonstrates how to add a grand total at the top and bottom of a pivot table.

Subscription Interactive Custom Report Writer

Platform Services

Regional availability

All regions
User type Interactive Custom Report Writer: Business, Project Manager
Permissions

Interactive Custom Report Writer: Run, List, View, Add, Edit, Delete

Platform Services: Run, List, View, Add, Edit, Delete

Open a pivot table view of a report

You can view any ICRW report as a table or pivot table. Changes to the report are shown immediately regardless of the view. These views allow you to explore the data in more detail by linking to more information.

You can choose to view a report as a table or a pivot table.

  1. Open an ICRW report and go to the Refine tab.
  2. Go to the Views pane in the lower left corner of the window.
  3. Select The New view icon brings up a new view of a table or pivot table. (Views) and choose the desired view.

Create grand totals at the top and bottom of a pivot table

This section shows you how to add a grand total at the top and bottom of a pivot table view of a report.

Add a Grand Total at the top of the report

  1. Open the pivot table view, then from the Refine tab, go to Rows in the Layout section.
  2. Select the Sigma icon (Sum/Totals), and choose Before from the dropdown menu.
  3. While still on the Sigma icon dropdown menu, select Format Labels.

    The Rows section of the pivot table with the Sigma icon menu showing the Before and Format Labels options selected.

  4. In the Edit Format dialog, enter a Caption, choose a Border Position, then select a Border Style and Border Color.
  5. Select OK.
    The following example uses Grand Total as a Caption, a Custom border Position, Border Style, and Border Color.

    Edit Format dialog with Grand Total in the Caption field and selected Border options. Border Position set for All, Border Style set to Thick, and a purple Border Color.

  6. Continue to Add a Grand Total at the bottom of the report.

Add a Grand Total at the bottom of the report

  1. Select the Calculation column icon to add a new calculation column.
  2. Enter a Folder title, Column title, then in the Calculation pane enter: 'Grand Total'
  3. Select OK and move the new column to the far left in the Rows section.
  4. Select the Sigma icon for the new column and choose After from the dropdown menu.
  5. While still on the Sigma icon dropdown menu, select Format Labels.
  6. In the Edit Format dialog, enter a Caption, choose a Border Position, then select a Border Style and Border Color.
  7. Select the Gear icon for the new column, choose Hidden, then select Done in the upper right corner of the window.
  8. Select Done in the upper right corner of the window and Save the report.

The following report shows the results for the previous procedures.

Year-over-Year Variance table report that shows 4 months of data with a Year To Date total. There is a Grand Total row at the top of the report table and a Grand Total row at the bottom, both with purple borders to enhance visibility.