7 Unique Features of Pivot Table

A pivot table summarizes business data and displays the result in a cross-table format. It allows the creation of multidimensional views for analyzing and satisfying the needs of business users. Pivot tables are widely used by analysts in financial domains to organize and analyze large data sets. In this article, we will discuss seven unique features of pivot table using Bold BI.

The seven unique features

A pivot table provides basic functionalities like sorting, filtering, and grouping, but the following seven features distinguish the pivot table as an efficient widget for advanced scenarios:

• Totaling values.
• Hierarchical grouping by rows and columns.
• Persisting node states on dynamic updates.
• Displaying no data items.
• Conditionally formatting values with color and text styles.
• Linking with relevant page URLs.
• Interactive sorting by value columns.

In this blog we will briefly discuss each of these using a pivot grid widget in a dashboard created using Bold BI.

Totaling values

Totals are the aggregated result of row or column values in the pivot table. Grand totals for rows are displayed at the bottom of the pivot table below all rows. Grand totals for columns are displayed at the right of the pivot table at the end of all columns. Similarly, subtotals for rows are displayed at the bottom of each row group and subtotals for columns are displayed at the right of each column group. During edit and update operations in value cells, these totals will be refreshed automatically. These rows and columns are enabled by default, but can be disabled through Grand Totals Settings in the Properties window in the Bold BI dashboard designer.

Pivot Table with Grand Total Row

Pivot Table with Grand Total Column

Pivot Table with Subtotal Row

Pivot Table with Subtotal Column

Hierarchical grouping by rows and columns

Pivot table data can be grouped to better analyze it as a subset of data. Data grouping in pivot tables can be handled by one or more rows, columns, or both. With grouping, each row or column value acts as a group with a corresponding aggregated value displayed in a value cell. Adding more than one row or column will organize each of its values as a subgroup of the parent group with a corresponding aggregated value displayed in a value cell. In this way, multiple groups under each row or column value are arranged as nested levels, forming a hierarchy.

With the pivot grid widget in Bold BI, all groups can be set expanded by default on initial rendering in the dashboard through the Expand Nodes by Default option in Basic Settings in the Properties window. In addition, the hierarchical grouping by rows and columns is achievable through data configuration. These groups can be expanded and collapsed dynamically.

Pivot Table with Row and Column Groupings

Persisting node states on dynamic updates

When dealing with large data sets that have many unique categories in a column that must be grouped, you may need to focus on specific categories and would like to expand only specific nodes and perform dynamic operations like sorting and filtering on them. Usually, the expanded state of nodes will not be maintained when such dynamic operations are handled in a pivot table. But with the pivot grid widget in Bold BI, this is possible through enabling Save Nodes State in Basic Settings in the Properties window.

Pivot Table with Sort and Filter Applied,
Retaining Expanded State of Shipped Date 2018 Value Node

Displaying no data items

By default, the pivot table shows a row or column item only if it has data in that particular row and column combination. With the pivot grid widget in Bold BI, row and column items without data can be displayed by enabling Show No Data Item for corresponding fields in the Edit Field Settings dialog in the Properties window.

Pivot Table with No-Data Item Hidden

Pivot Table with No-Data Item Shown

Conditionally formatting values with color and text styles

Conditional formatting is best suited for real-time use cases where a user needs to be alerted when a threshold value is reached. The alert will be in the form of customized font style, text representation, font color, and fill color based on the condition satisfied.

In the pivot grid widget, you can apply conditional formatting column-wise using the Add Conditions option in the Color Settings section in the Properties window.

Pivot Table with Conditional Formatting Applied to Value Cells

Linking with relevant page URLs

Linking is an important part of interactive data visualization for analysis. With this functionality, a row or column header cell or value cell in a pivot grid can be linked to relevant content hosted at a URL. Similarly, can be linked to hosted at a published dashboard URL and a general URL with or without parameters.

You can enable linking in the Bold BI pivot grid widget through the Link settings in the Properties window.

Pivot Table with URL Linking Enabled in Ship State Column

Interactive sorting by value columns

Sorting value columns can be handled the same as in flat grids by clicking the value column header. This option can be disabled in pivot grids through the Allow Value Sorting option under Basic Settings in the Properties window.

Pivot Table Sorted by Value Column

Conclusion

We hope this article helped you to get a clearer idea about the unique features of pivot tables supported in Bold BI dashboards. If you have any questions on this blog, please feel free to post them in the following comment section. Get started with Bold BI by signing up for a free 15-day trial and create more interactive business intelligence dashboards. You can also contact us by submitting your questions through the Bold BI website or, if you already have an account, you can log in to submit your support question.

Originally published at https://www.boldbi.com on July 2, 2019.