Referencing a Cell with Absolute Parent Group Reference
You can also use the value of the parent group to compare summary cells. To refer to a summary cell in another header group, use the absolute value of the header group, as shown below:
$cellName['grpName:absolute_value']
e.g., $sales['state:NJ']
$sales['yr:"2008"'] (specify numeric values in quotes)
Walkthrough
Consider a formula table based on the 'All Sales' query. In this example, you will find the relative sales for each year compared to the fixed year 2008. Follow the steps below:
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 an additional row so that the table has three rows. The table should now have three 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('All Sales');
This executes the 'All Sales' 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:
The second toList argument groups the returned dates by year.
a. In the 'Binding' panel, select the 'Formula' option, and enter 'toList(q['Order Date'],'date=year')' as the formula.
b. In the 'Expansion' panel of the Data tab, select 'Expand Cell' to set cell[1,0] to expand 'Vertical'.
c. In the 'Cell' panel of the Data tab, enter 'yr' for the 'Cell Name' of cell[1,0].
The Data tab of the 'Format' panel should still be open.
13. Select cell[1,1] (second row, second column) in the table.
a. Select the Data tab. In the 'Binding' panel, select the 'Formula' option, and enter the following formula:
sum(q['Total@=year(field["Order Date"]):$yr'])
In words, this says: “For each year in column 'yr', find the 'Order Dates' falling within that year, and sum the 'Totals' for those order dates.” Effectively, this calculates the total revenue generated for a given fiscal year.
b. In the 'Cell' panel of the Data tab, enter 'tot' for the 'Cell Name' of cell[1,1].
14. Select cell[1,2] (second row, third column). In the 'Binding' panel of the Data tab, select the 'Formula' option, and enter the following formula.
$tot - $tot['yr:"2008"']
This formula uses absolute cell referencing to calculate the difference between the total revenue (computed in the column named 'tot') of the current year and the year 2008.The table should appear as shown below:

15. Preview the table:

See Also
Referencing a Cell with Relative Parent Group Reference, to compare based on relative location.
| << Referencing a Cell with Relative Parent Group Reference | © 1996-2013 InetSoft Technology Corporation (v11.4) | Referencing a Cell with Parent Group Reference as an Expression >> |