| #1 Ranking: Read how InetSoft was rated #1 for user adoption in G2's user survey-based index |
|
Read More |
You can access the value in the current row of another column by using the field keyword. For example, field['col_name'] refers to the value in the current row of a column named “col_name.”
This example continues from Accessing Data in the Current Cell. It will set the color of the 'Name' column based on the value of the 'Price' column:
1. Select a cell in the 'Name' column. Right-click and choose 'Format'.
2. Select the Color tab. From the 'Text Color' menu select the 'Expression' option, and click the 'Edit' button. This opens the Formula Editor.
3. In the Formula Editor, enter the following expression: if (field['Price'] > 1000) { [255,0,0]; } else { [0,0,255]; }
4. Click 'OK' to exit the Formula Editor, and then click 'OK' to exit the 'Format' dialog box.
The values in the 'Name' column are now highlighted based on the corresponding values in the 'Price' column.
To access any cell in a table by relative reference, use the indexing expression table[rowIx][colIx] or data[rowIx][colIx].
Predefined variables row and col allow you to refer to the row and column of the current cell, that is, the cell whose format is being modified. You can use these variables to construct the desired offsets from the current cell.
For example:
• table[row][col]: Value in current cell (same as value keyword).
• table[row][col-1]: Value in current row, one column to left.
• table[row-1][col]: Value in current column, one row above.
• table[row+1][col]: Value in current column, one row below.
The data and table constructs yield the same results for tables, but may produce different results when used in charts/crosstab tables. The data syntax references the data prior to any summarization, while the table syntax references the tabular data as displayed.
It highlights every price that is less than the price immediately above it in the table, and uses the row and col variables to create relative references:
1. Select any data cell under the 'Price' column. Right-click and choose the 'Format' option.
2. Select the Color tab. From the 'Fill Color' menu select the 'Expression' option, and click the 'Edit' button. This opens the Formula Editor.
3. In the Formula Editor, enter the following script: if (value < table[row-1][col]){ [255,255,0]; }
4. Click 'OK' to exit the Formula Editor, and then click 'OK' to exit the 'Format' dialog box.
The cells in which the value is less than the previous row's price are now highlighted in yellow.