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. For example, if a service catalog item has a variable called Storage, you can create a report that has a column or row for the values in this variable, such as 128 GB, 500 GB, and 1 TB.

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. Give the report a name that reflects the information being grouped.
  3. Select a report source that has variables associated with it. By default this is the Requested Item table (sc_req_item) or any table that dot walks to it. There are two kinds of report sources:
    OptionDescription
    Data source A table with filters applied to provide a single source of information for all users.
    Table The raw data from a table with no filters applied.
    For list reports with variables, the report source is usually the service catalog table.
  4. From the Type dropdown list, select Multilevel Pivot in the Pivot section.
  5. Click the cog wheel icon next to the Type field to configure the appearance of the report. See Multi-level pivot report style options.
    Figure 1. Configure appearance icon
  6. Click Select Groups next to the word Columns to open the Multilevel Pivot Columns slush bucket.
    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.

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

  7. 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 expand icon (expand icon) to choose an item.
      Rows slushbucket
    3. Select a Catalog item from the pop-up window.
      The variables associated with the item appear in the Columns window.
      Rows with selected item and variables
    4. Move the selected variables to the Selected column and click Close.
  8. Click Select Groups next to the word Rows to open the Multilevel Pivot Rows slush bucket. Select rows the same way you select columns.
    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.
  9. Fill in the following fields and click Next.
    Table 1. Configure tab
    Field Description
    Aggregation Mathematical calculation to perform on the data. The default is Count, which displays the number of records selected.

    To display only unique records, select Count Distinct. For example, if you want a report on the distinct number of users who have one or more of the roles in a given list of roles. Users with more than one role would be counted twice unless you use Count Distinct.

    Select Average, Sum, or Count Distinct, to display a list of fields from the selected Table. 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.
    Max number of groups Maximum number of groups to display in the report. Groups with highest values are included first. Any excluded groups are combined into the single group Other.

    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 Check box to include the Other group in the report. The Other group contains data for all groups that exceed the number specified in Max number of groups.
  10. (Optional) To limit the information displayed in the report, click Add Filter Condition or Add "OR" Clause and select conditions to filter the report data.
    For more details on how conditions are constructed, see Condition builder.
  11. Click Save.
    The report is generated.

What to do next

  • To enter a description of the report, click the Report info icon (Report info icon).
  • To share the report, click Share to open the Sharing menu. On this menu, you can set visibility and schedules, add the report to a dashboard, export the report to PDF, and publish the report to the web. See Share a report for more information.