Pivot Table Layout

After creating a pivot table and selecting the fields for analysis, you may wish to enhance the report’s layout and formatting to improve readability and facilitate detailed data review.

Change Pivot Table Layout

The different layouts of a pivot table makes it more flexible and convenient to analyze its data. Spread WinForms supports the following pivot table layouts:

• Compact form

• Outline form

• Tabular form

In addition to these, you can also choose to insert blank rows, set the position of subtotals, show all items in the pivot table layouts.

Refer to the following example code to set the layout of pivot table and additional options.

C#

// Set pivot table layout.
pvFields["Category"].LayoutForm = LayoutFormType.Tabular;
pvFields["Category"].LayoutBlankLine = true;

pvFields["Country"].LayoutForm = LayoutFormType.Outline;
pvFields["Country"].LayoutCompactRow = false;

// Set subtotal location.
pvFields["Country"].LayoutSubtotalLocation = SubtotalLocationType.Bottom;
pvFields["Country"].ShowAllItems = true;

VB

' Set pivot table layout.
pvFields("Category").LayoutForm = LayoutFormType.Tabular
pvFields("Category").LayoutBlankLine = True

pvFields("Country").LayoutForm = LayoutFormType.Outline
pvFields("Country").LayoutCompactRow = False

' Set subtotal location.
pvFields("Country").LayoutSubtotalLocation = SubtotalLocationType.Bottom
pvFields("Country").ShowAllItems = True

Change Row Axis Layout

The display of pivot table can be changed to any desired layout using the LayoutRowType enumeration. The following options are provided by this enumeration:

• CompactRow (default layout)

• OutlineRow

• TabularRow

Refer to the following example code to set the row axis layout of the pivot table to TabularRow.

C#

pvTable.RowAxisLayout = LayoutRowType.Tabular;

VB

pvTable.RowAxisLayout = LayoutRowType.Tabular

Manage Grand Total Visibility Settings

The Grand total in pivot table helps in analyzing the total sum of the data in the pivot table. You can display or hide the grand total for the row or column field by setting the visibility of ColumnGrand and RowGrand properties of the IPivotTable interface. These properties take boolean values and are set to true by default. For example, if you want to display the grand total only for rows, then set the RowGrand property to true and ColumnGrand to false.

Refer to the following example code to manage the visibility settings of the grand total field.

C#

// Set the PivotTable report to show grand totals for columns & rows.
pvTable.ColumnGrand = false;
pvTable.RowGrand = true;

VB

' Set the PivotTable report to show grand totals for columns & rows.
pvTable.ColumnGrand = False
pvTable.RowGrand = True

Drill Indicators

Drill indicators visually show which fields in a pivot table can be expanded or collapsed to reveal more details. You can control their display on screen with the IPivotTable.ShowDrillIndicators property, and choose whether they appear in printed pivot tables using the IPivotTable.PrintDrillIndicators property.

Refer to the following example code to enable drill indicators in the pivot table.

C#

// Enable drill indicators.
pvTable.ShowDrillIndicators= true;

// Show drill indicators when printing.
pvTable.PrintDrillIndicators = true;

VB

' Enable drill indicators.
pvTable.ShowDrillIndicators = True

' Show drill indicators when printing.
pvTable.PrintDrillIndicators = true

Repeat Labels

You can use IPivotField.RepeatLabels property to display the label for each item in a pivot table field on every row, instead of only showing the label for the first item in each group. This makes reading and exporting pivot table data easier, especially when the data is flattened or used in reports.

Refer to the following example code to enable repeat labels for a pivot table field.

C#

var field = pvFields["Category"];
field.RepeatLabels = true;

VB

Dim field = pvFields("Category")
field.RepeatLabels = True

Merge Labels

Merge labels provide a way to visually combine adjacent cells with identical values in the pivot table, making grouped data more readable and the report layout cleaner. When enabled, consecutive labels with the same value are merged into a single cell.

You can use the IPivotTable.MergeLabels property to enable or disable the merging of labels.

Refer to the following example code to enable label merging in the pivot table.

C#

pvTable.MergeLabels=true;

VB

pvTable.MergeLabels=true

Defer Layout Update

Defer layout update allows you to temporarily suspend automatic updates to the pivot table layout while making multiple changes. This can improve performance by applying all changes at once, rather than refreshing after each modification.

You can set IPivotTable.DeferLayoutUpdate property to true before making changes, call Update() method to apply them, and set IPivotTable.DeferLayoutUpdate back to false to resume normal updates.

Refer to the following example code to defer layout updates and then apply them.

C#

// Suspend automatic updates.
pvTable.DeferLayoutUpdate = true;

// Perform your update operations here.

// Apply all changes at once.
pvTable.Update();

// Resume automatic updates.
pvTable.DeferLayoutUpdate = false;

VB

' Suspend automatic updates.
pvTable.DeferLayoutUpdate = True

' Perform your update operations here.

' Apply all changes at once.
pvTable.Update()

' Resume automatic updates.
pvTable.DeferLayoutUpdate = False