InetSoft Product Information: Chart Control - Visual Properties

You can control the visual properties (font, alignment, background, etc.) of expanding cells, rows, and columns in the formula table script for your chart. There are three general steps:

1. Call functions to modify the visual properties of the “pre-expansion” table.

2. Call the “expandCalcTable()” function to expand formula table.

3. Call functions to modify the visual properties of the “post-expansion” table.

Any script that precedes the “expandCalcTable()” command applies to the table prior to expansion. Any script that follows the “expandCalcTable()” command applies to the expanded table. To use 'expandCalcTable()', make sure to disable automatic table expansion. (It is disabled by default for new reports.)

To disable automatic table expansion, select 'Report Properties' from the 'File' menu. In the 'Report Properties' dialog box, deselect 'Expand calc table automatically on script reference'.

information dashboard design software

dashboard demo
View a 2-minute demonstration of InetSoft's easy, agile, and robust BI software.

Walkthrough: Formula Table Expansion

Construct a formula table based on the 'customers' query, with a multilevel header comprising 'State' and 'City'.

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

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

var q = runQuery('customers');

3. Select cell[1,0]. Right-click and select 'Format' from the context menu. This opens the 'Format' tab at the bottom. Click the Data tab for cell[1,0].

a. In the 'Binding' panel of the Data tab, select the 'Formula' option. Enter the formula 'toList(q['state'])'.

b. In the 'Expansion' panel of the Data tab, set 'Expand Cell' to expand 'Vertical'.

c. In the 'Cell' panel of the Data tab, set the 'Cell Name' to be “st”. The Data tab of the 'Format' panel should still be open.

4. Select cell[2,1].

a. In the 'Binding' panel of the Data tab, select the 'Formula' option. Enter the formula 'toList(q['city@state:$st'])'.

b. In the 'Expansion' panel of the Data tab, set 'Expand Cell' to expand 'Vertical'.

c. In the 'Cell' panel of the Data tab, set the 'Cell Name' to be “ct” and set the 'Row Group' to 'st'.

The table should appear as shown below.

 

 

toList(q['state'])

 

 

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

 

 

Pre-Expansion Script

In this example, you will format the 'State' rows with a red background color, and format the 'City' cells with a yellow background color. It is easiest to color these cells using pre-expansion script, because the formatting that you add to pre-expansion rows and cells is iterated for all corresponding rows and cells in the post-expansion table. Thus, the automatic table expansion takes the place of the Iteration Statements that you would otherwise need to write. To add the pre-expansion script, follow the steps below:

1. Select any cell of the table, right-click, and select 'Script' from the context menu. This opens the Script Editor to edit the element script.

2. Add the following script to the table element. 

 rowBackground[1] = [255,0,0];
 cellBackground[2][1] = [255,255,0];
 expandCalcTable();

The first line of the script specifies a red background for the second row of the pre-expansion table (the row containing 'State'). The next line of the script specifies a yellow background for the cell in the third row, second column of the pre-expansion table (the row containing 'City'). The third line of the script initiates table expansion, which propagates the specified formatting to all corresponding cells in the expanded table.

3. Preview the result.

Post-Expansion Script

In this example, you will assign every fifth row of the table a thick border. This formatting should be applied to the post-expansion table, so you will place the code for this after the expandCalcTable() function.

1. Select any cell of the table, right-click, and select 'Script' from the context menu. This opens the Script Editor to edit the element script.

2. Add a “for” loop after the expandCalcTable() function. The full script should appear as follows: 

 rowBackground[1] = [255,0,0];
 cellBackground[2][1] = [255,255,0];
 expandCalcTable();
   for (i=4; i<table.length; i+=5) {
    rowBorder[i] = StyleReport.THICK_LINE;
	 }

3. Preview the result.

Read why choosing InetSoft's cloud-flexible BI provides advantages over other BI options.

Previous: Formula Table Functions