Calculate a Weighted Average
- 4 minutes to read
Note
Set the UserDesignerOptions.DataBindingMode property to DataBindingMode.Bindings to use the approach described in this topic.
Refer to the Calculate a Weighted Average topic in the Shape Data (Expression Bindings) section for information on how to calculate weighted average data.
See Data Binding Modes for the available bind modes.
Use one of the following approaches to calculate weighted average data:
Aggregate Functions
You can create a calculated field and use a standard aggregate function in its expression to evaluate a weighted average at the report level.
Tip
Online Example: How to calculate a weighted average function
- Open a report application or create a new one from scratch.
- Bind a report to the sample Northwind database’s Products table (the nwind.mdb file is included in the XtraReports installation) and construct the report’s layout.
Right-click any item in the Field List‘s data source node, and in the invoked context menu, select Add Calculated Field.
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.
In the invoked Expression Editor, specify the following expression:
[][[CategoryID] == [^.CategoryID]].Sum([UnitPrice] * [UnitsInStock]) / [][[CategoryID] == [^.CategoryID]].Sum([UnitsInStock])
To construct a valid aggregate expression, use the following format:
[<Collection>][<Condition>].<Aggregate>(<Expression>)
- <Collection> - Specifies a collection to calculate an aggregated value against. It can be the relationship name for a master-detail relationship, or a collection property’s name exposed by the target class. For example, [CategoriesProducts][[CategoryId]>5].Count(). Empty brackets [] indicate the root collection.
- <Condition> - Specifies a condition that defines which records to use for the aggregate function calculation. To calculate an aggregated value against all records, delete this logical clause and its square brackets (for example, [].Count()).
- <Aggregate> - Specifies one of the available aggregate functions listed in the Aggregate enumeration.
- <Expression> - Specifies the expression to use. For example, [][[CategoryID] > 5].Sum([UnitPrice]*[Quantity]). The Count function does not require field values to count the records (the round brackets can be empty for this function).
Use the Parent Relationship Traversal Operator (‘^’) to refer to the processed group (for instance, [][[^.CategoryID] == [CategoryID]].Sum([UnitPrice])). This allows you to calculate aggregates within groups.
For more information, see Expression Constants, Operators, and Functions.
Add the created calculated field to the report as an ordinary data field and format its value.
Data Source Level
Use a SqlDataSource component to calculate summaries at data source level. You can use these summaries as regular data fields in your report. Then, create a calculated field where your expression uses these fields.
Tip
Online Example: How to calculate a weighted average function
When you use the Report Wizard or bind a report to an SQL data source, go to the query customization page and click the
button for the Queries category. Then use the Query Builder to create a new query.
To customize a 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 query’s ellipsis button.
In the invoked wizard page, select the Query option and click the Run Query Builder… button.
In the Query Builder, add the Products and Categories tables from the sample Northwind database (the nwind.mdb file is included in the XtraReports installation) to the query. Enable the CategoryID, CategoryName and UnitsInStock fields’ checkboxes to include them in the query.
In the column list under the data source editor, group data by the CategoryID and CategoryName fields and apply the Sum aggregate function to the UnitsInStock field.
Click a new column’s expression ellipsis button. In the invoked Expression Editor, specify an expression that multiplies the UnitPrice and UnitsInStock fields as in the image below:
Apply the Sum aggregation function to the previously created column as well.
The image below shows the created query.
Click OK to complete the Query Builder, then click Finish to exit the wizard.
Go to the Field List, right-click any item inside the data source node. In the invoked context menu, select Add Calculated Field.
Right-click the created calculated field and select Edit Expression….
In the invoked Expression Editor, construct the expression and click OK:
- Add the created calculated field to the report as an ordinary data field and format its value.