Pivot Grid

A Pivot Grid control presents data from a data source in a cross-tabulated form to create a crosstab report. It calculates summaries and summary totals against specific fields, and displays the summary values within data cells.

A designer is used to customise the Pivot Grid. It can be displayed using the control's Smart Tag.

The Pivot Grid displays data in a manner similar to Pivot Tables in Microsoft Excel. Column headers display unique values from one data field, for example car models. Row headers display unique values from another field, for example dates. Each cell displays a summary for the corresponding row and column values. By specifying different data fields, the report can display, for example, the total number of cars sold on a particular date, or the total number of deals. In this way, a compact layout for data analysis can be created.

A tutorial on how to use the Pivot Grid control can be found at Cross-Tab Report.

In the Property Grid, the Pivot Grid control's properties are divided into the following groups:

Appearance

Appearance

Used to define the appearance properties (such as Background Color, Foreground Color, Font, etc.) for the Pivot Grid's elements (Cell, Field Value, Filter Separator, Header Group Line).

Formatting Rules

Displays the Formatting Rules Editor which enables users to choose which rules should be applied to the band during report generation, and define the precedence of the applied rules. For further information, refer to Conditionally Change a Control's Appearance.

Styles

Used to display the Styles Editor, which is used to manage and customize the control's styles, which then can be assigned to the Pivot Grid's elements.

Behaviour

Anchor Vertically

Specifies the vertical anchoring style of the Pivot Grid, so that after page rendering it stays attached to the top control, bottom control, or both.

Keep Together

Specifies whether the contents of the control can be horizontally split across pages. In other words, if the control occupies more space than remains on the page, this property specifies whether this Pivot Grid should be split between the current page and the next, or whether it will be printed entirely on the next page. If a control does not fit on the next page either, then the Pivot Grid will be split despite this property's setting.

Scripts

Contains events which can be handled with scripts.. For further information on scripting, refer to Handle Events via Scripts.

Visible

Specifies whether the control is visible when using the print preview.

Data

Data Adapter

Determines the data adapter that will populate the Pivot Grid's data source specified by the Data Source property. It is automatically set to the appropriate value, when the Data Member property is defined. For further information, refer to Cross-Tab Report.

Data Member

Specifies the data source member which supplies data to a Pivot Grid. For further information, refer to Cross-Tab Report.

Note:
It is not usually necessary to specify the Data Member property when binding a Pivot Grid to data. This property should only be set directly if the dataset contains more than one table.

Data Source

Determines a Pivot Grid's data source. For further information, refer to Cross-Tab Report.

Fields

Displays the Pivot Grid Field Collection Editor, used to manage and fully customize a Pivot Grid's fields.

OLAP Connection String

Specifies a connection string to a cube in an Microsoft Analysis Services database. A sample connection string is shown below:

OLAPConnectionString='Provider=msolap;Data Source=localhost;Initial

Catalog=Adventure Works DW;Cube Name=Adventure Works;Query Timeout=100;'

A connection string can be built via the Connection String Editor. To display it, click the ellipsis for the OLAP Connection String property.

To represent information from the bound cube, create specific Pivot Grid fields, and bind them to the required fields in the data source.

If the OLAP Connection String property is set to a valid string, the value of the Data Source property is cleared. Setting the Data Source property to a valid object clears the OLAP Connection String property.

Prefilter

When this property is expanded in the Property Grid, its Enabled property can be set to Yes, and the Pivot Grid Prefilter window accessed from the Criteria property.

This window enables users to build complex filter criteria with an unlimited number of filter conditions, combined by logical operators. It provides a set of logical operators that significantly simplify the process of creating filters for text, numeric and date-time fields.

Note:
The Prefilter property is not supported in OLAP mode.

Design

(Name)

Determines a control's name, by which it can be accessed in the Report Explorer, Property Grid or by scripts.

Layout

Location

Specifies the location of the control in report measurement units.

Size

Specifies the size of the control in report measurement units.

Navigation

Bookmark and Parent Bookmark

These properties are used to create a hierarchical structure within a report called a document map. For further information, refer to Add Bookmarks.

Options

Data Field Options

Used to customise the options which control the presentation of the data fields.

Data Options

Used to specify whether a Pivot Grid's fields are case sensitive or not.

Print Options

Used to customise the Print options of a Pivot Grid.

View Options

Used to customise the Pivot Grid's display options.