Thank you for your feedback.
Form temporarily unavailable. Please try again or contact docfeedback@servicenow.com to submit your comments.
Versions
  • London
  • Kingston
  • Jakarta
  • Istanbul
  • Helsinki
  • Geneva
  • Store
Close

Create a multilevel pivot report in the Report Builder

Create a multilevel pivot report in the Report Builder

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

Create a multilevel pivot table report in the Report Builder

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.

About this task

This task refers to using the Report Builder (Classic UI) in the Kingston release under UI15 and UI16. If you are using the Report Designer, see Create a multilevel pivot report in the Report Designer.
Report Designer UI Report Builder UI
Report designer UI example Report builder UI example
Note: To export multilevel pivot tables to PDF, you must activate the Webkit HTML to PDF plugin.

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.
    Note: If you select a data source used by existing reports, a notification will display prompting you to view them.
    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.
    Show Other Select this check box to display the Other column.
    Add Filter Condition Click the filter icon The filter icon to create conditions for filtering and ordering data. For example, you might create a condition that states Priority + less than + 3 – Moderate to have the report include only records with priorities of 2 – High and 1 – Critical.
    Note: Keywords is a special field used for text searches across all fields. It’s use in a filter or condition, in combination with other conditions, may return inconsistent results.
    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.

Create a multilevel pivot report in the Report Builder 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.

About this task

This task refers to using the Report Builder (Classic UI) in the Kingston release under UI15 and UI16. If you are using the Report Designer, see Create a multilevel pivot report in the Report Designer.

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 Multilevel pivot report style options – Report Builder.
    Figure 1. Configure appearance icon
    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 2. Configure tab
    Field Description
    Aggregation Mathematical calculation to perform on the data. The default is Count, which shows the number of records selected.

    To show 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 show 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.

    If you choose Sum or Average, select Show related fields to aggregate on dot-walked fields. See Dot-walking.

    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.

    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.
    Note: Keywords is a special field used for text searches across all fields. It’s use in a filter or condition, in combination with other conditions, may return inconsistent results.
  11. 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.

Multilevel pivot report style options – Report Builder

Change the look of your multi-level pivot report.

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 3. Chart style options
Field Description
General
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 List view to show when a user selects a segment of a report for which no drilldown report type is specified. This view is also used when the user reaches the lowest drilldown level of a report. See Configure the list layout . If you specify a Report drilldown, Drilldown view is ignored.
Note: All users are able to view report visualizations, such as pie charts and column reports. However, the last level of a report 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.

For more information, see Define a report drilldown in the Report Designer.

Edit coloring rules Click this hyperlink to configure how cells and cell text with numerical values are colored in the report. You can create rules to define which colors are used based on operators and values. For example, you can specify that any value greater than 5 displays in red. See Create a coloring rule for a multilevel pivot table.
Decimal precision Number of decimal places to show. You can show from zero to four decimal places. Default value: 2. To change the default value, create the system property glide.chart.decimal.precision and specify the value.
Note: Percentage labels do not change accordingly with the decimal precision specified.
Decimal precision - Bar reportDecimal precision - Donut report
Title
Show chart title When the chart title is shown for the report.
  • Never: Never show the chart title.
  • Report only: Shows the chart title on reports.
  • Always: Shows the chart title on reports, dashboards, and homepages.
Chart title The chart title has a maximum length of 40 characters. If no title is entered, the report name is used for the title. This field appears when Report only or Always is selected from the Show chart title list.
Size of the chart title Size of the chart title in pixels. This field appears when Report only or Always is selected from the Show chart title list.
Chart title color Color of the chart title. This field appears when Report only or Always is selected from the Show chart title list.
Custom chart title position Check box to specify X and Y coordinates for the position of the chart title. This field appears when Report only or Always is selected from the Show chart title list.
Title horizontal alignment How the chart title is aligned horizontally. This field is available when Custom chart title position is cleared.
Title vertical alignment How the chart title is aligned vertically. This field appears when Custom chart title position is cleared.
Chart title X position Number of pixels to adjust the chart title position right or left. By default the title appears at the center top of the chart. To move the chart title to the right, enter a positive value. To move the title to the left, enter a negative value.

This field appears only when Custom chart title position is selected.

Chart title Y position Number of pixels to adjust the chart title position up or down. By default the title appears at the center top of the chart. To move up the chart title, enter a positive value. To move the chart title down, enter a negative value.

This field appears only when Custom chart title position is selected.

Create coloring rules for multilevel pivot reports

Configure rules for how numerical values are displayed in a multilevel pivot table report, to easily highlight the more important values. The color rule is applied to the content of cells in pivot reports.

Before you begin

This task is part of configuring the style options of a multilevel pivot report.

Procedure

  1. On the Style tab of the report designer, click Edit coloring rules.
  2. In the Multilevel Pivot Rules dialog box, click New rule.
  3. In the New record dialog box, select an Operator.
    Options are: greater than, greater than or is, lower than, lower than or is, is, and between.
  4. Specify a value. If you selected the operator between, specify two values. The color rule is applied to the aggregated values.
  5. Select a font color and a background color.
  6. (Optional) Specify a Rule order. Rules are evaluated from lowest value to highest. For example, you have one rule applies the color blue to the value 7, and a second rule that applies the color red to values between 1 and 10. If you want the 7 to appear blue, the Rule order value for the first rule should be higher so that the second rule does not override it.
    If you do not specify a rule order, coloring rules are applied in the order in which they were created.
  7. Click Submit to save the rule and create a new rule, or click OK to save the rule and return to the report designer.