InetSoft Product Information: 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:"2002"'] - specify numeric values in quotes

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 2002. Follow the steps below:

1. Create a new report, and add a table with three rows and three columns.

2. Add the following script to the report's onLoad Handler to store the results of the 'All Sales' query:

var q = runQuery('All Sales'); 

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. The second toList argument groups the returned dates by year.

a. Select the Data tab. In the 'Binding' panel, select the 'Formula' option, and enter 'toList(q['Order Date'],'date=year')' as the formula.

b. In the 'Cell' panel of the Data tab, enter 'yr' for the 'Cell Name' of cell[1,0].

c. In the 'Expansion' panel of the Data tab, select 'Expand Cell' to set cell[1,0] to expand 'Vertical'.

4. Select cell[1,1] 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].

5. Select cell[1,2]. In the 'Binding' panel of the Data tab, select the 'Formula' option, and enter the following formula.

$tot - $tot['yr:"2002"'] 

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 2002.The table should appear as shown below:

toList(q['Order Date'], 'date=year')	sum(q['Total@= year(field["Order Date"]):$yr'])	$tot - $tot['yr:"2002"']

6. Preview the table.

Referencing a Cell with Parent Group Reference as an Expression

The referenced group can also be specified with a JavaScript expression. The JavaScript expression should be prefixed to the expression string with an '=', as shown below:

$company['state:=iif($type == "T", $state, $province)'] 

The syntax of the iif function used above is CALC.iif(logical_test, value_if_true, value_if_false).

Previous: Report Scripting - Group Numbering

Next: Formula Table Functions