Configuring Pivot Grid

Pivot grid allows you to display summarized data in cross tabular format i.e., in rows and columns.

How to configure table data to pivot grid?

NOTE: Before adding the Pivot grid widget to the design layout, make sure to create the data source. You can refer to this section to learn how to create a new data source.

Pivot grid need a minimum of 1 value , 1 row and 1 column element to showcase proper pivot table details. The measure or expression field that you would like to analyze can be dropped into Value(s) field. The dimension that you would like to categorize the measure based on row and column can be dropped in Row(s) and Column(s) field.

The following steps explain how to configure the data to a Pivot grid:

1. Drag and drop the pivot grid widget into a canvas and resize it to your required size.

2. To open the Property pane, click the Properties icon in the Configuration panel.

3. In the property pane, switch to the ASSIGN DATA tab from the PROPERTIES, to assign data to the pivot grid data fields.

4. The ASSIGN DATA tab will be opened with available measures and dimensions from the connected data source. Also it contains Value(s), Row(s), and Column(s) field as mentioned earlier.

Adding data to Value(s) field

You can add more than one Measures into Value(s) field by drag and drop the required measure.

Now the pivot grid will be rendered like this for single data

Pivot grid will be rendered like this, if more than one data bind to Value(s)

If more than one data is added to Value(s) field and then grid column is separated based on the number of data attached to Value(s) field, as shown in above image.

Click the Settings option to change the name by using the Rename option and the required summary type from the available summary types shown in Settings.

Filtering data

You can filter the data to be displayed in pivot grid by using filter. For more details, refer filter.

Formatting data

You can format the data to be displayed in the pivot grid by using format option. For more details, refer measure format

You can add more than one column from Dimensions field into Value(s) field.

Adding Column(s)

Adding single data to Value(s) and Column(s)

You can change the Settings.

Sorting data

You can Sort the dimension data using Sort option under Settings menu list. To apply sorting for the data, refer Sort.

Filtering data

You can apply filters by selecting filter in settings. For more details, refer filter.

Similarly, you can add the Measures and Expressions into the column(s) field.

Adding Row(s)

You can drag and drop the Measure or Dimension into the Row(s) field.

You can apply filter and sort option for the rows field, if required.

Adding Multiple Column(s)

If more than one data is added to Column(s) field and then the arrow icon or node is prepended to each cell of the column header. A new tree view list will be opened when the arrow icon is clicked.

Adding Multiple data to Column(s) with single Value(s) and Row(s)

Adding Multiple Row(s)

If more than one data is added to Row(s) field and then the arrow icon or node is prepended to each cell of the row header. A new tree view list will be opened when the arrow icon is clicked.

Adding Multiple data to Row(s) with single Value(s) and Column(s)

Hidden column section

You can drag and drop the elements to Hidden Columns if required. Based on the hidden column elements, the values will be shown.

Removing Data from Field

To remove the added data from fields click the highlighted cross icon.

Important

Data Limit

• For now, the pivot grid has a limitation of data up to 10K records.

How to format pivot grid?

Pivot grid can be formatted for better illustration of the view, through the settings available in Properties tab.

To format pivot grid follow the below steps

1. Drag and drop the pivot grid into canvas and resize it to your required size.

2. Click on Settings icon and Configure the data from ASSIGN DATA tab as mentioned in previous section, then switch back to PROPERTIES tab.

The property window will be opened.

You can see the list of properties available for the widget with default value.

General Settings

Name, Subtitle and Description for pivot-grid can be given through these properties.

Name

This allows you to set title for this pivot-grid widget.

Subtitle

This allows you to set subtitle for this pivot-grid widget.

Description

This allows you to set brief explanation about this pivot-grid widget.

Basic Settings

Basic Settings contains the general property for pivot grid, which includes value sorting, collapsing and expanding nodes state, saving the state of nodes and wrapping the text.

Allow Value Sorting

If we click the column header, the value field get sorted as ascending or descending when the property Allow Value Sorting is enabled.

Expand Nodes by Default

Nodes are expanded by default, if this property’s checkbox is checked.

Save Nodes State

This property restore the expanded and collapsed state of node even after reloading the dashboard.

Allow Text Wrap

Allow Text wrap allows you to wrap the long words onto the next line.

Allow Column Resize

You can shrink or enlarge the columns by dragging the vertical lines.

Fit To Content

The columns in the pivot grid can be made to auto-size based on the length of the content of the column.

Note: The Virtualization dynamically loads the given data when you scroll down the pivot grid. If any one of these properties (Fit to content, allow text wrap, and allow column resize) is enabled then the virtualization disables. Also, we do not recommend these properties (Fit to content, allow text wrap, and allow column resize) when using a large number of records.

Show Values in Rows

It shows the values in rows.

Content Settings

Content settings allows you to adjust the row height, column width, and header column width for better visibility.

Row Height

You can customize the height of the grid rows. The value can range between 25 and 120.

Column Width

You can customize the column width by increasing or decreasing the values from 110 to 300.

Header Column Width

You can customize the column width for header by increasing or decreasing the values from 250 to 500.

Group Bar Settings

This section contains all the property related to group bar (i.e., row and column header), which includes show or hide, sorting and filtering properties for group bar.

Show Group Bar

If this property’s checkbox is unchecked, and then the group bar (i.e., row and column header) is disabled as shown in below image. By checking this property the group bar will be displayed.

Allow Sorting

Allow Sorting allows user to enable and disable sorting property for group bar.

Allow Filtering

Allow Filtering allows user to enable and disable Filtering property for group bar.

Grand Total Settings

This section contain the show and hide properties for grand and sub totals of rows and columns.

Show Row Grand Totals

This property allows user to show or hide row’s grand total. The row’s grand total in the below image will be disabled if this property is unchecked.

Show Column Grand Totals

This property allows user to show or hide column’s grand total. The column’s grand total in the below image will be disabled if this property is unchecked.

Show Row Sub Totals

This property allows user to show or hide row’s sub total. The below image is an example for disabled sub total for row.

Show Column Sub Totals

This property allows user to show or hide column’s sub total. The below image is an example for disabled sub total for column.

Edit Field Settings

You can edit the field settings.

Show No Data Item

Show No Data Item displays the empty data cell of selected Name from Edit Field Settings. For example, if the ShipCity’s Show No Data Item is checked and then it displays all the empty data points as empty cell similar to the below image.

In the above image other than Campinas, all other cities have empty data and so it displayed as empty cells.

Formatting

This section explains how to format the pivot grid cells.

Group panel background color

This allows you to select the color for the group panel’s background. The default value is #e9e9e9.

Header background color

This allows you to select the color for the header row’s background. The default value is #eeeeee.

Header foreground color

This allows you to select the color for the header row’s foreground. The default value is #333333.

Column representation

You can define the column value represented as simple text or condition-based formatted text. This lists out the columns added to the pivot grid widget.

Value

Select value as display type to get the column values represented as it is.

Advance setting

Select Advance setting to configure conditions and apply color to the cells based on that. Click here for more details.

Pivot Grid as Heatmap

Using the conditional formatting, you can customize the pivot grid as a Heatmap.

How to configure pivot grid as a heatmap

1. In the properties panel, under the Formatting section select the field name that configured in value, then click the Advanced Setting radio button.

2. Click the Customize button, now the Advanced Setting will open.

3. Customize the colors as per your needs and click the save button.

Pivot Grid Datetime Hierarchy Fields

It can be achieved by applying date formatting for configured fields.

How to do pivot grid datetime hierarchy

1. configure the date fields as follows,

2. Click the settings menu icon and select the date format based on the hierarchy.

3. Do the same for remaining fields.

Link

To configure the linking to URL or dashboard with the widget through its settings. For more details, refer to Linking.

Font Settings

Font settings section allows you to enable the auto font size.

Auto Font Size

On enabling auto font size, the size of the font adjusted automatically based on the screen resolution.

Font Size

You can enable this property by disabling Auto Font Size, which allows you to change the font size.

Filter

Filter allows you to apply the filtering based on data field.

Act as Master Widget

This allows you to define this pivot grid widget as a master widget such that its filter action can be made to listen by other widgets in the dashboard.

Ignore Filter Actions

This allows you to define this pivot grid widget to ignore responding to the filter actions applied on other widgets in dashboard.

Hierarchical Filter

Through this option, you can enable/disable hierarchical top N filtering. While applying Top N filter with multiple dimension columns, the data returned can be customized based on whether the filtering need to be done as flat or based on the hierarchy of dimension columns added.

When Hierarchical Filter option is enabled, the Top N will be applied for each individual column separately based on the number set for each column.

How to apply conditional formatting in pivot grid

In case, if you have the requirement to highlight any data based on some conditions, you might be required to enable the advanced formatting option.

How to enable advanced formatting

1. In the properties pane, under the Formatting section, click the Advanced Setting radio button.

2. This will open the Conditional Formatting dialog.

3. Select the mode and enter the conditions as required and click the Save button to apply.

4. Now, the widget visualization will be updated based on the conditions.

Refer to the following sections for the detailed steps on using the Gradient and Rule modes.

Gradient mode

You can customize the background color of the pivot grid cells using the gradient based conditional formatting. This is the default mode.

Based on field

The configured value fields. Based on field option cannot be changed in the conditional formatting dialog.

Summary type

The summary type of the configured value field.

Low, mid, and high values

You can define your range by entering the low, mid, and high values.

NOTE: It is optional, if no value is entered then the ranges will be auto calculated based on the minimum and maximum values from the data source.

Rule based condition mode

This allows you to customize the cell colors and value font style of the pivot grid cells based on one or more numerical / text conditions.

Condition name

You can give a meaningful name to the applied conditions.

Condition type

Choose the condition for measure field from the highlighted conditions.

Value

You can mention the condition value.

Foreground

Allows you to define the pivot grid cell foreground for this condition.

Background

Allows you to define the pivot grid cell background for this condition.

Font style

Allows you to choose the title font style. (Bold, Italic, Underline, and Superscript).

Add condition

Click Add Condition to specify add new condition.

Delete condition

Click the delete button to remove the existing condition.

Container appearance

Title alignment

This allows you to handle the alignment of widget title to either left, center, or right.

Title color

This allows you to apply the text color to the widget title.

Title Auto Font Size

On enabling Auto Font Size, the font size of the title will be adjusted automatically if the resolution of the screen varies.

Font Size

This allows you to apply the specified font size to the widget title, if the Title Auto Font Size is disabled. The value can range between 10 and 44.

Subtitle Auto Font Size

On enabling Auto Font Size, the font size of the subtitle will be adjusted automatically if the resolution of the screen varies.

Font Size

This allows you to apply the specified size of the font to the widget title if the Subtitle Auto Font Size is disabled. The value can range between 10 and 32.

Show Border

This allows you to toggle the visibility of border surrounding the widget.

Corner Radius

This allows you to apply the specified radius to the widget corners if the Show Border is enabled. The value can range between 0 and 10.

Transparency

You can change the transparency from 0 to 1.

Container actions

Show header

This allows you to enable or disable the widget title of the pivot grid.

Allow maximize view

This allows you to enable or disable the maximized mode of the pivot grid. The visibility of the maximize icon in the widget header will be defined based on the setting in the viewer.

Allow CSV export

This allows you to enable or disable the CSV export option for the pivot grid. Enabling this allows you to export the summarized data of the widget view to CSV format in the viewer.

Allow Excel export

This allows you to enable or disable the Excel export option for the pivot grid. Enabling this allows you to export the summarized data of the widget view to (.xlsx or .xls) format in the viewer.

Allow image export

This allows you to enable or disable the image export option for the pivot grid. Enabling this allows you to export the view of the widget to image format (.jpg), (.png), or (.bmp) in the viewer.

Allow PDF export

This allows you to enable or disable the PDF export option for the pivot grid. Enabling this allows you to export the view of the widget to pdf format in the viewer.

Enable comments

This allows you to enable or disable comment for the dashboard widget. For more details, refer to this link.

Allow Viewing of underlying data

This allows you to visualize the raw data associated with a widget at runtime.

Pin widget

This allows you to pin the widget.

Related links

Blog Post – https://www.boldbi.com/blog/7-unique-features-of-pivot-table