CT.6 Accessing Chart Data in Script

You can access Chart data in scripts by referencing the Chart's data or table property. The table object contains the aggregate values displayed on the chart (the same values shown by the 'Show Data' button in a Viewsheet Chart). The table object is accessible as a two-dimensional array, where each column represents a distinct dataset (measure). The first column of the array, table[i][0], contains the X-axis labels. The first row of the array, table[0][i], contains the dataset or measure titles.

For example, consider a chart with two aggregated datasets (measures), as shown below:

 

The table object provides access to these aggregate values as the following array:

 

table[0][0] = 'DayOfWeek(Day)'

table[0][1] = 'Sum(Measure1)'

table[0][2] = 'Sum(Measure2)'

table[1][0] = 'Sun'

table[1][1] = 1

table[1][2] = 4

table[2][0] = 'Mon'

table[2][1] = 2

table[2][2] = 5

etc.

You can index the datasets (columns) numerically, e.g., table[1][2], or by using the dataset name, e.g., table[1]['Sum(Measure2)']). You can also use more complex formula table syntax; see Data in Appendix CR:Chart Script Reference.

In a Viewsheet chart, the data property and table property are identical. In a report chart, the data property represents the chart data prior to grouping and aggregation (i.e., the raw data).

Example: Accessing Chart Data  >>

In this example, add a script to display data values on the chart only if the measure falls below a certain threshold.

1. Create a new report or Viewsheet. (For a Viewsheet, select the 'Sales Explore' Worksheet as the data source.)

2. Add a new Chart element to the report or Viewsheet. (For a Viewsheet, click the 'Edit' button on the Chart to open the Chart Editor.)

3. Bind the 'Category' field of the 'Sales Explore' Worksheet to the chart's X-axis.

4. Bind both 'Total' and 'Quantity Purchased' fields of the 'Sales Explore' Worksheet to the chart's Y-axis. This creates a facet chart with two sets of axes.

5. Resize the chart to show all data.

6. Open the Chart Script Editor:

a. For a report, right-click on the chart and select 'Script' from the context menu. This opens the Script Editor.

b. For a Viewsheet, right-click on the chart and select 'Properties' from the context menu. This opens the 'Chart Properties' dialog box. Select the Script tab

7. Add the following script:

var threshold = 5000;

 

// Step through the rows of chart data with index i

for (var i = 1; i < table.length; i++) {

 

  // Obtain the ith value of 'Category' and 'Quantity'

  var Xvalue = table[i][0];

  var Yvalue = table[i]['Sum(Quantity Purchased)'];

 

  // test the value of Quantity against the threshold

  if(Yvalue < threshold) {

    // Create the label object

    var form = new LabelForm();

 

    // Set the label to appear only on Quantity axes

    form.setMeasure('Sum(Quantity Purchased)')

 

    // Set the label text

    form.setLabel(Yvalue);

 

    // Set the label position and alignment

    form.setValues([Xvalue,Yvalue]);

    form.setAlignmentX(Chart.CENTER_ALIGNMENT);

 

    // Add the label to the graph

    graph.addForm(form)

  }

}

8. Press 'OK' to close the Script Editor.

9. Preview the report or Viewsheet.

Observe that values are shown for groups that have totals falling below the threshold of 5000.

 

 

See Also

Data, in Appendix CR:Chart Script Reference, for additional information.

<< CT.5.5 Bind Chart to JavaScript Array (Report/Viewsheet) © 1996-2013 InetSoft Technology Corporation (v11.5) CT.7 Changing Chart Scaling >>