Pivot

A Pivot table is a statistics tool that summarizes and reorganizes selected columns and rows of data to obtain a desired report.

How To Create a Pivot 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.

Changing Aggregation Type in the Values pane in the Table Fields section:

You can change the aggregation type of the selected column in the Values pane by:

• Clicking on the dropdown button alongside the selected column

• Then choose among different aggregation types like Sum, Count, Avg, Min, & Max, etc, and

• Then click on the 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.

Add Labels for columns:

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.

Formatting Pivot Reports:

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 & Sub-totals

This allows you to enable column total for your pivot table. You can enable the Show grand total toggle and then click on Apply.

• Total Formatting & Positioning

  • Row Grand Total: Can be positioned at the top or bottom of the row section.

  • Column Grand Total: Can be positioned to the left or right of the column section.

  • Total and Subtotal Formatting: Independently adjust font style, size, color, and background color for total and subtotal rows and columns.

• Subtotal Control

  • Toggle subtotals on or off for individual rows and columns.

  • At least one checkbox must be selected to ensure subtotal visibility.

• Show total for Values

  • Choose which values should include totals using individual checkboxes.

  • Exclude Values: Opt out of displaying totals for specific value columns.

Conditional formatting

• Conditional formatting allows you to visually highlight data in your pivot table by applying specific formatting based on values or conditions.

• This helps in quickly identifying trends, outliers, or important information.

• Sprinkle offers two primary methods for conditional formatting: Gradient Scales and Rules.

Gradient Scales

Gradient Scales

With Gradient Scales, you can color-code cells based on a continuous range of values. The color intensity varies proportionally to the data value.

Steps:

1. Select the Column: In the conditional formatting section, choose the column you want to format.

2. Choose Gradient Scale: In the conditional formatting modal, select the "Gradient Scale" option.

3. Define Color Scale:

   • Predefined Scales: Select from a variety of built-in color scales.

   • Custom Scale: Create your own color scale by defining the start and end colors. Optionally, you can add a middle color for more granular control.

4. Format Empty Cells: If desired, check the "Format empty cells as zero" option to apply the same formatting to empty cells as if they contained zero.

5. Apply: Click "Apply" to save your changes.

Rules

Rules

Rules provide more granular control over formatting by applying specific styles based on defined conditions.

Steps:

1. Select the Column: In the conditional formatting section, choose the column you want to format.

2. Choose Rules: In the conditional formatting modal, select the "Rules" option.

3. Define Rule:

   • Operator: Select an operator (e.g., greater than, less than, between) to define the condition.

   • Threshold Value: Specify the value or range of values that trigger the formatting.

4. Choose Formatting: Select predefined formatting options or customize the background color, border color, and text color.

5. Apply: Click "Apply" to save your changes.

Here additionally you can also choose to highlight the entire row if the cell satisfies the condition.

Gridlines

Here you can configure the appearance of the lines separating the cells. You can customize the vertical & horizontal gridlines separately.

For each,

• You can choose to show or hide them using the toggle their toggle switch.

• You can also configure the line width and the color of the line.

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.

Global Formatting

Global Formatting

This applies to all cells in a table or pivot table at once unless overridden by specific column formatting.

To access global formatting:

1. Navigate to the field formatting section.

2. Click on the "Global Settings" tab.

3. Adjust the desired settings:

   • Font: Size, style, and color.

   • Text Alignment: For dimensions.

   • Number Alignment: For measures and numeric aggregates.

   • Display Units: For measures and numeric aggregates.

   • Decimal Places: For measures and numeric aggregates.

   • Separator Style: For measures and numeric aggregates.

   • Text Wrap: For both dimensions and measures.

4. Click "Reset" to restore default settings.

Specific column formatting

Specific Column Formatting

You can override global settings for individual columns to achieve specific formatting.

To apply unique formatting to a specific column:

1. Navigate to the field formatting section.

2. Click on the "Specific Columns" tab.

3. Click the "+ Select column" button to add a column.

4. Choose the desired column from the pop-up list.

5. Adjust the desired settings for the selected column. These settings will override the global settings.

6. By default, the fields will be disabled. You can simply click on the fields to make them editable and override the global setting for that specific field.

7. To remove specific formatting for a column, you can either reset the changes or hover over the column and click the delete icon.