Introduction

In our previous topic, we have shown how to create a simple PivotTable. In this topic, we will discuss about customizing the appearance of PivotTable by setting their properties.

Setting PivotTable Format Options

 

Setting AutoFormat type

[C#]

 

 

//Setting the PivotTable report is automatically formatted

pivotTable.IsAutoFormat = true;

 

 

//Setting the PivotTable atuoformat type.

pivotTable.AutoFormatType = PivotTableAutoFormatType.Classic;

 

 

[VB]

 

 

'Setting the PivotTable report is automatically formatted

pivotTable.IsAutoFormat = True

 

 

'Setting the PivotTable atuoformat type.

pivotTable.AutoFormatType = PivotTableAutoFormatType.Classic

 

 

[JAVA]

 

 

//Setting the PivotTable report is automatically formatted

pivotTable.setAutoFormat(true);

 

 

//Setting the PivotTable atuoformat type.

pivotTable.setAutoFormatType(PivotTableAutoFormatType.CLASSIC);

 

Setting Format Options

 

[C#]

 

 

//Setting the PivotTable report shows grand totals for rows.       

pivotTable.RowGrand = true;

 

 

//Setting the PivotTable report shows grand totals for columns.       

pivotTable.ColumnGrand = true;

 

 

//Setting the PivotTable report displays a custom string in cells that contain null values.

pivotTable.DisplayNullString = true;

pivotTable.NullString = "null";

 

 

//Setting the PivotTable report's layout

pivotTable.PageFieldOrder = PrintOrderType.DownThenOver;

 

 

[VB]

 

 

'Setting the PivotTable report shows grand totals for rows.       

pivotTable.RowGrand = True

 

 

'Setting the PivotTable report shows grand totals for columns.       

pivotTable.ColumnGrand = True

 

 

'Setting the PivotTable report displays a custom string in cells that contain null values.

pivotTable.DisplayNullString = True

pivotTable.NullString = "null"

 

 

'Setting the PivotTable report's layout

pivotTable.PageFieldOrder = PrintOrderType.DownThenOver

 

 

[JAVA]

 

 

//Setting the PivotTable report shows grand totals for rows.       

pivotTable.setRowGrand(true);

 

 

//Setting the PivotTable report shows grand totals for columns.       

pivotTable.setColumnGrand(true);

 

 

//Setting the PivotTable report displays a custom string in cells that contain null values.

pivotTable.setDisplayNullString(true);

pivotTable.setNullString("null");

 

 

//Setting the PivotTable report's layout

pivotTable.setPageFieldOrder(OrderType.DOWN_THEN_OVER);

 

Setting PivotFields Format Options

 

Setting Row/Column/Page fields format

[C#]

 

 

//Accessing the row fields.

PivotFields pivotFields = pivotTable.RowFields;

//Accessing the first row field in the row fields.

PivotField pivotField = pivotFields[0];

 

 

//Setting Subtotals.

pivotField.SetSubtotals(PivotFieldSubtotalType.Sum,true);

pivotField.SetSubtotals(PivotFieldSubtotalType.Count,true);

 

 

//Setting autosort options.

//Setting the field auto sort.

pivotField.IsAutoSort = true;

//Setting the field auto sort ascend.

pivotField.IsAscendSort = true;

//Setting the field auto sort using the field itself.

pivotField.AutoSortField = -1;

 

 

//Setting autoShow options.

//Setting the field auto show.

pivotField.IsAutoShow = true;

//Setting the field auto show ascend.

pivotField.IsAscendShow = false;

//Setting the auto show using field(data field).

pivotField.AutoShowField =0;

 

 

[VB]

 

 

'Accessing the row fields.

Dim pivotFields As PivotFields = pivotTable.RowFields

'Accessing the first row field in the row fields.

Dim pivotField As PivotField = pivotFields(0)

 

 

'Setting Subtotals.

pivotField.SetSubtotals(PivotFieldSubtotalType.Sum,True)

pivotField.SetSubtotals(PivotFieldSubtotalType.Count,True)

 

 

'Setting autosort options.

'Setting the field auto sort.

pivotField.IsAutoSort = True

'Setting the field auto sort ascend.

pivotField.IsAscendSort = True

'Setting the field auto sort using the field itself.

pivotField.AutoSortField = -1

 

 

'Setting autoShow options.

'Setting the field auto show.

pivotField.IsAutoShow = True

'Setting the field auto show ascend.

pivotField.IsAscendShow = False

'Setting the auto show using field(data field).

pivotField.AutoShowField =0

 

 

[JAVA]

 

 

//Accessing the row fields.

PivotFields pivotFields = pivotTable.getRowFields();

//Accessing the first row field in the row fields.

PivotField pivotField = pivotFields.get(0);

 

 

//Setting Subtotals.

pivotField.setSubtotals(PivotFieldSubtotalType.SUM,true);

pivotField.setSubtotals(PivotFieldSubtotalType.COUNT,true);

 

 

//Setting autosort options.

//Setting the field auto sort.

pivotField.setAutoSort(true);

//Setting the field auto sort ascend.

pivotField.setAscendSort(true);

//Setting the field auto sort using the field itself.

pivotField.setAutoSortField(-1);

 

 

//Setting autoShow options.

//Setting the field auto show.

pivotField.setAutoShow(true);

//Setting the field auto show ascend.

pivotField.setAscendShow(false);

//Setting the auto show using field(data field).

pivotField.setAutoShowField(0);

 

Setting Data fields format

[C#]

 

 

//Accessing the data fields.

PivotFields pivotFields = pivotTable.DataFields;

//Accessing the first data field in the data fields.

PivotField pivotField = pivotFields[0];

 

 

//Setting data display format

pivotField.DataDisplayFormat = PivotFieldDataDisplayFormat.PercentageOf;

//Setting the base field.

pivotField.BaseField = 1;

//Setting the base item.

pivotField.BaseItemPostion = PivotItemPosition.Next;

 

 

//Setting number format

pivotField.Number = 10;

 

 

[VB]

 

 

'Accessing the data fields.

Dim pivotFields As PivotFields = pivotTable.DataFields

'Accessing the first data field in the data fields.

Dim pivotField As PivotField = pivotFields(0)

 

 

'Setting data display format

pivotField.DataDisplayFormat = PivotFieldDataDisplayFormat.PercentageOf

'Setting the base field.

pivotField.BaseField = 1

'Setting the base item.

pivotField.BaseItemPostion = PivotItemPosition.Next

 

 

'Setting number format

pivotField.Number = 10

 

 

[JAVA]

 

 

//Accessing the data fields.

PivotFields pivotFields = pivotTable.getDataFields();

//Accessing the first data field in the data fields.

PivotField pivotField = pivotFields.get(0);

 

 

//Setting data display format

pivotField.setDataDisplayFormat(PivotFieldDataDisplayFormat.PERCENTAGE_OF);

//Setting the base field.

pivotField.setBaseField(1);

//Setting the base item.

pivotField.setBaseItem(PivotItemPosition.NEXT);

 

//Setting number format

pivotField.setNumber(10);