Pivot grid allows you to display summarized data in cross tabular format i.e., in rows and columns.
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.
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
Drag and drop the pivot grid into canvas and resize it to your required size.
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.
Name, Subtitle and Description for pivot-grid can be given through these properties.
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.
If we click the column header, the value field get sorted as ascending or descending when the property Allow Value Sorting
is enabled.
Nodes are expanded by default, if this property’s checkbox is checked.
This property restore the expanded and collapsed state of node even after reloading the dashboard.
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.
You can resize the pivot grid by dragging vertical lines of pivot grid control. This option is not enabled by default.
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.
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.
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
allows user to enable and disable sorting property for group bar.
Allow Filtering
allows user to enable and disable Filtering property for group bar.
This section contain the show and hide properties for grand and sub totals of rows and columns.
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.
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.
This property allows user to show or hide row’s sub total. The below image is an example for disabled sub total for row.
This property allows user to show or hide column’s sub total. The below image is an example for disabled sub total for column.
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.
This section explains how to format the pivot grid cells.
This allows you to select the color for the group panel’s background. The default value is #e9e9e9
.
This allows you to select the color for the header row’s background. The default value is #eeeeee
.
This allows you to select the color for the header row’s foreground. The default value is #333333
.
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.
Select value as display type to get the column values represented as it is.
Select Advance setting to configure conditions and apply color to the cells based on that. Click here for more details.
Using the conditional formatting, you can customize the pivot grid as a Heatmap.
In the properties
panel, under the Formatting
section select the field name that configured in value, then click the Advanced Setting
radio button.
Click the Customize
button, now the Advanced Setting
will open.
Customize the colors as per your needs and click the save button.
It can be achieved by applying date formatting for configured fields.
configure the date fields as follows,
Click the settings menu icon and select the date format based on the hierarchy.
Do the same for remaining fields.
To configure the linking to URL or dashboard with the widget through its settings. For more details, refer to Linking.
Filter
allows user to apply filtering based on data field.
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.
This allows you to define this pivot grid widget to ignore responding to the filter actions applied on other widgets in dashboard.
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.
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.
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.
In the properties
pane, under the Formatting
section, click the Advanced Setting
radio button.
This will open the Conditional Formatting
dialog.
Select the mode
and enter the conditions as required and click the Save
button to apply.
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.
You can customize the background color of the pivot grid cells using the gradient based conditional formatting. This is the default mode.
The configured value fields. Based on field option cannot be changed in the conditional formatting dialog.
The summary type of the configured value field.
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.
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.
You can give a meaningful name to the applied conditions.
Choose the condition for measure field from the highlighted conditions.
You can mention the condition value.
Allows you to define the pivot grid cell foreground for this condition.
Allows you to define the pivot grid cell background for this condition.
Allows you to choose the title font style. (Bold, Italic, Underline, and Superscript
).
Click Add Condition
to specify add new condition.
Click the delete button to remove the existing condition.
Blog Post – https://www.boldbi.com/blog/7-unique-features-of-pivot-table