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

Click on the Properties tab and under Main\Actions, look for the button's click actions.

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

Click Script 1 to open the script editor.

In the editor, copy and paste the following JavaScript code:

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;
});

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 2 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

When the visualization is connected to a filter, the request created in the code above will fetch all the data without filtering. To retrieve only the filtered data, the request could be copied from the existing table visualization and modified to change the paging options. The code above could be modified as follows:

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

// Copy the request from the table:
var request = Class.clone(table1.metricSetBindings[0].dataResult.request);
// Set paging to retrieve at most 10000 rows in case there are no filters
request.pagingOptions.rowSequenceSize = 10000;

var def = dataRetrievalService.getData(request);
def.done(function(results) {
    var cellset = results[0].cellset;
}); 

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