Aggregates

See Also

An aggregate is a special type of Field object that you can use to have data automatically calculated based on a hierarchy of Command objects. You can define an aggregate on any relation or grouped-based hierarchy. Each aggregate you define adds a new Field object to the current Command object. At run time, you can access the calculated data just like any other field.

In addition, you can create a Grand Total Aggregate, which can be used on any top-level Command object to calculate its values. When a Grand Total Aggregate is created, a new Command object is created as the parent of the Command object on which the calculated values are based. Thus, the existing Command object becomes a child Command object and is referenced through a field in the parent Command object.

For example, if an aggregate is based on the CustID field, you can obtain the total number of orders for each customer. This is illustrated by the following figure.

The aggregate can be based any of the operations in the following table.

Operation Description
Any Returns one of the values from the rows of the selected field.
Average Returns the average of the values of the selected field.
Count Returns the count of the number of records of the selected field.
Maximum Returns the highest value of the selected field.
Minimum Returns the smallest value of the selected field.
Standard Deviation Returns the standard deviation of the selected field.
Sum Returns the sum of all the values of the selected field.

To create an aggregate within a Command hierarchy

  1. Right-click any Command object and select Properties from the shortcut menu. (To create an aggregate other than a Grand Total, the Command object must have a child or be grouped.) The Command Properties dialog box appears.

  2. Click the Aggregates tab. The Aggregates frame lists all currently defined aggregates.

  3. Click Add to add an aggregate to the Aggregates box.

    Note   To delete an aggregate from the Aggregates box, select the aggregate to delete, and then click Remove.

  4. Specify the Aggregate Settings information, as follows:
    Item Description
    Name The name of the Field object that will be added to the Command object. At run time, this field will contain the calculated aggregate value.

    You can change this to a more meaningful, unique name that describes the aggregate. For example, if you are creating an aggregate that provides the average number of items on each order, an appropriate name may be AverageOrderNumber.
    Function Select the operation to associate with the aggregate: Any, Average, Count, Maximum, Minimum, Standard Deviation, or Sum, as described above.
    Aggregate On Select a grouping, a child Command object, or Grand Total from the drop-down list. The selected item is what the aggregate is based upon.

    Note   Grand Total is only available on top-level Command objects. Choosing Grand Total implies that an additional Command object will be created to provide a grand total of your data.
    Field Choose a Field object on which to base the aggregate. For example, if the aggregate is to provide the average number of orders, specify the field that contains the quantity of orders.
    Name If you have selected Grand Total in the Aggregate On box, specify a name for the Grand Total Command. If Grand Total is not selected, this option is disabled. Once created, the Grand Total Name displays as the name of a new top-level Command object within the Data Environment.

    Note   The Grand Total Name can be the same as the Aggregate Name.

  5. Click OK to save the aggregate definition and close the dialog box. The aggregate appears as a Field object of the selected Command object in the Data Environment outline view.