All docs
V20.2
20.2
20.1
19.2
19.1
The page you are viewing does not exist in version 19.1. This link will take you to the root page.
18.2
The page you are viewing does not exist in version 18.2. This link will take you to the root page.
18.1
The page you are viewing does not exist in version 18.1. This link will take you to the root page.
17.2
The page you are viewing does not exist in version 17.2. This link will take you to the root page.

Intermediate Level Aggregations

  • 2 minutes to read

The Optimized calculation engine introduces a new aggregate function not available in Legacy and LegacyOptimized modes - the aggr function.

TIP

Use the PivotGridOptionsData.DataProcessingEngine property to enable the Optimized engine.

The aggr function uses the summary function to calculate values for data obtained from the first specified data source column, group data by the next specified columns, and calculate group summaries.

The aggr function can be nested, and you can summarize aggregated data in a single expression.

The aggr function has the following syntax:

aggr(summaryExpression, column1, column2, ...) 

The first argument is a summary expression calculated against a data source column, followed by a set of data columns whose values are grouped to calculate summaries for the first argument.

NOTE

A calculated field that contains the aggr function is not displayed in the underlying data.

For example, the data below is Northwind orders for several years:

To show the percentage of sales in the country with the highest sales:

  1. Create the SalesByYearByCountry field and calculate its value with the following expression:

    aggr(Sum([ExtendedPrice]), GetYear([OrderDate]), [Country]) 
    

    Add the SalesByYearByCountry field to the Pivot Control’s Data area area.

  2. Add one more field that uses ExpressionBinding with the new expression:

    Sum([ExtendedPrice]) / Max(aggr( Max([SalesByYearByCountry]), GetYear([OrderDate])))
    
  3. Add the created SalesByYearByCountry field to the Data area and set the summary type to "Max".

The following picture illustrates the resulting Pivot Grid with Average sales by year and Percent of Max sales by year fields:

Examples for WinForms Pivot Grid

Examples for ASP.NET Pivot Grid

Examples for Dashboards