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 card 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.

Follow the steps to configure data to Pivot grid

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

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

Or click Properties icon from widget’s top right corner

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

The 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 required summary type from the available summary types shown in Settings.

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

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.

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

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

Similarly you can add the Measures and Expressions into columns 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)

Removing Data from Field

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

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.

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

By default, long texts get trimmed in pivot grid. But if the property Allow Text Wrap is enabled, then it displays a wrapped text instead of a trimmed one.

Allow Column Resize

You can resize the pivot grid by dragging vertical lines of pivot grid control. This option is not enabled by default.

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. This option is not enabled by default.

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

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.

Filter

Filter allows user to apply 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.

Container Appearance

This section contain the property for pivot grid container’s layout.

This allows you to customize the widget container appearance, widget title’s properties and provides options to enable/ disable widget exporting options. For more detailed information, refer to container appearance properties.

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.

Related links

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