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.
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.
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.
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.
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.
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.
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.
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.
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, please leave them in the comments section below. You can also contact us by submitting your queries through the Bold BI website, or if you already have a Bold BI account, log in to submit your support query. Bold BI now comes with a 15-day free trial with no credit card information required. We welcome you to start a free trial and experience Bold BI for yourself.