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

In this example, you will create a formula table, and 'fill' it with dynamically-generated row and column headers extracted from a query.

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 'Table' > '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('Order details');

This script runs the 'Order details' query and stores the result set in a variable 'q'.

8. Click the 'Save and Close' button.

 

9. Expand the Style Studio Properties panel. (Select 'Properties Pane' from the Style Studio 'Window' menu.)

10. Select cell[1,0] (second row, first column) and select the Data tab in the Properties panel.

11. In the 'Binding' region of the Data tab, choose the 'Formula' option, and enter the following formula:

q['Company']

12. Check the 'Expand Cell' box and select the 'Vertical' radio button. This sets the cell to expand (fill) vertically.

 

13. Click the 'Apply' button.

14. Preview the report and notice how the cell fills out all the values of the 'Company' column from the query result set. Notice how each Company name repeats multiple times.

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, see Formula Tables in Report Scripting.

See toList in Report Scripting for more information.

15. To retrieve a distinct set of companies, use the toList() function, which returns an array of unique values from a given input array. Change the previous formula to the following:

toList(q['Company'])

16. Preview the report and notice the unique listing of companies.

17. Select cell[0,1] (first row, second column).

18. In the 'Binding' region of the Data tab, choose the 'Formula' option. Add the following formula:

toList(q['Product'])

19. Check the 'Expand Cell' box and select the 'Horizontal' radio button. This sets this cell to expand (fill) horizontally.

20. Click the 'Apply' button.

21. Double-click to open the 'Table Properties' dialog box. Click the Table tab, and select the 'Fit Content' radio button. Click 'OK'.

22. Preview the report, and notice the horizontal and vertical expanding rows and columns.

 

<< 7.11.3 Creating a Formula Table Using Script (Advanced) © 1996-2013 InetSoft Technology Corporation (v11.5) Expansion Hierarchies >>