Calculating a Weighted Average Function

• Aug 03, 2020
• 5 minutes to read

This tutorial demonstrates how to calculate a weighted average function in a report, for instance,calculate a weighted average price for the units in stock within each product category: Sum (Unit Price * Units In Stock) / Sum (Units In Stock).

You can accomplish this using the following approaches:

• Using Report Summary Functions (Recommended)
• Using Aggregate Functions (Legacy)
• Calculating at Data Source Level

Using Report Summary Functions (Recommended)

You can calculate a weighted average at report level by specifying a control’s expression using several built-in report summary functions.

Note

You can use this approach when the UserDesignerOptions.DataBindingMode is set to DataBindingMode.Expressions or DataBindingMode.ExpressionsAdvanced.

See the next section to learn about the legacy approach.

1. Open an existing reporting application or create a new one from scratch.
2. Bind a report to the sample Northwind database’s Products table (the nwind.mdb file is included in the XtraReports installation).

3. Group the report’s data against the CategoryName data field (for instance, using the Group and Sort Panel) and construct a layout like the following:

   

4. Add the Group Footer band to the report and drop an XRLabel control on this band to display the summary result.

   Click the label’s smart tag and set its Summary Running property to Group.

   

5. Click the Expression property’s ellipsis button. This invokes the Summary Expression Editor where you can specify a custom expression with multiple built-in functions from the Functions | Summary section. Report summary functions start with the “sum” prefix to help differentiate them from aggregate functions.

   

6. You can also use the control’s Format String property to format the summary’s value. For instance, set this property to Weighted Average Price: {0:c2}.

Using Aggregate Functions (Legacy)

You can create a calculated field and use a standard aggregate function in its expression to evaluate a weighted average at the report level.

Note

Use the approach described in this section when the UserDesignerOptions.DataBindingMode is set to DataBindingMode.Bindings.

See the previous section to learn about solving this task using expression bindings.

Tip

Online Example: How to calculate a weighted average function

1. Open an existing reporting application or create a new one from scratch.
2. Bind a report to the sample Northwind database’s Products table (the nwind.mdb file is included in the XtraReports installation) and construct the required report layout.

3. Right-click any item in the Field List‘s data source node, and in the invoked context menu, select Add Calculated Field.

   

4. Select the created calculated field and switch to the Properties window. Specify the CalculatedField.Name property, set the CalculatedField.FieldType to Decimal and click the Expression property’s ellipsis button.

   

5. In the invoked Expression Editor, specify the following expression using the Sum aggregate function:

   [][[CategoryID] == [^.CategoryID]].Sum([UnitPrice] * [UnitsInStock]) / [][[CategoryID] == [^.CategoryID]].Sum([UnitsInStock])

   

   To construct a valid aggregate expression, use the following format, which consists of four parts.

   [<Collection>][<Condition>].<Aggregate>(<Expression>)

   • <Collection> - Specifies a collection against which an aggregated value should be calculated. It can be the relationship name in a case of a master-detail relationship, or the name of a collection property exposed by the target class. For example, [CategoriesProducts][[CategoryId]>5].Count(). Empty brackets [] indicate the root collection.
   • <Condition> - Specifies a condition defining which records should participate in calculating an aggregate function. To obtain an aggregated value against all records, delete this logical clause along with square brackets (for instance, [].Count()).
   • <Aggregate> - Specifies one of the available aggregate functions listed in the Aggregate enumeration.
   • <Expression> - Specifies an expression evaluating values to be used to perform calculation. For example, [][[CategoryID] > 5].Sum([UnitPrice]*[Quantity]). The Count function does not require field values to count the records, so leave the round brackets empty for this function.

   You can refer to the currently processed group using the Parent Relationship Traversal Operator (‘^’). This allows you to calculate aggregates within groups using expressions like the following: [][[^.CategoryID] == [CategoryID]].Sum([UnitPrice]).

   For more information, see Criteria Language Syntax and Expression Operators, Functions, and Constants.

6. Add the created calculated field to the report as an ordinary data field and format its value.

Calculating at Data Source Level

You can calculate summaries at data source level using an SqlDataSource component and use them in a report as regular data fields. Then, create a calculated field with an appropriate expression utilizing these fields.

Tip

Online Example: How to calculate a weighted average function

1. When creating a new data-bound report using the Report Wizard or binding an existing one to an SQL data source, go to the query customization page. On this page, click the button for the Queries category to create a new query using the Query Builder.

   

   To customize an existing data source, right-click the data source in the Report Explorer or Field List and select Manage Queries… in the context menu.

   

   In the invoked Manage Queries dialog, click the required query’s ellipsis button.

   

   In the invoked wizard page, select the Query option and click the Run Query Builder… button.

   

2. In the Query Builder, add the Products and Categories tables to a query. A simple Northwind database (the nwind.mdb file is included in the XtraReports installation) contains these tables. Enable the CategoryID, CategoryName and UnitsInStock fields’ checkboxes to include them in the query.

   

3. In the column list under the data source editor, apply grouping to the CategoryID and CategoryName fields and the Sum aggregate function to the UnitsInStock field.

   

4. Specify an expression for a new column by clicking the corresponding ellipsis button. In the invoked Expression Editor, specify an expression that multiplies the UnitPrice and UnitsInStock fields as in the following image:

   

5. Apply the Sum aggregation function to the previously created column as well.

   The image below demonstrates the resulting query.

   

6. Click OK to complete the Query Builder, and then click Finish to exit the wizard.

7. Go to the Field List, right-click any item inside the data source node, and in the invoked context menu, select Add Calculated Field.

   

8. Right-click the created calculated field and select Edit Expression….

   

9. In the invoked Expression Editor, construct the following expression and click OK:

   

10. Add the created calculated field to the report as an ordinary data field and format its value.