Create a multilevel pivot table report

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

Before you begin

Role required: itil, 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 Reports > Create New
  2. Fill in the fields, as appropriate.
    Table 1. Multilevel pivot table fields
    Field Description
    Data Specify the table or report source containing the data set that you want to include in the report. From the first choice list, select Table or Report source. Then select the specific table or predefined data set from the second choice list.
    Type Multilevel Pivot
    Style your chart Click the gear icon (Cogwheel) after the Type field to see the following style options:
    • Display Zero: Select this check box to display the number 0 when the value of a cell is 0. Clear this check box to display an empty cell when the value of the cell is 0. Applicable when Aggregation is Count or Count Distinct.
    • Drilldown view: Select a view that determines how detailed records are shown when a specific part of the chart is clicked. This option is available for charts with drill-down capabilities. If you select a view that bears no relation to the table that the report is based on, the default view is used.
    Columns Select one or more fields to use as chart columns. The chart displays data broken down by a combination of row and column values. You can select 3 columns maximum.
    Note: It is not possible to group or stack reports by the Tags field.
    Rows Select one or more fields to use as chart rows. The chart displays data broken down by a combination of row and column values. You can select 5 rows maximum.
    Note: It is not possible to group or stack reports by the Tags field.
    Aggregation Select a computational method for aggregating report data. The default is Count, which displays the number of records selected.

    If you select Count Distinct, only unique records are counted. For example, if you want to generate a report with a distinct number of users who have one or more of the roles in a given list of roles, these users would be counted twice unless you use count distinct.

    If you select Average, Sum, or Count Distinct, this displays an additional list of fields from the selected Table. From this list, select a field to aggregate by. For example, if you select a duration field, such as the Business duration field on the Incident table, the aggregated data is expressed in days, hours, and minutes. If you select an integer field, such as the Priority field, the data is expressed as a number. If a value in a column being aggregated has a comma, the value will be separated by the comma, and the aggregation will not be performed accurately.

    Note: For duration values, the unit of measurement displayed in the aggregation axis cannot be customized.
    No. groups Select the maximum number of individual values that can be represented as columns. By default, Pivot charts display up to 12 of the largest values from the selected data. Remaining values are grouped into an Other category. If you select Show all, all values up to a limit of 50 bars are displayed. The rest of the results are stacked on the Other column. If you select Remove Other, the Other column is hidden.
    Show Other Select this check box to display the Other column. This check box is not available when Show all or Remove Other are selected from the No. groups list.
    Add Filter Condition Create conditions for filtering data to include in the report. For example, to include only records with priorities of 2- High and 1 - Critical, select [Priority] [less than] [3 - Moderate].
    Add "OR" Clause Select a second condition that must be met if the first condition is invalid. For example, select [Assignment Group] [is] [Database], to include records that are assigned to the Database group, if the first condition is false.
  3. Click Save. The report is generated.

What to do next

Add reports to homepages or dashboards, publish to the internet, schedule email distribution of PDFs or URLs of published reports, and share reports with others. For more information, see Distribute reports.