Referencing a Cell with Relative Parent Group Reference

Relative cell referencing is used primarily when comparing different summary cells with respect to their header cell. For example, you might want find the difference between the total sales for the current year and the previous year.

The syntax for relative cell referencing is as follows:

$cellName['grpName:+/-relative_index']

 

e.g., $sales['state:-1']

      $sales['yr:+1']

Here, $cellName is the name of the cell/column containing the value(s) to be compared, and grpName is name of the cell/column that indexes the different values.

Walkthrough

Consider a formula table based on the 'All Sales' query. In this example, you will find the difference in sales between successive fiscal years. 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] in the table (second row, second column).

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:-1']

This formula uses relative cell referencing to calculate the differ­ence between the total revenue (computed in the column named 'tot') of the current year and the previous year. The table should appear as shown below:

 

15. Preview the table:

 

<< Group Numbering © 1996-2013 InetSoft Technology Corporation (v11.4) Referencing a Cell with Absolute Parent Group Reference >>