A Pivot table is a statistics tool that summarizes and reorganizes selected columns and rows of data to obtain a desired report.
- Firstly you need to choose the Input table and give a name to the Report.
- You then need to go to the Build Panel. It has two parts Table Fields and Row Limit.
- In the Table Fields section, you will need to select one or more columns with their sort order in the Rows pane which you want to showcase as rows in your Pivot Report.
- Now go to the Columns pane and select one or more columns with their sort order which you want to showcase as Column headers in your Pivot Report.
- Then in the Values pane, you will need to select one or more Measure columns (aggregation applied) such as Sum of Sales, Count of Orders, etc as per your need to showcase the values in the Pivot Report.
- After this you need to click on Run Button and your Pivot Report will be ready.
Rows Limit: You can Limit the Output rows by going to the Row Limit section in the Build panel then selecting the number of rows you want to showcase in the Pivot Report and clicking on Run Button.
You can change the aggregation type of the selected column in the Values pane by clicking on the dropdown button alongside the selected column and then choosing among different aggregation types like Sum, Count, Avg, Min, & Max, etc, and then clicking on Run button to reflect the changes.
For eg: You created a report "Region vs Product Category wise Total Sales" report using the Region column in the Rows pane, Product Category in Columns Pane, and Sum(Sales) column in the Values pane but now you want to see the "Region vs Product Category wise Avg Sales" then you can simply change the aggregation type for Sales column and click on the Run button to update the report.
You can add column labels in the Pivot report as well.
Here are the steps to add Labels for selected Columns:
- You need to click on the drop-down button alongside the selected columns.
- You then need to select Add Label option for columns selected in the Table Fields section.
- A new Add Lable window will open up there you can specify the labels according to your requirement.
- After specifying the labels click on Add button (Add Label).
- Then Click on the Run button to reflect the changes in the report.
The Format panel is used to perform various kinds of formatting on the Pivot report to make it more efficient.
It consists of the below-mentioned Formatting techniques:
- Freeze Column: This allows you to freeze single or multiple columns according to your requirement.
- Total: This allows you to enable column total for all the measure columns in the report. You can enable the Show Total Row toggle and then click on Apply.
- Conditional Formatting: This allows you to make patterns and trends in the Report more prominent. It involves formatting certain values in the column or the entire row based on some conditions/rules.
- Column Headers: This allows you to format Column headers in the Report as per your requirement. It involves changing the Column header's font size, font color, font style, alignment and background color, etc.
- Field Formatting: This allows you to format field values in any Column as per your requirement. It involves changing the field value's font size, font color, font style, alignment and background color, Decimal Places,Prefix, and Suffix, etc.