Pivot tables

Pivot tables aggregate data from a table into columns and rows, which you define.

You can configure a filter to further refine the data and select the aggregation values. Pivot tables enable you to quickly investigate the source of the summarized data. Non-empty cells display tooltips to indicate how many records the cell represents. Clicking a non-empty cell displays a breakdown of those records.

Figure 1. Pivot table

Create a pivot table

Create a pivot table to aggregate data from a table into columns and rows.

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. Pareto table
    Field Description
    Name Unique and descriptive name for your report
    Description Click the information icon (The information icon) to enter a more detailed description of what the report does and its purpose.
    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

    Select Pivot table.

    Alternatively, click the question mark icon (The question mark icon) to use the report type selector.

    Style your chart Click the gear icon (The gear icon) to view available style options:
    • 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.
    Row Select the field used as the source of the data for the rows in the pivot table.
    Note: It is not possible to group or stack reports by the Tags field.
    Column Select the field used as the source of the data for the columns in the pivot table.
    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.

    Select Count Distinct to display only unique records. For example, 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. Users with more than role would be counted twice unless you use count distinct.

    If you select Average, Sum, or Count Distinct, a list of fields from the selected Table appears. Select a field to aggregate by from this list. For example, if you select a duration field, such as Business duration on the Incident table, the aggregated data is expressed in days, hours, and minutes. If you select an integer field, such as Priority, the data is expressed as a decimal value number.

    Note: For duration values, the unit of measurement displayed in the aggregation axis cannot be customized.
    No. groups

    Select the maximum number of groups such as bars, sections, or columns, that can be displayed in the chart. If the number of values from the selected data exceeds this limit, only groups for the largest values appear. By default, up to 12 of the largest values from the selected data are represented. Remaining values can be grouped on the Other bar.

    If you select Show all, all groups up to a limit of 50 are displayed. The rest of the results are grouped as Other. If you select Remove Other, the Other group is hidden.

    Show Other Select this check box to display the Other group for values that exceed the No. groups limit. This check box is not available when Show all or Remove Other is selected from the Max number of 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. See Distribute reports.

Pivot table style options

Change the look of your box chart.

When you create or edit a report, click the gear icon (The gear icon) after the Type field. Use the options in the Style your chart dialog box to configure the look of your chart. Chart options are automatically saved when you click Close. To see how the chart looks with the saved settings, click Save.

Table 2. Chart style options
Field Description
General
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 has no fields in common to link to the table that the report is based on, the default view is used.
Note: All users can view report visualizations, such as pie charts and column reports. However, the last level of a drilldown is always a list. Platform access control lists determine user access to list information. Users who do not have rights to any part of the list data see the message "Number of rows removed from this list by Security constraints:" followed by the number. See Access control rules.