Multilevel pivot tables

Multilevel pivot tables display aggregate data broken down by multiple dimensions in a single table. They display separate cells for each row and column value combination, as well as a column subtotal for each first-level row. Aggregate information is presented in the top left of the chart.

You can also create multilevel pivot tables with columns and rows containing variables. See Use service catalog variables in a report.

You can expand and collapse the rows in the table to show the chart details, or only the subtotals. The top row of a multilevel pivot report is always visible.

Note:

• On instances with Unified Analytics enabled, and on new Australia instances, both Core UI reports and Platform Analytics experience data visualizations are found in the Platform Analytics library. Navigate to All > Platform Analytics > Library > Data Visualizations. For more information, see Exploring the Data Visualizations library.
• Some row configurations prevent the chart from displaying subtotal information, such as when a string column has the same text value but with different character cases.
• You can only schedule this report type as a PDF.

Parent Topic:Report types

Create a multilevel pivot report

Create a multilevel pivot report to display aggregate data broken down by multiple metrics in a single table.

Before you begin

Role required: itil, report_user, report_group, report_global, report_admin, or admin. To create a meaningful report, you must have the right to access the data you want to report on.

Note: On net new Australia instances, only users with the report_admin role can create reports. To visualization your information in these instances, see Creating data visualizations.

Procedure

1. Navigate to All > Platform Analytics > Library > Data Visualizations and select New.

2. On instances with Unified Analytics enabled, and on new Australia instances, both Core UI reports and Platform Analytics experience data visualizations are found in the Platform Analytics library. Navigate to All > Platform Analytics > Library > Data Visualizations. For more information, see Differences between Core UI and Platform Analytics dashboards.

3. On the Data tab, give the report a name that reflects the information being grouped.

4. Select the applicable source for the report.

1. Select Next.

2. On the Type tab, enter Multi-level pivot table in the filter, select the report type, and click Next.

   The application shows a preliminary version of the report. To view the updated report at any time, select Run.

3. On the Configure tab, fill in the following fields and select Next.

**Note:** Currency fields default to two decimal places. With the **Set Value Formatting** option, you can configure decimal precision for 0-4 decimal places.

1. Select Default expanded to display the report with the rows expanded.

   Otherwise, the report will display with all rows collapsed.

1. Check Display row lines and Display column lines to show the lines that separate the cells in the report.

1. Configure the sort order of the applicable fields in the report (column, row, Group by, Stack by or Trend by depending on the report type).

   Select the filter icon (

1.  In the Sorting Order list, choose the field you want to sort on and then choose **a-z** or **z-a** for alphabetical order or reverse alphabetical order.

    The list contains all possible fields from the report's source and fields on extended, or related, tables. The only effective values, however, are the fields chosen for the current report \(column, row, Group by, Stack by, or Trend by depending on the report type\).

    **Note:** On fields from dot-walked \(extended\) tables, you can only sort on date fields.

2.  Select the sort plus icon \(

3.  Select **Save**.
For fields of the type Choice list, sort order is not determined alphabetically based on Label, it is sorted based on the **Value** of the choice field. For multi-level pivot table and heatmap reports, sort order is determined by the sequence of the choices in the list.

1. To limit the information displayed in the report, select the filter icon (

To learn how to construct conditions, see [Condition builder](../../platform-user-interface/c_ConditionBuilder.md).

**Note:** In aggregated and list reports, language-dependent filter conditions may return zero results on localized instances.

1. On the Style tab, fill in the fields as appropriate to configure the appearance of the report.

2. Select Save to continue editing the visualization, or Save and close to return to the Analytics Overview main screen.

What to do next

• Select the Report info icon (

• Select the sharing icon (

• Search the Community site for more insight or help with multi-level pivot reports.

Create a multilevel pivot report with variable columns and rows

You can create a multilevel pivot report with variables columns and rows based on a data source or table that has variables associated with it. Variables are descriptions of catalog items.

Before you begin

Roles required: itil, report_user, report_group, report_global, report_admin, or admin. To create a meaningful report, you must have the right to access the data you want to report on.

Procedure

1. Navigate to All > Platform Analytics > Library > Data Visualizations and select New.

2. On the Data tab, give the report a name that reflects the information being grouped.

3. Select the applicable source for the report.

1. Select Next.

2. On the Type tab, enter Multi-level pivot table in the filter, select the report type, and click Next.

   The application shows a preliminary version of the report. To view the updated report at any time, select Run.

3. On the Configure tab, click Select columns.

   From the Available list in the Multilevel Pivot Columns window, select columns that you want to use in the report and move them to the Selected list.

   Note: It is not possible to group or stack reports by the Tags field, or by certain field types, such as MEDIUMTEXT.

   Depending on system configuration, you can add fields from tables that extend the table selected as the report data source. For more information, see Report on extended tables.

4. Select variables to use as columns:

   1. Select one or more fields to use as report columns.

      The report visualization displays data broken down by a combination of row and column values. You can select up to three columns including the variables.

   2. Select Variables [+] and click the structure icon (

3.  Select a **Catalog item** from the pop-up window.

    The variables associated with the item appear in the **Columns** window.

4.  Move the selected variables to the **Selected** column and click **OK**. Select the info icon

for descriptions of the selected fields

1. Click Select rows to select one or more fields to use as report rows.

   You select rows similarly to how you select columns. Select the info icon

for descriptions of the selected fields.

The report visualization displays data broken down by a combination of row and column values. You can select up to five rows including the variables.

**Note:** It is not possible to group or stack reports by the **Tags** field, or by certain field types, such as MEDIUMTEXT.

1. On the Configure tab, fill in the following fields and select Next.

**Note:** Currency fields default to two decimal places. With the **Set Value Formatting** option, you can configure decimal precision for 0-4 decimal places.

1. Check Display row lines and Display column lines to show the lines that separate the cells in the report.

1. Configure the sort order of the applicable fields in the report (column, row, Group by, Stack by or Trend by depending on the report type).

   Select the filter icon (

1.  In the Sorting Order list, choose the field you want to sort on and then choose **a-z** or **z-a** for alphabetical order or reverse alphabetical order.

    The list contains all possible fields from the report's source and fields on extended, or related, tables. The only effective values, however, are the fields chosen for the current report \(column, row, Group by, Stack by, or Trend by depending on the report type\).

    **Note:** On fields from dot-walked \(extended\) tables, you can only sort on date fields.

2.  Select the sort plus icon \(

3.  Select **Save**.
For fields of the type Choice list, sort order is not determined alphabetically based on Label, it is sorted based on the **Value** of the choice field. For multi-level pivot table and heatmap reports, sort order is determined by the sequence of the choices in the list.

1. To limit the information displayed in the report, select the filter icon (

To learn how to construct conditions, see [Condition builder](../../platform-user-interface/c_ConditionBuilder.md).

**Note:** In aggregated and list reports, language-dependent filter conditions may return zero results on localized instances.

1. On the Style tab, fill in the fields as appropriate to configure the appearance of the report.

   See Multilevel pivot report style options.

2. Select Save to continue editing the visualization, or Save and close to return to the Analytics Overview main screen.

What to do next

• Select the Report info icon (

• Select the sharing icon (

• Search the Community site for more insight or help with multi-level pivot reports.

Multilevel pivot report style options

Change the look of your multi-level pivot chart.

When you create or edit a report, click the Style tab for options to configure the look of your report. The options are organized under two or more of the following tabs: General, Title, Legend, and Axis. To see how the report looks with the changed settings, click Save.