Create a pivot table visualization

Create a pivot table visualization to summarize large data sets by breaking them down by multiple dimensions in a single table. Cells show each row and column value combination and can also show subtotals.

Before you begin

Role required: Anyone with access to data can create a visualization of that data on a dashboard that they own. Users with the itil, report_user, admin, or viz_creator role can create a visualization in the Visualization Designer. If you create a visualization in the Visualization Designer, it is saved to the Library. For more information on access, see Report_view access control and Platform Analytics roles.

About this task

For information about the use of a Pivot visualization in a dashboard, see the Developer Site. This site gives information about Pivot data visualization components in the UI Builder, and some configuration options may differ from the Visualization Designer.

Procedure

1. Navigate to All > Platform Analytics > Library > Data Visualizations.
2. Select New.
3. Select the Pivot Table () visualization type.
4. Configure the Header and border. Header and border options are the same for all data sources.

   Header and border fields	Description
   Show header	The visualization header, including title and icons.
   Show header separator	Option to display a line separating the header from the rest of the component.
   Chart title	Title of the visualization.
   Description	A short overview about the visualization that the end user sees.
   Wrap title	Option to wrap long titles onto a second line. If false, displays an ellipsis to truncate long titles.
   Show border	Option to display a line around the component.
   Bare	Option to remove padding around data visualization to provide more compact positioning on the page. Only available when Show border is turned off.

5. Choose a data source.
   For general descriptions of the data sources, see Data sources for data visualizations.

   • Table (available in the base system). When you select a table, you can filter it by custom or preconfigured conditions. Custom conditions can include questions or Service Catalog variables.

     Configured report sources appear in the Predefined conditions list. For more information, see Report sources.

     To help you create a custom filter, there is a preview list of records that would be included in the visualization. You can change which fields are shown as columns and the width of columns in the list actions.
     
     
   • Indicator (available in the base system). When you select an indicator as a data source, you can select a breakdown and one or several elements for filtering the results. If an element filter is set on the breakdown source, that element filter is available in the elements field. However, data visualizations support element filters only for automated indicators with the Count, Minimize, or Maximize aggregations. For more information, see Element filters.
   • Usage Insights (available with the User Experience PAR Integration application, to users with a required role). Choose one of up to three KPIs included with this application, depending on the visualization type. For more information, see User Experience Analytics data sources for visualizations.
   Note:

   You can choose multiple data sources for this visualization. However, all data sources must be of the same type: table, indicator, or Usage Insights. For more information, see Multiple data sources.
6. Select the options for your data source.
   • If your visualization represents table data, go to Table data options for pivot table visualizations.
   • If your visualization represents indicator data, go to Indicator data options for pivot table visualizations.
   • If your visualization represents Usage Insights data, go to Usage Insights data options for pivot table visualizations.
7. Under Presentation, provide display and color information.

   Field	Description
   Display settings
   Freeze row header	Allows users to keep the header visible while scrolling to another area in the visualization.
   Freeze row footer	Allows users to keep the footer visible while scrolling to another area in the visualization.
   Freeze first column	Keeps the first column visible while scrolling.
   Show alternative row colors	Toggles a background color for alternative rows to make reading the visualization easier.
   Show alternative column colors	Toggles a background color for alternative columns to make reading the visualization easier.
   Display row lines	Displays border lines to separate rows.
   Display column lines	Displays border lines to separate columns.
   Table size settings
   Truncate first column [truncateFirstColumn]	When true, limits the width of the first column. First column contents beyond this width are truncated. First column headers are not truncated. Specify this width limit in First Column max width, in px.
   First Column max width, in px	The maximum width of the first column, beyond which its contents are truncated. This maximum width cannot be less than the width of the first column header, which is never truncated. Values lower than the header width are ignored. Only takes effect when Truncate first column is active. Default value is 200px.
   Wrap column headers	Breaks header text into lines matching content width, with a minimum limit of 100px. Does not apply to first column. You cannot both wrap and truncate column headers.
   Truncate column headers	Truncates header text into lines matching content width, with a minimum limit of 100px. Does not apply to first column. You cannot both wrap and truncate column headers.
   Colors
   Set color type	Default: A color or set of colors that come from the UX Theme that is applied to the instance. For more information, see Working with themes in Next Experience. Single color/Colors per metric: Select a single color in the Set value color field. You can select the color from a set of icons, from a list of colors including their icons, or by searching for the color name or hexadecimal value. The colors are taken from the Color Definition [sys_report_color] table. If the visualization has more than one metric, you see the Colors per metric option instead. This option lets you select a single color for each metric. Alternatively, you can add a rule to define a dynamic color. For more information, see Create coloring rules for data visualizations. Note: Color icons and colors per metric are included in the latest Data Visualizations application from the ServiceNow® Store.

8. Under Chart interaction, set what if anything happens when a viewer clicks a chart or a chart segment on the visualization.

   Field	Description
   Allow chart interaction	Enable an event to occur when a user clicks in a chart or one of its segments.
   Action	Choose the event that occurs when a user clicks in a chart or one of its segments. Choices depend on the visualization type and data source. Applies only when Allow chart interaction is on. For more information, see Chart interactions in a data visualization. Go to data view opens the records view in a Core UI list or KPI Details relevant to the associated segment or visualization. Records do not open in Workspace embedded lists. Not supported for Usage Insights data sources. Go to URL opens the specified URL, which can be on the instance or external. You have the option of specifying a page name to appear in the tooltip, for those visualizations with tooltips. Drill down to chart (Visualization Designer only) Opens a different data visualization that is filtered by the selected data. You can add a drill-down visualization for each metric on the parent visualization. Note: The last level of drill down in the Platform Analytics experience is always a Core UI list. Records do not open in Workspace embedded lists. Drill down to chart supports only table data sources. It requires the latest Platform Analytics application from the ServiceNow® Store.

9. Select Save.
   All > Platform Analytics > Library > Data Visualizations to return to the Data Visualization list.

Pivot table with multiple data sources

Consider a case where you want to present the following information, split up for each assignment group:

• Number of assigned open incidents
• The average age of the assigned open incidents
• The average number of times an incident was reassigned

You can present this information in a single pivot table. Each set of information is going to come from a different data source. You have to find either three tables or three Performance Analytics indicators with this information. You cannot mix and match different types of data sources! Fortunately, you find three appropriate indicators already defined in your instance. You select them as your data sources for a pivot table.

You want to see these indicator scores broken down by assignment group. Therefore, you select Assignment Group under Group By. Your three data sources are going to show as three columns, so you skip the Column field and select Assignment Group as a row.

Now you are done. Save the data visualization and use it in a dashboard.

What to do next

• Add a visualization to a dashboard from the Visualization Designer
• Share a data visualization in the Visualization Designer
• Bookmark a visualization in the Visualization Designer