![]() ![]() |
The Total class exists as a holder for the aggregate functions. The Total class contains a number of functions for aggregating. These functions are BIRT extensions to JavaScript that summarize a set of rows. The BIRT Report Engine implements a function in the following two phases:
BIRT rewrites any script that contains an aggregate, creating a revised script that performs the two phases. The only time it is important to be aware of this process is when debugging an expression that contains an aggregate. Consider the example of showing a value in the footer of a group, where the value is a percent of the total for all the accounts. The following statement performs this action:
In the preceding example, Total.OVERALL causes BIRT to compute the sum aggregate over all rows, even though the expression appears in a group total. To compute the expression in the preceding example, BIRT makes one pass to compute the total balance and a second pass to compute the value for each account.
The general rules for the Total class are:
All aggregates allow two optional arguments to indicate filtering and grouping:
A filter argument provides a Boolean expression that BIRT evaluates on every row. BIRT only considers rows that match the filter when computing the aggregate. For example, a report could sum the credit limits of active customers to determine the maximum credit exposure.
The expression is executed in the data transform phase. It can access columns in the row, parameters, user-defined functions and other resources as defined in the context description above. For example:
A filter argument of null or undefined or Total.NO_FILTER means that no filter is provided. If the application provides no filter, the filter defaults to undefined. If you supply a group argument, you must supply either null or Total.NO_FILTER to indicate no filtering.
Reports frequently need to display totals for the current grouping level. For example, suppose a report is grouped by customers and orders and the detail records represent line items in orders. Each line item has a unit price and a quantity, as in the following aggregate:
This aggregate sums values over the current group. If placed in the footer, it displays the sum of line items for that order. If placed in the customer footer, it displays the sum of all the line items for all orders for that customer. And, if placed in the report footer, it displays the sum of all line items for all customers.
Sometimes, however, the application needs to access a total from a different grouping level. For example, you may want to know the total value of one order as a percentage of all orders for the customer. In this case, you must access the total for a group other than the current group. You do this using the grouping argument:
The group argument can be one of the following:
The following examples illustrate these kinds of group arguments:
Total.sum( row["myCol"] null, null ); // Current group Total.sum( row["myCol"], null, "Customer" ); // By name Total.sum( row["myCol"], null, "row[`custID']" ); // By group key Total.sum( row["myCol"], null, 1 ); // By group index Total.sum( row["myCol"], null, "Overall" ); // Grand totals Total.sum( row["myCol"], null, 0 ); // Grand totals
You must provide the filter argument when using the grouping argument. If you don't require a filter, give null as the value of the filter. The group index is not quoted. It must be given as a number. That is, 1 means the group at level 1, while "1" means the group named "1".
OVERALL
This aggregate function computes the mathematical mean value. If the expression evaluates to a number, then this function returns the average of those numbers. If the expression evaluates to a date, then the function returns the average date.
Total.ave( expr [, filter [, group ]] )
This function returns the average value of the given expression. Returns null if no rows were available.
The following statement returns the average age of students in a class:
To return the average birthday of students in a class, use the following statement:
Total.median aggregate
Total.mode aggregate
Total.movingAve aggregate
Total.stdDev aggregate
Total.variance aggregate
Total.weightedAve aggregate
This function counts the number of rows within the group.
Total.count( [ filter [, group ]] )
This example counts the number of male and female students in a class. It is necessary to create two data items. Set the first to:
Set the second to:
Total.countDistinct aggregate
Total.sum aggregate
This function computes the number of distinct values within the group or data set. The expr argument gives an expression used to group the values. The expression refers to a data row column. Null values are counted as one distinct value.
Total.countDistinct ( expr [, filter [, group ]] )
The number of distinct values within the group or data set. Returns zero if no rows were available.
Suppose we want to know the number of different countries represented by a group of students. We can define a data item that uses the following expression:
In this statement row.Country is a column that contains the name or code for the student's home country. Suppose that some rows contain null, meaning that we don't know the home country. Use the following statement to exclude such rows from the count:
Total.count aggregate
This aggregate function returns the first value that appears in a data set. This is the first value fetched from the data set when fetching rows using the sort order defined for the Table or List.
Total.first( expr [, filter [, group ]] )
This function returns the first value that appears in the sequence of rows, or null if the data set contains no rows.
Suppose that a report lists transactions for a given stock over a period of time. The following statement displays the earliest purchase of the stock:
Total.isBottomNPercent aggregate
Total.max aggregate
Total.min aggregate
This function returns a boolean that indicates if the value of a specified numeric field is one of the lowest n values.
Total.isBottomN( expr, n, [, filter [, group ]] )
This function returns true or false. This function returns true if the value in the numeric field is in the bottom n of all values in this column.
Suppose that a report colum has five rows, containing the values 1, 2, 3, 4 and 5. Total.isBottomN(expr, 2) tests if the field represented by expr contains a 1 or a 2, the lowest two numbers in the column.
This function returns a boolean that indicates if the value of a specified numeric field is in the bottom n percent of all values in the column.
Total.isBottomNPercent( expr, n, [, filter [, group ]] )
This function returns true or false. This function returns true if the value in the field is in the bottom n percent of all values in this column.
Suppose that a report colum has five rows, containing the values 1, 2, 3, 4 and 5. Total.isBottomNPercent(expr, 60) tests if the field represented by expr contains a 1, 2, or a 3, the lowest sixty percent of the numbers in the column.
This function returns a boolean that indicates if the value of a specified numeric field is one of the highest n values.
Total.isTopN( expr, n, [, filter [, group ]] )
This function returns true or false. This function returns true if the value in the numeric field is in the top n of all values in this column.
Suppose that a report colum has five rows, containing the values 1, 2, 3, 4 and 5. Total.isTopN(expr, 2) tests if the field represented by expr contains a 4 or 5, the highest two numbers in the column.
This function returns a boolean that indicates if the value of a specified numeric field is in the top n percent of all values in the column.
Total.isTopNPercent( expr, n, [, filter [, group ]] )
This function returns true or false. This function returns true if the value in the field is in the top n percent of all values in this column.
Suppose that a report colum has five rows, containing the values 1, 2, 3, 4 and 5. Total.isTopNPercent(expr, 60) tests if the field represented by expr contains a 3, 4, or 5, the highest sixty percent of the numbers in the column.
This function returns the last value that appears in a data set. This is the last value fetched from the data set when fetching rows using the sort order defined for the Table or List.
Total.last( expr [, filter [, group ]] )
This function returns the last value that appears in the sequence of rows, or null if the data set contains no rows.
Suppose that a report lists transactions for a given stock over a period of time. The following statement displays the most recent sale of the stock:
Total.first aggregate
Total.max aggregate
Total.min aggregate
This function computes the maximum value of the given expression. The expression is evaluated for each row, and the maximum value is retained. This function can work with number, date, or string.
Total.max( expr [, filter [, group ]] )
This function returns the maximum value of the given expression. It returns null if no rows were available.
The following statement finds the oldest student within a class:
Total.first aggregate
Total.isBottomNPercent aggregate
Total.min aggregate
This aggregate function computes the mathematical median value. Half the values fall above the median, and half below.
Total.median( expr [, filter [, group ]] )
This function returns a median value of the given expression. Returns null if no rows were available.
The following statement return the median age of students in a class:
And, to return the median birthday of students in a class, use the following statement:
Total.ave aggregate
Total.mode aggregate
Total.movingAve aggregate
Total.stdDev aggregate
Total.variance aggregate
Total.weightedAve aggregate
This aggregate function computes the minimum value of the given expression. The expression is evaluated for each row, and the minimum value is retained. This function can work with any simple type: number, date, or string.
Total.min( expr [, filter [, group ]] )
This function returns the minimum value of the given expression. Returns null if no rows were available.
The following statement returns the age of the youngest student within a class:
Total.first aggregate
Total.isBottomNPercent aggregate
Total.max aggregate
This aggregate function computes the mathematical mode value. The mode is the value that occurs most frequently in the data. For example, in the sequence {1, 2, 3, 2, 4, 7}, 2 is the mode because it appears twice, while all other numbers appear only once. If a data set has multiple modes, such as in the sequence {1,2,3,2,3}, the mode aggregate returns null.
Total.mode( expr [, filter [, group ]] )
This function returns a mode value of the given expression. Returns null if no rows were available, or if the data has more than one mode.
The following statement returns the most frequently occurring age of students in a class:
Total.ave aggregate
Total.median aggregate
Total.movingAve aggregate
Total.stdDev aggregate
Total.variance aggregate
Total.weightedAve aggregate
This aggregate function computes a moving average. The expr argument gives the value to average, and the window argument gives the number of rows to consider. The rows are averaged in the order determined by the context, usually the order specified by a sort for a List or Table element.
Total.movingAve( expr, window [, filter [, group ]] )
This function returns a moving average value of the given expression. Returns null if no rows were available.
Suppose a report lists the daily price for a stock. To display the moving average of that stock over the last five days, use the following statement:
Total.ave aggregate
Total.median aggregate
Total.mode aggregate
Total.stdDev aggregate
Total.variance aggregate
Total.weightedAve aggregate
This function examines a set of values and returns the value that is at some specified percentile of all the values in the set. For example, you might want to know what score represents the 90th percentile of all scores on a test.
This function returns the value that is at the specified percentile in the set of analyzed values.
The following statement returns the 50th percentile score value:
If the set of test scores in the specified column is 10, 20, 30, 40, and 50, this statement would return 30.
This function computes the percentage ranking of a value in a specified field.
percentRank( expr, filter [, group ]] )
This function returns the percentage ranking of the value in the specified field. The return value ranges from zero to one.
The following statement returns the percent ranking of a score among all the scores in a column:
If the scores in the evaluation range are 10, 10, 20, and 50, and the score in the cell containging this statement is 30, the return value is .5 because half the values are above 20 and half are below 20.
This function computes t.
percentSum( expr [, filter [, group ]] )
This function returns the percentage of the contribution of this value to the sum of all the values in the range.
The following statement returns the percentage of contribution of this order amount to the sum of all order amounts:
This function computes the value within a defined set of values for which 0%, 25%, 50%, 75%, or 100% of the values in the set are greater.
quartile( expr, quartile, [, filter [, group ]] )
This function returns the value within the specified set that falls at the specified quartile.
The following statement returns the order amount that for which 75% are smaller and 25% are greater:
This function computes where a value ranks among a set of values. The ranking value ranges from 1 to the number of values in the set. If two values are identical, they have the same ranking.
rank( expr [, filter [, group ]] )
This function returns an integer that reflects the value ranking and has a range of 1 to the number of items in the set of values
The following statement returns the rank of the OrderAmt field among all order amounts in the OrderAmt column.
This function computes a running count of rows.
runningCount( filter [, group ]] )
This function returns the running count of rows.
The following statement returns the running count for a report:
Total.count aggregate
Total.percentRank aggregate
This aggregate function computes the statistical standard deviation of a sequence of numbers. The standard deviation is a measure of the spread of a set of values.
Total.stdDev( expr [, filter [, group ]] )
This function returns a standard deviation of the given expression. Returns null if no rows were available.
Total.ave aggregate
Total.median aggregate
Total.mode aggregate
Total.movingAve aggregate
Total.variance aggregate
Total.weightedAve aggregate
This aggregate function computes the sum resulting from adding up a value for each row in the group. The value for each row is computed using the expression given in the expr argument. The sum is obtained by adding all these values together.
Total.sum( expr [, filter [, group ]] )
The sum of the given expression. Returns zero if no rows were available.
The following statement totals order amounts for a customer:
Total.count aggregate
Total.percentRank aggregate
This aggregate function computes the statistical variance of a sequence of numbers. The variance is a measure of the spread of a set of values.
Total.variance( expr [, filter [, group ]] )
This function returns the variance of the given expression. Returns null if no rows were available.
Total.ave aggregate
Total.median aggregate
Total.mode aggregate
Total.movingAve aggregate
Total.stdDev aggregate
Total.weightedAve aggregate
This aggregate function computes the mathematical weighted mean value. If either the expr or weight arguments evaluate to null, then the row is excluded from the average.
Total.weightedAve( expr, weight [, filter [, group ]] )
This function returns the weighted average value of the given expression. Returns null if no rows were available.
Suppose that a finance application tracks batches of a given stock purchased at different times. Each batch has a different purchase price, and a number of shares purchased at that price. The following statement computes the weighted average purchase price:
Total.ave aggregate
Total.median aggregate
Total.mode aggregate
Total.movingAve aggregate
Total.stdDev aggregate
Total.variance aggregate
![]() ![]() |