Defining Formulas by Referencing Cells
Once you have defined row/column headers, you can define aggregation formulas. These formulas summarize (sum, count, average, etc.) data returned by the data source for a given row header element and/or column header element. For example, you can count the number of customers within each city and each state.
Walkthrough
Consider the table you designed in the Expansion Hierarchies section, which contains a listing of states and cities. You will now add formulas to count the number of customers within each city and each state. To do this, you will use cell referencing to aggregate data for a specific city and state.
Note: Do NOT set the cells to expand. The formula will automatically be calculated for each cell of the expanding “st” and “ct” cells.
1. Add the following formulas to cell[1,1] (second row, second column) and cell[2,1] (third row, second column), respectively
cell[1,1]: count(q['customer_id@state:$st'])
cell[2,1]: count(q['customer_id@city:$ct'])

For more details on extracting data from a query result set, see Formula Tables in Report Scripting.
The formula q['customer_id@state:$st'] returns an array of all the 'customer_id' records where the corresponding 'state' record is equal to '$st' ('$st' refers to the current value of the expanding cell 'st'). The count() formula counts the number of elements in this array.
2. Preview the report and notice the counts. You can verify them by inspecting the output of the base query.

| << Expansion Hierarchies | © 1996-2013 InetSoft Technology Corporation (v11.4) | Page Break on Group in Formula Tables >> |