How can I create Pivot Table Visual in an IntelliFront BI Dashboard?
This article will help you to create a Pivot Table Visual in an IntelliFront BI Dashboard. A Pivot Table is ideal for reorganizing and summarizing selected amounts of data from a large database.
Dashboard - Pivot Table Visual
When creating a dashboard item it is important to know what you want to display. We recommend having some questions in mind of what you want the dashboard to show before you start creating it.
The Dataset used in this dashboard is a sample of a Black Friday sales. The Dataset contains information about Age, Occupation, Gender, Marital Status, Purchased Items, Purchase Price, City of residence etc.
I would like to find out the following from the Dashboard:
Who spends more on Black Friday, Men or Women?
Does Marital Status have an effect on spending habits?
Below is an example of a Pivot Table created based on the two questions above:
To create this visual, begin by selecting Pivot.
Binding Menu
Use the Click here to bring up the Binding Menu. The Binding Menu allows you to create and modify the data selected for the dashboard.
Values
Select the value for the information you are going to show.
For the example above I am going to show the Purchase Number as a Sum.
You may need to use a calculated field. To create a calculated field use the F+ button. You will need to use SQL Queries for this.
Please seek assistance from your IT department as we do not provide SQL Query support.
Binding
Rows and Columns: From the menu on the right select the data for the rows and the data for the columns
Summary Type: Select which type of summary variable you would like.
Options
Caption: This is the heading for the selected value. It is best to caption the visual with a variation of the question asked at the beginning of creating the dashboard.
For Example; Male vs Female spend on Back Friday.
Show Values: Toggle on an off values.
Show Totals: Toggle on and off totals for rows / columns.
Show Grand Totals: Toggle on and off Grand totals for rows and columns.
Conditional Formatting
In this section, allows you to highlight specific cells or entire rows by setting a rule.
Click on (+) button to set a rule.
Calculated by: Select the column you want to calculate the rule.
Apply to: Select the column you would like to apply this new rule.
Condition Type: Select the condition type in order to highlight the column.
Select the Style of the condition.
Depending on the selected format condition, the menu used to create a format rule for Pivot contains different settings.
Miscellaneous: In this section of the format rule menu contains additional settings depending on the dashboard item type.
Calculations
In this section, it will automatically calculate the value by selecting one of the options.
Format
In this section, you have the option to select the format type and unit to the value.
Columns
The columns in Pivot Visual Contains data items whose values are used to label columns.
Binding
Select the value for the information you are going to show.
For the example above I am going to show the Is Married?.
You may need to use a calculated field. To create a calculated field use the F+ button. You will need to use SQL Queries for this.
Please seek assistance from your IT department as we do not provide SQL Query support.
Options
Caption: This is the heading for the column selected. It is best to caption the visual with a variation of the question asked at the beginning of creating the dashboard.
Data Shaping
In this section, you can change the sort order of the value.
Sort Order: You can select Ascending or Descending sort order.
Sort By: Select the value you want to sort this data.
Group Interval: You can set group intervals by alphabetical or none.
Conditional Formatting
In this section, allows you to highlight specific cells or entire rows by setting a rule.
For more information, go to Values in this article.
Top N
In this section, you can customize the display number of values that correspond to the highest or lowest values of a particular measure.
Enable this option by clicking ON.
Mode: Specifies whether top or bottom values should be displayed.
Count: The number of Values you want to display.
Measure: The measure selected which the top or bottom values will be determined.
Show "Others" Value: If enabled, all values that are not shown in the top/bottom ones will be displayed in the "Others" value.
Rows
In the Pivot Visual, the rows contains data items whose values are used to label rows.
Binding
Select the value for the information you are going to show.
For the example above I am going to show the Gender.
You may need to use a calculated field. To create a calculated field use the F+ button. You will need to use SQL Queries for this.
Please seek assistance from your IT department as we do not provide SQL Query support.
Options
Caption: This is the heading for the Row selected. It is best to caption the visual with a variation of the question asked at the beginning of creating the dashboard.
Data Shaping
In this section, you can change the sort order of the value.
Sort Order: You can select Ascending or Descending sort order.
Sort By: Select the value you want to sort this data.
Group Interval: You can set group intervals by alphabetical or none.
Conditional Formatting
In this section, allows you to highlight specific cells or entire rows by setting a rule.
For more information, go to Values in this article.
Top N
In this section, you can customize the display number of values that correspond to the highest or lowest values of a particular measure.
Enable this option by clicking ON.
Mode: Specifies whether top or bottom values should be displayed.
Count: The number of Values you want to display.
Measure: The measure selected which the top or bottom values will be determined.
Show "Others" Value: If enabled, all values that are not shown in the top/bottom ones will be displayed in the "Others" value.
Hidden Dimensions/Hidden Measures
This section is used to utilize a field without displaying in the dashboard item. Adding the Hidden dimension or measure, you can sort any values in the data by that specific dimension/measure.
This section is to sort or filter the visual dashboard in order to get more detailed analysis.
You can use Hidden Dimensions/ Hidden Measures with the following sections:
Data/Filtering
Data Shaping
Common
Top N
In this example, we want to sort the individuals by their occupation.
After the data is added in the Hidden Measure, select the columns and/or rows you want to filter.
In this case, I want to sort Gender by Occupation. Select Rows and select Gender and click Data Shaping. In Data Shaping, go to the Sort By section and select Occupation.
Interactivity Menu
In this section, you can enable interaction between the Pivot and other dashboard items, you can use the interactivity features.
If you don't want the pivot visual be affected from others dashboards items, click ON in the Ignore Master filters option.
Options Menu
In this section, contains specific options and settings related to the dashboard item.
Common
Show Caption: You can enable or disable the visual caption.
Caption: This is the heading for the visual selected. It is best to caption the visual with a variation of the question asked at the beginning of creating the dashboard.
Layout
This topic describes how to control the Pivot Visual layout, the visibility of totals and grand totals, etc.
Layout: You can specify the layout used to arrange values corresponding to individual groups.
Compact: Displays values from different Row dimensions in a single column.
Tabular: Displays values from different Row dimensions in separate columns.
Column Grand Totals/Totals: You can control the visibility of totals and grand totals for the entire Pivot.
Column/ Row Total Positions: You can change the position of totals/grand totals for the Pivot dashboard item.
Values Position: You can control the position of headers used to arrange summary values corresponding to different measures.
Initial State
In this section, you have the option to automatically expand your columns and rows in your Pivot Visual.
Conditional Formatting
All conditional formatting in the visual will be displayed here.
In order to add a conditional formatting click on the (+) button.
Calculated by: Select the column you want to calculate the rule.
Apply to: Select the column you would like to apply this new rule.
Condition Type: Select the condition type in order to highlight the column.
Select the Style of the condition.
Depending on the selected format condition, the menu used to create a format rule for Pivot contains different settings.
Miscellaneous: In this section of the format rule menu contains additional settings depending on the visual type
Convert To Menu
In this section, you have the option to convert or duplicate the current dashboard item.
Remove Button
Click on the remove button to delete the current dashboard item.
