InetSoft Product Information: Formula Tables

Formula tables are used to create real-time, spreadsheet-like reports with highly specific or complex layouts. These tables can be used to implement the kind of data grouping and aggregation which is commonly required in accounting and financial applications.

Formula tables bridge the gap between spreadsheet applications (like Excel) and traditional reporting tools. In a spreadsheet, you usually 'fill' a column with data and then define summary formulas that reference different cells.

A formula table is similar. Rather than bind the table to a query as a whole, you extract parts of a query result set and then dynamically fill the header rows and header columns of the table. You can then reference these 'filled-in' cells to perform statistical calculations in formulas.

The next sections discuss some of the most commonly used formulas, and methods for referencing data from query results and table cells.

Extracting Data from a Query

To populate a formula table with data from a query result set, execute the query using the runQuery() method and save the result in a variable. 

var q = runQuery('customers');

You can then selectively extract parts of that query result set (see Referencing Query Data) and fill the cells of your formula table.

Extracting Data from a Report Element

To extract data from another report element (Table/Section) use the element's table or data attributes:

marketing management dashboard

var q = Table1.table['column_name']

Referencing Query Data

Once you have executed the query (see Extracting Data from a Query), you can access specific ranges of the query result set. The data-referencing syntax allows you to also group and filter the results, and to create expressions.

Referencing a Query Column

You can reference all cells of a field (column) in the result set by using the field name: 

q['state'];

The following example illustrates this approach.

Consider the sample 'customers' query. In this example, you will extract all the values under the 'state' column and use them to populate a formula table. Follow the steps below.

1. Create a new report. Add a table with two rows and four columns.

2. Run the 'customers' query in the onLoad script. (See Extracting Data from a Query for more details.) var q = runQuery('customers')

3. Select cell[1,0]. Right-click on the cell and select 'Format' from the context menu. This opens the Format panel at the bottom.

4. Click the Data tab in the 'Format' panel. Select the 'Formula' button in the 'Binding' section, and enter the following formula for cell[1,0]: q['state'] This will extract the entire 'state' column from the 'customers' query to populate the table.

5. In the 'Expansion' region of the Data tab, select 'Expand Cell' and choose 'Vertical'. (This causes the extracted data to fill down vertically.)

6. Preview the report and see how the table is populated with all of the records from the 'state' column.

Previous: Data Tables