Expansion Hierarchies
It is common to have multiple levels of row/column headers nested in a hierarchy, e.g., State and City. Formula tables accommodate this structure in the following ways:
• Two or more vertically expanding cells defined on the same row will automatically expand in hierarchy. The order of the hierarchy moves from left to right.
• Two or more horizontally expanding cells defined under the same column will automatically expand in hierarchy. The order of the hierarchy moves from top to bottom.
Walkthrough
In this example, you will create a formula table which uses data from the 'customers' query. The query is executed in the onLoad script and is stored in a variable called 'q':
1. Click the 'New' button in the Style Studio toolbar. This opens the 'New Asset' dialog box.
2. In the left panel, select the 'Report' node. In the right 'Types' panel, select 'Blank Tabular Report' and click 'OK'
3. Expand the Style Studio Toolbox panel. (Select 'Toolbox Pane' from the Style Studio 'Window' menu.)

4. Drag the 'Table' component from the Toolbox panel to the report. This creates a new Table without a binding.
5. Right-click the table and select 'Convert to Formula Table' from the context menu.
6. Right-click on a blank area of the report, and select 'Script' from the context menu. This opens the Script Editor.
7. Add the following script in the onLoad tab.
var q = runQuery('customers');
This script runs the 'customers' query and stores the result set in a variable 'q'.
8. Expand the Style Studio Properties panel. (Select 'Properties Pane' from the Style Studio 'Window' menu.)
9. Select cell[1,0] (second row, first column) and select the Data tab in the Properties panel.
10. In the 'Binding' region of the Data tab, choose the 'Formula' option, and enter the following formula:
toList(q['state'])
11. Check the 'Expand Cell' box and select the 'Vertical' radio button. This sets the cell to expand (fill) vertically.
12. Set the 'Cell Name' field in the 'Cell' panel to be “st”. Click the 'Apply' button.
13. Select cell[1,1] (second row, second column).
14. In the 'Binding' region of the Data tab, choose the 'Formula' option, and enter the following formula
toList(q['city'])
15. Check the 'Expand Cell' box and select the 'Vertical' radio button. This sets the cell to expand (fill) vertically.
16. Set the 'Cell Name' field in the 'Cell' panel to be “ct”. Click the 'Apply' button.

17. Preview the report.

In the preview, all cities are shown for each state, not just the cities actually belonging to the given state. See Formula Tables in Report Scripting and the Sales Revenue Ledger (formula_table1.srt) example for an illustration of how to filter the field so that only cities within the given state are listed.
Note that the two cells expand in a hierarchy (i.e., for each state in the 'State' field, the table displays every city from the 'City' field). The cell to the left is the parent (State) and the cell to the right is the child (City). These state and city cells expand in hierarchy automatically because they share the same row. By default, two vertically expanding cells on different rows will expand independently of each other. To make them expand in a hierarchy you need to explicitly group them together. (The same applies to two horizontally expanding cells on different columns).
18. Right-click cell[1,0] (second row, first column), and select 'Row' > 'Append Row' from the context menu. This adds a new row beneath the two existing rows.
19. Drag the exiting formula from cell[1,1] to cell[2,0] (third row first column).

20. Preview the report.

Notice how the two cells expand independently. To force them to expand in a hierarchy, you must explicitly group them together.
21. Select cell[2,0]. From the 'Row Group' menu of the 'Cell' panel, select 'st'. This specifies that the 'City' field should expand in hierarchy with the 'State' field.

22. Preview the table and notice how the cells expand in a hierarchy.

| << Cell Expansion | © 1996-2013 InetSoft Technology Corporation (v11.5) | Defining Formulas by Referencing Cells >> |