Calculate a Weighted Average Function

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).

Use 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 if expression bindings are enabled in the Report Designer (the Property Grid provides the Expressions tab).

See the next document sections to learn about alternative approaches.

  1. Open an existing report or create a new one from scratch.
  2. Bind a report to a required data source.

  3. Group the report's data using the Group and Sort Panel and construct a layout like the following:

  4. Add the Group Footer band to the report and drop a Label 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}.

Use 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.

  1. Open an existing report or create a new one from scratch.
  2. Bind a report to a required data source 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 Property Grid. Specify the Name property, set the Field Type to Decimal and click the Expression property's ellipsis button.

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

    [][[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 example, [].Count()).
    • <Aggregate> - Specifies one of the available aggregate functions.

    • <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 Expression Syntax.

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

Calculate at Data Source Level

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

  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 plus 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 required tables to a query and enable checkboxes for the field you want to include in the query.

  3. In the column list under the data source editor, apply grouping and the Sum aggregate function to the field that stores the units in stock.

  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 unit price and the units in stock 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.