InetSoft Product Documentation: Displaying Hybrid Data from Multiple Queries

Previous sections have presented a variety of techniques for displaying data within tables, including table data binding, crosstab tables, and freehand tables. However, these methods may not be flexible enough to create very specific layouts, display hybrid data from multiple queries, or address other advanced display needs. For such cases, you can gain greater flexibility by using a Formula Table.

Note: To use Formula Tables effectively, you should possess a working knowledge of JavaScript.

Characteristics of Formula Tables

A formula table is a regular table with no data binding. Formula tables bridge the gap between spreadsheets applications (like Excel) and traditional reporting tools. In a spreadsheet, we usually fill a column with data, and then define summary formulas which reference different cells. A formula table is similar. It is not explicitly bound to a query; instead we have to extract parts of a query result set and fill the header rows and header columns of the table dynamically. We can then reference these filled cells in formulas to perform all our statistical calculations. Formula tables can thus be used to create real-time, spreadsheet-like reports with very specific and complex layouts, grouping of data, and aggregation which are commonly required in accounting and financial applications.

Fundamental Concepts of Formula Tables

The fundamental approach to creating formula tables is as follows:

1. Fill out row and/or column headers. (Cell Expansion)
2. If there are multiple level row/column headers, set up an expansion hierarchy. (Expansion Hierarchies)
3. Define aggregation formulas by referencing the header cells or other summarized cells. (Defining Formulas by Referencing Cells).

Cell Expansion

If you assign a formula to a cell of a table, and this formula returns an array of values, you can specify that the cell should expand in the horizontal/vertical direction to ‘fill’ the table with the values of the array.

Walkthrough

We will illustrate in detail how to create a formula table, and ‘fill’ it with dynamically-generated row and column headers extracted from a query.

1. Open the Designer and click the ‘New’ button. In the ‘Create Report’ dialog box, select ‘Blank Tabular Report’ and click ‘OK’
2. Click the ‘Table’ button to add a table to the report. (A formula table is a regular table with no data binding).
3. Right-click on a blank area outside the table. Select ‘Script’. This will display the Scripting window. Add the following script in the onLoad tab. This script runs the ‘Order details’ query and stores the result set in a variable ‘q’.

var q = runQuery('Order details');

4. Click the ‘Save and Close’ button.
5. Select cell[1,0], then right-click the cell, and select ‘Format’ from the context menu. In Designer’s bottom panel, select Data tab and choose the ‘Formula’ option. Enter the following formula: q['Company']
6. Check the ‘Expand Cell’ box and select the ‘Vertical’ radio button. This sets the cell to expand (fill) vertically.
7. Preview the report and notice how the cell fills out all the values of the ‘Company’ column from the query result set.

Note: q['Company'] will return an array of records under the 'Company' column of the query result set. For more information on how to extract and manipulate data from a query result set, refer to the Formula Tables section of the Report Scripting Guide.

8. Notice how the Company name repeats. To retrieve a distinct set of companies, use the toList() function, which returns an array of distinct values from a given input array. Change the formula to toList(q['Company']). Preview the report and notice the unique listing of companies.
9. Now select cell [0,1], right-click the cell, and select ‘Format’ from the context menu. Select the Data tab in the bottom panel, and choose the ‘Formula’ option. Add the formula toList(q['Product']).
10. Check the ‘Expand Cell’ box and select the ‘Horizontal’ radio button. This sets this cell to expand (fill) horizontally.
11. To format the table, select the table, and double-click to open the ‘Table Properties’ dialog box. On the Table tab select the ‘Fit Content’ radio button, then click ‘OK’.
12. Preview the report, and notice the horizontal and vertical expanding rows, and how we have filled our formula table with the row/column headers.

Previous: Non-Flow Page Areas in a Report

Next: Report Viewing