Referencing a Cell for Column Filtering

The Referencing Query Data section explained how to extract and filter records from a specified column of a query result set. All of the examples in that section used hard-coded values as the filtering parameters.

To perform dynamic filtering, you can use cell references as the filtering parameters. This is particularly useful when the table has multiple levels of row/column headers, and you wish to filter the sub-level based on the parent level.

Walkthrough

In this example, you will create a formula table (based on the 'customers' query) with a two-level row header consisting of 'State' and 'Cities within the State'.

1. Click the 'New' button in the Style Studio toolbar. This opens the 'New Asset' dialog box.

2. In the left panel, select 'Report'. In the right 'Types' panel, select 'Blank Tabular Report' and click 'OK'.

3. Expand the Style Studio Toolbox panel, and drag a Table element into the report. This creates a new blank Table.

4. Right-click the table, and select 'Table' > 'Convert to Formula Table' from the context menu.

5. Right-click the table, and select 'Table' > 'Insert Rows/Columns' from the context menu.

6. Insert an additional column so that the table has three columns.

7. Repeat the above step, and add two additional rows so that the table has four rows. The table should now have four rows and three columns

8. Click away from the Table to deselect it.

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

10. Select the 'onLoad' button in the Script tab, and enter the following script:

var q = runQuery('customers')

This executes the 'customers' query in the onLoad script. (See Extracting Data from a Query for more details.)

11. Click cell[1,0] in the Table (second row, first column) to select it.

12. Select the Data tab in the Properties panel. Follow the steps below:

a. In the 'Binding' panel of the Data tab, select the 'Formula' option. Enter the formula 'q['state']' for cell[1,0].

b. In the 'Expansion' panel of the Data tab, select 'Expand Cell' and choose 'Vertical'.

c. In the 'Cell' panel of the Data tab, set the 'Cell Name' to 'st'.

The Data tab of the 'Format' panel should still be open.

13. Select cell[1,1] in the table (second row, second column).

a. In the 'Binding' panel of the Data tab, select the 'Formula' option. Enter the formula q['city'] for cell[1,1].

b. In the 'Expansion' panel of the Data tab, select 'Expand Cell' and choose 'Vertical'.

c. In the 'Cell' panel of the Data tab, set the 'Cell Name' to 'ct'.

The table should appear as below.

 

14. Preview the report.

 

Notice that all of the cities are listed for each state, not just the cities within the corresponding state. In most cases, it is desirable to see only those cities within the corresponding state. To filter out the cities based on the state, include a field-filtering condition with a reference to the cell 'st'.

Note: When making comparisons with 'null', use the syntax “$st['.']” to ref­erence the cell value.

15. Change the formula in cell[1,1] to 'q['city@state:$st']'.

16. Preview the report.

 

Notice that the table now lists only those that correspond to the given state. (This example continues in the next section, Referencing Cells in Summary Formulas.)

<< 3.7.4 Cell Referencing © 1996-2013 InetSoft Technology Corporation (v11.4) Referencing Cells in Summary Formulas >>