Grouping

In This Topic

The examples in this topic consider the sample data set described in Grouping, Filtering and Sorting:

Each data grouping has a collection of grouping expressions.

When a data grouping with grouping expressions is processed, the input recordset is split into one or more sub-recordsets called group recordsets or simply groups. Group recordsets do not overlap and their union is the input recordset. Two records from the input recordset belong to the same group, if all grouping expressions evaluate to the same value for both records. For example:

Let A be a root data grouping with a single grouping expression: =Fields!company. The generated groups are: A1={1,2,3}, A2={4,5,6} and A3={7,8,9}. (e.g. group by the company field)

Let A be a root data grouping with a single grouping expression: =Fields!sales > 150. The generated groups are: A1={1,2,3,4,5,9} and A2={6,7,8}.

Let A be a root data grouping with two grouping expressions: =Fields!company, =Fields!sales > 150. The generated groups are: A1={1,2,3}, A2={4,5}, A3={6}, A4={7,8} and A5={9}.

A data member is created for each one of the groups, which the grouping expressions produced (if it passes the group filters - see Filtering). The children of each data member are the data members produced by the child data groupings, evaluated towards the data member group recordset. For example:

Let A be a root data grouping with grouping expression: =Fields!company and let B be a child data grouping of A with grouping expression: =Fields!region. A produces 3 data members: A1, A2 and A3 (for Bigfoot, Maison and Leka respectively). For A1, B produces three data members - A1.B1, A1.B2, A1.B3 (for Bigfoot - Europe, Bigfoot - US and Bigfoot - Asia respectively). For A2, B again produces three data members - A2.B1, A2.B2, A2.B3 (for Maison - Europe, Maison - US and Maison - Asia respectively) and so on. The group recordset for A1.B1 is {1}, for A1.B2 is {2} etc.

A data member is an instance of the data grouping in the context of a group produced by the data grouping grouping expressions. The properties of data member are the properties of the data grouping, evaluated towards the group recordset. You can use aggregate functions in the properties of a data grouping. For example:

Let A be a root data grouping with grouping expression: =Fields!company and let A has a property P with expression:=SUM(Fields!profit). A produces three data members A1, A2 and A3 with group recordsets A1={1,2,3}, A2={4,5,6} and A3={7,8,9}. Each data member has a property P. For A1 the value of P is (15+50+80)=145, for A2 the value of P is (18+20+70)=108, for A3 the value of P is (10+60+30)=100.

In essence: Multiple grouping expressions help you group the data by multiple rules which are combined with logical AND. Child data groupings help you organize the data hierarchically. Grouping expressions are evaluated towards a single record. Data grouping property expressions are evaluated towards the group recordset.

When the data grouping has no grouping expressions, only a single data member is created for it (if at least one record from the input recordset passed the record filters). The absence of grouping expressions also instructs the filter and sorting rules to operate on single records, rather than on groups (see Filtering and Sorting)

It is often required to create groups for each record of the data source, which is generally a requirement when you want to convert the pivot to a simple table. You can do that by using the =Fields![RowIndex] as grouping expression.