Previous TopicNext Topic


Class Total

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:

(balance / Total.sum( balance, Total.OVERALL ) ) * 100.0 

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:

About grouping and filtering

All aggregates allow two optional arguments to indicate filtering and grouping:

Total.<aggregate function> ( ..., filter, group ) 

About filter argument

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:

Total.sum( row["CreditLimit"], row["Active"] == 'Y' ); 

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.

About group argument

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:

Total.sum( row["price"] * row["quantity"] ) 

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:

Total.sum( row.CreditLimit, null, "Customer" ); 

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

Static properties

OVERALL

functions

Total.ave

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.

Syntax

Total.ave( expr [, filter [, group ]] )

Arguments

Returns

This function returns the average value of the given expression. Returns null if no rows were available.

Example

The following statement returns the average age of students in a class:

Total.ave( row.Age ) 

To return the average birthday of students in a class, use the following statement:

Total.ave( row.BirthDate ) 

See also

Total.median aggregate

Total.mode aggregate

Total.movingAve aggregate

Total.stdDev aggregate

Total.variance aggregate

Total.weightedAve aggregate

Total.count

This function counts the number of rows within the group.

Syntax

Total.count( [ filter [, group ]] )

Arguments

Example

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:

Total.sum( row.sex == 'M' ); 

Set the second to:

Total.sum( row.sex == 'F' ); 

See also

Total.countDistinct aggregate

Total.sum aggregate

Total.countDistinct

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.

Syntax

Total.countDistinct ( expr [, filter [, group ]] )

Arguments

Returns

The number of distinct values within the group or data set. Returns zero if no rows were available.

Example

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:

Total.countDistinct( row.Country ) 

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.countDistinct( row.Country, row.Country != null ) 

See also

Total.count aggregate

Total.first

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.

Syntax

Total.first( expr [, filter [, group ]] )

Arguments

Returns

This function returns the first value that appears in the sequence of rows, or null if the data set contains no rows.

Example

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.first( row.TransDate, row.Action == 'Buy' ); 

See also

Total.isBottomNPercent aggregate

Total.max aggregate

Total.min aggregate

Total.isBottomN

This function returns a boolean that indicates if the value of a specified numeric field is one of the lowest n values.

Syntax

Total.isBottomN( expr, n, [, filter [, group ]] )

Arguments

Returns

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.

Example

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.

See also

Total.isBottomNPercent

Total.isTopN

Total.isTopNPercent

Total.isBottomNPercent

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.

Syntax

Total.isBottomNPercent( expr, n, [, filter [, group ]] )

Arguments

Returns

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.

Example

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.

See also

Total.isBottomN

Total.isTopN

Total.isTopNPercent

Total.isTopN

This function returns a boolean that indicates if the value of a specified numeric field is one of the highest n values.

Syntax

Total.isTopN( expr, n, [, filter [, group ]] )

Arguments

Returns

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.

Example

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.

See also

Total.isTopNPercent

Total.isBottomN

Total.isBottomNPercent

Total.isTopNPercent

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.

Syntax

Total.isTopNPercent( expr, n, [, filter [, group ]] )

Arguments

Returns

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.

Example

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.

See also

Total.isTopN

Total.isBottomN

Total.isBottomNPercent

Total.last

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.

Syntax

Total.last( expr [, filter [, group ]] )

Arguments

Returns

This function returns the last value that appears in the sequence of rows, or null if the data set contains no rows.

Example

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.last( row.TransDate, row.Action == 'Sell' ); 

See also

Total.first aggregate

Total.max aggregate

Total.min aggregate

Total.max

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.

Syntax

Total.max( expr [, filter [, group ]] )

Arguments

Returns

This function returns the maximum value of the given expression. It returns null if no rows were available.

Example

The following statement finds the oldest student within a class:

Total.max( row.Age ) 

See also

Total.first aggregate

Total.isBottomNPercent aggregate

Total.min aggregate

Total.median

This aggregate function computes the mathematical median value. Half the values fall above the median, and half below.

Syntax

Total.median( expr [, filter [, group ]] )

Arguments

Returns

This function returns a median value of the given expression. Returns null if no rows were available.

Example

The following statement return the median age of students in a class:

Total.median( row.Age ) 

And, to return the median birthday of students in a class, use the following statement:

Total.median( row.BirthDate ) 

See also

Total.ave aggregate

Total.mode aggregate

Total.movingAve aggregate

Total.stdDev aggregate

Total.variance aggregate

Total.weightedAve aggregate

Total.min

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.

Syntax

Total.min( expr [, filter [, group ]] )

Arguments

Returns

This function returns the minimum value of the given expression. Returns null if no rows were available.

Example

The following statement returns the age of the youngest student within a class:

Total.min( row.Age ) 

See also

Total.first aggregate

Total.isBottomNPercent aggregate

Total.max aggregate

Total.mode

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.

Syntax

Total.mode( expr [, filter [, group ]] )

Arguments

Returns

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.

Example

The following statement returns the most frequently occurring age of students in a class:

Total.mode( row.Age ) 

See also

Total.ave aggregate

Total.median aggregate

Total.movingAve aggregate

Total.stdDev aggregate

Total.variance aggregate

Total.weightedAve aggregate

Total.movingAve

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.

Syntax

Total.movingAve( expr, window [, filter [, group ]] )

Arguments

Returns

This function returns a moving average value of the given expression. Returns null if no rows were available.

Example

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.movingAve( row.price, 5 ); 

See also

Total.ave aggregate

Total.median aggregate

Total.mode aggregate

Total.stdDev aggregate

Total.variance aggregate

Total.weightedAve aggregate

Total.percentile

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.

Syntax

Arguments

Returns

This function returns the value that is at the specified percentile in the set of analyzed values.

Example

The following statement returns the 50th percentile score value:

Total.percentile( row.TestScore, 0.5) 

If the set of test scores in the specified column is 10, 20, 30, 40, and 50, this statement would return 30.

See also

Total.percentRank

Total.percentSum

Total.quartile

Total.percentRank

This function computes the percentage ranking of a value in a specified field.

Syntax

percentRank( expr, filter [, group ]] )

Arguments

Returns

This function returns the percentage ranking of the value in the specified field. The return value ranges from zero to one.

Example

The following statement returns the percent ranking of a score among all the scores in a column:

Total.percentRank( row.Scores ) 

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.

See also

Total.percentile

Total.percentSum

Total.quartile

Total.percentSum

This function computes t.

Syntax

percentSum( expr [, filter [, group ]] )

Arguments

Returns

This function returns the percentage of the contribution of this value to the sum of all the values in the range.

Example

The following statement returns the percentage of contribution of this order amount to the sum of all order amounts:

Total.percentSum( row.OrderAmt ) 

See also

Total.percentile

Total.percentRank

Total.quartile

Total.quartile

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.

Syntax

quartile( expr, quartile, [, filter [, group ]] )

Arguments

Returns

This function returns the value within the specified set that falls at the specified quartile.

Example

The following statement returns the order amount that for which 75% are smaller and 25% are greater:

Total.quartile( row.OrderAmt, 3) 

See also

Total.percentile

Total.percentRank

Total.percentSum

Total.rank

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.

Syntax

rank( expr [, filter [, group ]] )

Arguments

Returns

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

Example

The following statement returns the rank of the OrderAmt field among all order amounts in the OrderAmt column.

Total.rank( row.OrderAmt ) 

See also

Total.percentRank

Total.runningCount

This function computes a running count of rows.

Syntax

runningCount( filter [, group ]] )

Arguments

Returns

This function returns the running count of rows.

Example

The following statement returns the running count for a report:

Total.runningCount( ) 

See also

Total.count aggregate

Total.percentRank aggregate

Total.stdDev

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.

Syntax

Total.stdDev( expr [, filter [, group ]] )

Arguments

Returns

This function returns a standard deviation of the given expression. Returns null if no rows were available.

See also

Total.ave aggregate

Total.median aggregate

Total.mode aggregate

Total.movingAve aggregate

Total.variance aggregate

Total.weightedAve aggregate

Total.sum

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.

Syntax

Total.sum( expr [, filter [, group ]] )

Arguments

Returns

The sum of the given expression. Returns zero if no rows were available.

Example

The following statement totals order amounts for a customer:

Total.sum( row.OrderAmt ) 

See also

Total.count aggregate

Total.percentRank aggregate

Total.variance

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.

Syntax

Total.variance( expr [, filter [, group ]] )

Arguments

Returns

This function returns the variance of the given expression. Returns null if no rows were available.

See also

Total.ave aggregate

Total.median aggregate

Total.mode aggregate

Total.movingAve aggregate

Total.stdDev aggregate

Total.weightedAve aggregate

Total.weightedAve

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.

Syntax

Total.weightedAve( expr, weight [, filter [, group ]] )

Arguments

Returns

This function returns the weighted average value of the given expression. Returns null if no rows were available.

Example

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.weightedAve( row.purchasePrice, row.shareCount ) 

See also

Total.ave aggregate

Total.median aggregate

Total.mode aggregate

Total.movingAve aggregate

Total.stdDev aggregate

Total.variance aggregate


(c) Copyright Actuate Corporation 2006

Previous TopicNext Topic