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, 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.
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. Create a new report. Add a table with four rows and three columns.
2. Run the 'customers' query in the onLoad script. (See Extracting Data from a Query for more details.) var q = runQuery('cutomers')
3. Select cell[1,0] in the table. 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.
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.
5. Select cell[1,1] in the table.
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.
q['state'] q['city']
6. 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'.
7. Change the formula in cell[1,1] to 'q['city@state:$st']'.
8. 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.)
Referencing Cells in Summary Formulas
Consider the formula table from the previous example in Referencing a Cell for Column Filtering. It consists of a two-level row header listing 'States' and 'Cities within a State'.
In the following example, you will add a formula to count the number of customers within each city.
1. Add the following formula to cell[1,2]:
count(q['customer_id@state:$st;city:$ct'])
This counts all the customers within the given city and state. The table should now appear as shown below.
q['state'] q['city@state:$st'] count(q['customer_id@state:$st;city:$ct'])
2. Preview the report and note the output.