Search results

Configuring Pivot Grid

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

pivot-grid

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

pivot-grid_dragDrop

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

configurationPanel_propertyIcon

Or click Properties icon from widget’s top right corner

pivot-grid_propertyIcon

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

pivot-grid_properties

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,

pivot-grid_assignData

Adding data to Value(s) field

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

pivot-grid_bindValue

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

pivot-grid_valueRender

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

pivot-grid_valueMultiRender

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.

pivot-grid_valueSettingIcon

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

pivot-grid_valueSettingFilter

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

pivot-grid_valueSettingFormat

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)

pivot-grid_singleValuesColumn

You can change the Settings.

Settings

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

Sort Settings

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

Configuring widget filters

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.

pivot-grid_rowData

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)

pivot-grid_multipleColumn

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)

pivot-grid_multipleRow

Removing Data from Field

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

pivot-grid_valueRemove

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.

Property window

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.

Pivot grid property Name

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.

Basic Settings

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.

pivot-grid_prop-basic-sorting

Expand Nodes by Default

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

pivot-grid_prop-basic-nodes

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.

Show Values in Row

By enabling this property, you can visualize the value fields in the row section instead of the column. This option is not enabled by default.

pivot-grid_prop-basic-nodes

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.

pivot-grid_prop-groupBar

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.

pivot-grid_prop-group-show

Allow Sorting

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

pivot-grid_prop-group-sorting

Allow Filtering

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

pivot-grid_prop-group-filtering

Grand Total Settings

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

pivot-grid_prop-grandTotal

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.

pivot-grid_prop-grand-row

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.

pivot-grid_prop-grand-column

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.

pivot-grid_prop-grand-subRow

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.

pivot-grid_prop-grand-subColumn

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.

pivot-grid_prop-edit-noDataItem

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.

Formatting properties

Group panel background color

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

Group panel background color

Header background color

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

Header background color

Header foreground color

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

Header foreground color

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.

Columns list

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.

Advanced settings applied widget

Pivot Grid as Heatmap

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

pivot grid As 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. Enable Advanced Settings

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

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

Pivot Grid Datetime Hierarchy Fields

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

Pivot Grid Datetime hierarchy

How to do pivot grid datetime hierarchy

  1. configure the date fields as follows, Assign Data Section

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

  3. Do the same for remaining fields. Assign Data Section

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

pivot-grid_prop-link

Filter

Filter allows user to apply filtering based on data field.

Filter

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.

Container appearance properties

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. Enable advance settings

  2. This will open the Conditional Formatting dialog. Conditional formatting window

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

  4. Now, the widget visualization will be updated based on the conditions. Widget after applying  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.

Rule mode

Condition name

You can give a meaningful name to the applied conditions.

Condition type

Choose the condition for measure field from the highlighted conditions.

Condition type

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.

Add condition

Delete condition

Click the delete button to remove the existing condition.

Delete condition

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