Setting Group Numbering Using Report Scripting

To obtain the sequence number of an element within an expanding cell, use the '#' qualifier. (This is useful when you wish to add numbering to row/column headers.)

For example, consider the formula table from the example in Referencing Cells in Summary Formulas. You will now add numbering for the expanding row header cell 'st'.

1. Select any cell in the first column. Right-click the cell and select 'Column' > 'Insert Column' from the context menu. This adds a new column to the left.

2. Right-click cell[1,0] of the newly inserted column, and select 'Format' from the context menu. This opens the 'Format' panel at bottom. Do not specify any cell expansion. Note that numbering starts with 0, hence the '+1'.

3. Select the Data tab of the 'Format' panel. In the 'Binding' panel, select the 'Formula' option, and add the formula '$st['#'] + 1'.

4. Select the Option tab. Enable 'Merge expanded cells', and select 'st' from the 'Row Group' menu.

dashboard software vendor example 
 
Register for More Info
Request a Personalized Demo
#1 Ranking: Read how InetSoft was rated #1 for user adoption in G2's user survey-based index Read More

5. Select cell[1,1] in 'State' column. Under the Data tab, change the formula to 'toList(q['state'])', which will show only unique values.

6. Under the Option tab, select 'Merge expanded cells', and select 'st' from the 'Row Group' menu.

7. Select cell[1,2] in the 'City' column. Under the Data tab, change the formula to 'toList(q['city@state:$st'])', which will show only unique values.

8. Select cell[1,3] in the 'Count' column. Under the Option tab, select 'Merge expanded cells', and select 'ct' from the 'Row Group' menu. The table should appear as shown to the right:

$st['#'] + 1 toList (q['state']) toList (q['city@state:$st']) count(q['customer_id@ state:$st;city:$ct'])

 

 

 

 

$st['#'] + 1

toList
(q['state'])

toList
(q['city@state:$st'])

count(q['customer_id@

state:$st;city:$ct'])

 

 

 

 

 

 

 

 

9. Preview the report and notice the numbering of the groups.

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.

Consider a formula table based on the 'All Sales' query. In this example, you wish to find the difference in sales between successive fiscal years. 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 argu­ment 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'. The Data tab of the 'Format' panel should still be open.

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:-1'] This formula uses relative cell referencing to calculate the difference 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: toList(q['Order Date'], 'date=year') sum(q['Total@= year(field["Order Date"]):$yr']) $tot - $tot['yr:-1']

6. Preview the table:

 

 

 

toList(q['Order Date'], 'date=year')

sum(q['Total@=
year(field["Order Date"]):$yr'])

 

$tot -
$tot['yr:-1']

 

 

 
Previous: Relative Cell Referencing