The Group Filter

Logi Info, Logi Report, Logi ETL
v9.2 - 14 Oct 2008

The Group Filter groups data rows in a datalayer element and gives developers the ability to create aggregate values from the records. Its behavior is analogous to the GROUP BY clause in a SQL statement but, unlike a query, it's applied to data after it's been retrieved into the datalayer. The Group Filter is available to all datalayer elements but is primarily designed for those lacking SQL support, such as DataLayer.XML and DataLayer.Web Service. This document discusses use of this element; topics include:

Grouping Data
Working with Group Aggregate Columns

Grouping Data

The following example uses a Group Filter to group data in the datalayer on a specific column:

As shown above, a Group Filter element is added as a child to the DataLayer.XML File element.
Its Group Column attribute is set to the name of a column in the datalayer. The data will be grouped based on the values in this column.
Its Data Type column is set to the data type of the column named in the Group Column attribute. This ensures unambiguous ordering of the data.
Its Keep Grouped Rows attribute is set to True to ensure that all rows are retained in the datalayer.

The resulting output is shown above. The rows are grouped by CategoryID and all rows for each category are included.

GROUPING FOR DISTINCT ROWS

There are many situations in which developers may need to use only distinct rows, for instance, to populate an Input Select List control. This can be done with grouping, as the following example illustrates:

As shown above, a Group Filter element is added as a child to the DataLayer.XML File element.
Its Group Column attribute is set to the name of a column in the datalayer. The data will be grouped based on the values in this column.
Its Data Type column is set to the data type of the column named in the Group Column attribute. This ensures unambiguous ordering of the data.
The Keep Grouped Rows attribute is left blank; its default value is False. This causes all rows after the first one in each grouping to be removed from the datalayer.

The resulting output is shown above. The rows are grouped by CategoryID but only one row exists for each category.

GROUPING ON MULTIPLE COLUMNS

Grouping can be accomplished on more than one column in the datalayer at the same time. This example shows the effects of grouping multiple columns:

As shown above, a Group Filter element is added as a child to the DataLayer.XML File element.
Its Group Column attribute is set to the names of columns in the datalayer, in a comma-separated list. The data will be grouped based on the values in these columns, in the order of the column names in the list.
Its Data Type column is set to the data type of the column named in the Group Column attribute. This ensures unambiguous ordering of the data.
Its Keep Grouped Rows attribute is set to True to ensure that all rows are retained in the datalayer.

The resulting output is shown above. The rows are grouped first by Room and then by Organizer.

Working with Group Aggregate Columns

A very common requirement when grouping data is to perform some kind of aggregation, such as summing or counting, providing a summary for each group. The Group Aggregate Column element gives developers the ability to generate this summary data for each grouping column in the datalayer.

The following example builds on the earlier example in the section "Grouping For Distinct Rows", creating a total product sales value for each product category:

As shown above, a Group Aggregate Column element is added to the earlier example.
Its Aggregate Column attribute value is set to the name of the column that we want to aggregate.
Its Aggregate Function attribute value is set to Sum. Other options include Average, Count, DistinctCount, Max, Median, Min, Mode, and StdDeviation.
Its ID attribute specifies the name of a new column that will be added to the datalayer, containing the summarized data values.
Its Data Type attribute specifies the data type of the column named in the Aggregate Column attribute.

The data in the new column in the datalayer is available at runtime like any other, as shown above, through the use of an @Data token.

And the resulting output is shown above. All records have been grouped by CategoryID, only the first record for each group has been retained, and ProductSales values for each group has been summed and is now available in the Total Sales column.