Aggregate Functions are useful for performing calculations by iterating through a data set and computing the chosen function of your choice.  These types of functions are used in example such as summing every field of an entire database table, finding the minimum value for a particular attribute present in a repeating node set in XML, and much more.  In this section, you can find helpful information on each Aggregate Function available to you.

Figure 1:  The Aggregate Functions are listed in the Functions dialog.

 

Usage

To insert any Aggregate Function in your Data Diagram, you will first need to select Functions in the Side Menu to access the functions dialog.  Here, you will be able to access the Aggregate Functions group.  Simply select the Function that fits your need to insert into the Design Surface.  For the examples and descriptions below, we will use a database table that holds data based upon orders for an imaginary company.  

The Max Set function is used to iterate over a specified input data set to compute the maximum value found for a specified input parameter.  As an example, a report needs to find the specific value of the largest order that was placed.  For this, we'll use the Max Set function to iterate through our database table, and compute the maximum value for the Quantity field. Notice how the Data Set Connector is used to map the entire table data set and the Quantity field is connected to the function's input parameter Data Connector.  

Figure 2:  The Max Set function is used to retrieve the maximum value for the Quantity field.

Figure 3:  The output of the Max Set function reveals the largest order quantity.

The Min Set function is used to iterate over a specified input data set to compute the minimum value found for a specified input parameter.  As an example, a report needs to find the specific value of the smallest unit price that was offered for every order.  For this, we'll use the Min Set function to iterate through our database table, and compute the minimum value for the UnitPrice field. Notice how the Data Set Connector is used to map the entire table data set and the UnitPrice field is connected to the function's input parameter Data Connector.

Figure 4:  The Min Set function is used to find the lowest unit price in all orders present.

Figure 5:  The output shows the value 2 to be the minimum value present for unit price.

The Average Set function is used to calculate the average of the values present for a specified input parameter in a data set.  For this example, we want to retrieve what the average discount that was applied was for all orders.  In order to accomplish this goal, we will use the Average Set function, Map the table and function Data Set Connectors, and map the Discount field as the input parameter.

Figure 6:  The Average Set function is used to compute the average discount applied for orders.

Figure 7:  The output preview reveals the average discount applied to orders.

The Sum Set function is used for situations where you would like to compute the sum value for all values present for a specified data point.  As an example, we want to find the total combined sales for every order in our database table.  We first are going to use a Math Function to help us compute the total sale per product by multiplying the UnitPrice by the Quantity.  Then, we will use this value as our input parameter for our Sum Set Function.  This will sum all of these values as computed for the entire data set.

Figure 8:  The Sum Set Function is used to sum all computed values to find the total sales figure.

Figure 9:  The output reveals the combined sum value for all sales in the data set.

The Count Set function is used when you wish to return the computed count of records in a data set.  As an example, we need to know the total number of orders placed.  In order to accomplish this, we will use a Count Set function and use a Data Set Connection to map our orders table as the input set to count by the Count Set function.  Next, the result will be mapped to an output data source.

Figure 10:  The Count Set Function is used to return the record count value for the orders table.

Figure 11:  The Count Set function displays the count of all orders in the database table.