Read data from a visualization by script

Contents[Hide]

1. Overview

Dundas BI visualizations request a metric set's data from the server for display purposes only, and may not immediately include the entire result, for example if that data is currently scrolled out of view. The JavaScript API can be used to issue a request for a metric set's data for your own purposes.

2. Set up the visualization

The example in this article is using the SQL Adventure Works 2012 database. The data for any visualization is always retrieved using metric sets in the same way, so the same script is applicable for all visualization types.

2.1. Table visualization

Use the data cube or data connector to create a table on the dashboard canvas with the required hierarchies and measures.

Set up a table visualization
Set up a table visualization

From the toolbar, select a Button component.

2.2. Retrieve data

In the Properties window under Actions, look for the button's Click actions.

Click the + button to add a script action for retrieving data.

Click Script 1 to open the script editor.

In the editor, use JavaScript like the following:

var dataRetrievalService = this.getService("DataRetrievalService");

// Create a Request object with our own paging settings:
var request = new dundas.data.Request({ objectId: table1.metricSetBindings[0].metricSetId });
request.pagingOptions.pagingKind = dundas.data.PagingKind.SEQUENCE;
request.pagingOptions.rowSequenceSize = 100;

// Request the data:
var def = dataRetrievalService.getData(request);

def.done(function(results) {
    // The request was successful:
    var cellset = results[0].cellset;
    debugger; // (check the result in the browser's debugging tools, then replace this line)
});

Once the cellset has been retrieved, the data contained in its tuples and cells can be accessed. See the dundas.data.DataCellSet class in the JavaScript API reference for details. The cellset for the example above contains 100 rows:

The first 100 tuples retrieved
The first 100 tuples retrieved

The dundas.data.Request class has options that can be customized. When pagingKind is set to dundas.data.PagingKind.NONE, all the rows in the table are retrieved:

All the rows are retrieved
All the rows are retrieved

Note
If the pagingKind is set to NONE and the data volume is too much, the browser may not be able to handle the amount of data requested. In this case, consider if a server-side plugin should be used instead.

The measure values can be accessed by looping through the nested cells array, e.g., cellset.cells[columnIndex][rowIndex]. As there are two measures in the table above, cells contains two arrays of size 100 (as specified in the paging options).

The first 100 column cells
The first 100 column cells

2.3. Retrieve filtered data

The request created in the code above will fetch all the data without filtering. To retrieve only data filtered like some visualization on the dashboard, parameter values should be included in the request.

Parameter values can be created in script as done in other articles, or parameter values could be copied from a visualization displaying the same metric set.

Assuming the same metric set is also dragged onto the dashboard, the code above could be modified as follows to refer to it (i.e., table1):

var dataRetrievalService = this.getService("DataRetrievalService");

// Create a Request object with our own paging settings:
var request = new dundas.data.Request({ objectId: table1.metricSetBindings[0].metricSetId });
request.pagingOptions.pagingKind = dundas.data.PagingKind.SEQUENCE;
request.pagingOptions.rowSequenceSize = 100;

// Get the request from the visualization:
var vizRequest = table1.metricSetBindings[0].dataResult.request;

// Copy the parameter values, which will have matching parameterIds if for the same metric set:
vizRequest.parameterValues.forEach(function (parameterValue) {
    request.parameterValues.push(Class.clone(parameterValue));
});

var def = dataRetrievalService.getData(request);
def.done(function(results) {
    var cellset = results[0].cellset;
    debugger; // (check the result in the browser's debugging tools, then replace this line)
});

This cellset will contain only the rows and columns that match the filtering criteria. For example, if a filter is applied on the ProductID in the sample table:

Connect a filter to the table visualization
Connect a filter to the table visualization

cellset.cells contains only 9743 rows instead of the total available 12173 rows.

cellset.cells returns only the filtered data points
cellset.cells returns only the filtered data points

3. See Also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri