Pivot Grid

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

A designer is used to customize the Pivot Grid. It can be displayed using the 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 number of cars sold on a particular date, or the total number of sales, creating a compact layout for data analysis.

On the Properties window, the Pivot Grid control properties are divided into the following tabs:

Appearance

Appearance

Specifies the pivot grid appearance properties (for example Background Color, Foreground Color, Font) defining how the elements (for example, Cell, Field Value, Filter Separator, Header Group Line) are presented.

Formatting Rules

Displays the Formatting Rules Editor window, used to choose which rules are applied to a report during report generation, and to define the priority of the applied rules.

Location

Specifies the location of the control in report measurement units.

Size

Specifies the size of the control in report measurement units.

Styles

Defines styles for the control, as well as assigning an existing style to the control (or a newly created one).

Behavior

Anchor Horizontally

Specifies the horizontal anchoring style of the control, so that after page rendering it stays attached to the left control, right control, or both. The property setting is useful for data-bound controls located between controls, which are able to resize depending on their contents.

Anchor Vertically

Specifies the vertical anchoring style of the control, so that after page rendering it stays attached to the top control, bottom control, or both. The property setting is useful for data-bound controls located between upper and lower controls, which are able to resize depending on their contents.

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, the property specifies whether the control is split between the current page and the next, or printed entirely on the next page. If the control does not fit on the next page either, the control is split, regardless of the property value.

Scripts

Contains events which can be handled with scripts.

Visible

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

Data

Data Adapter

Specifies the data adapter that populates the pivot grid data source assigned using the Data Source property. It is automatically set to the appropriate value when the Data Member property is defined.

Data Member

Specifies the data source member which supplies data to the pivot grid.

It is not usually necessary to specify the Data Member property when binding a pivot grid to data. The property must only be set directly if the source dataset contains more than one table.

Data Source

Determines the pivot grid data source.

Fields

Displays the Field Collection Editor window, used to manage and fully customize pivot grid fields.

OLAP Connection String

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

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

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

Locate the OLAP Connection String property and click the ellipsis to display the Connection String Editor window to manage and customize the connection string.

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

Setting the OLAP Connection String property to a valid string clears the value of the Data Source property. Setting the Data Source property to a valid object clears the OLAP Connection String property.

Prefilter

Provides access to build complex filter criteria with an unlimited number of filter conditions, combined by logical operators. A set of logical operators are available that significantly simplify the process of creating filters for text, numeric and date-time fields.

The Prefilter property is not supported in OLAP mode.

Tag

Adds additional information to a control; for example, the Id, by which the control can then be accessed via scripts.

Miscellaneous

(Name)

Determines the name of the control, by which it can be accessed in the Report Explorer, Properties window or via scripts.

Bookmark and Parent Bookmark

These properties are used to create a hierarchical structure within a report called a document map.

Data Field Options

Provides access to properties which control the presentation of the data fields.

Data Options

Provides access to the pivot grid data specific properties.

Options Chart Data Source

Provides access to layout settings for pivot grid data that is used in a chart.

Print Options

Provides access to the pivot grid print options.

Right to Left

Specifies the content orientation of the controls.

Snap Line Margins

Specifies the margins for the control, which are maintained when the control is aligned using snap lines.

View Options

Provides access to the pivot grid display options.

Favorites

Right-click on the Favorites tab and select Edit Favorite Property List to display the Favorite Properties Editor, which is used to customise the available properties on the Favorites tab.

Refer to Cross-Tab Report, Conditionally Modify Control Display, Styles Concepts, Handle Events via Scripts and Add Bookmarks for further information.