Read data from a Visualization by script

Contents[Hide]

1. Overview

Dundas BI allows its users to integrate data from various sources and visualize them on a single data control. The user might want to retrieve this collaborated data to send to an external application or use it for reporting and analysis purposes. A call to the server is made to be able to retreive all or any data from the entire data result, including data scrolled out of view or otherwise left out because a particular visualization is not using it. This article shows how to retrieve data from a visualization via a script using the Dundas BI Javascript API.

2. Set Up the Data Visualization

The example in this article is using the SQL Adventure Works 2012 database. The following example is to be used as a reference and would need to be amended for production.

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. The table can be displayed with row headers or as a flat structure.

Set up a table visualization
Set up a table visualization

From the toolbar on the top, select a Button component.

2.2. Retrieve Data

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

Click the plus sign button to add an empty script for handling the data retrieval action.

Click Script 1 to open the script editor.

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

//A global variable will save the unpaged cellset data
//global variable is needed if you would like to access this data from any new event on a dashboard
//If you need the data only in the one event, then, create a local variable
window.Cellset = [];

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

//set pagingKind to "None" to retrieve all members instead of first 100 ones
//and remove the rowSequenceSize option.
var pagingOption = new dundas.data.ResultPaging({
    pagingKind: "Sequence" 
    rowSequenceSize: 100
});

//Create Request object
var request = new dundas.data.Request({
    objectId: table1.metricSetBindings[0].metricSetId,
    pagingOptions: pagingOption,
    viewId: myView.id
});
//Make the primary call to request data from the server

var def = dataRetrievalService.getData(request);

//Resolve the promise object when the data is returned by the server.
//If successful an array is returned to the Cellset.
def.done(function(dataResult) {
    CellSet = dataResult[0].cellset;
});

Note
If the pagingKind is set to None and the data volume is too much, the browser won't be able to handle the amount of data requested. This can cause slow processing times and sending the data over to an external application can be difficult. In this case, a server-side plugin can be created to retrieve, process and send the data to the external application."

Once the data has been retrieved in the CellSet, the tuples can be accessed using the following code snippet:

CellSet.rows.forEach( function (row) { //your code }

CellSet.rows returns the first 100 tuples:

The first 100 tuples retrieved
The first 100 tuples retrieved

When the paging Kind is set to None, all the rows in the table are retrieved:

All the rows are retrieved
All the rows are retrieved

The columns or the measure values can be accessed by looping through the array CellSet.cells[<columnIndex>][<RowIndex>].

As there are 2 measures in the table above, the CellSet.cells returns 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 the table displayed before the filtering was done. To retrieve only the filtered data from a visualization, the request can be copied from the existing table and modified to change the paging options.

A modified version of the code above will look like this:

//A global variable will save the unpaged cellset data
//A global variable is needed if you would like to access this data from any new event on a dashboard
//If you need the data only in the one event, then, please, create a local variable
window.CellSet = [];

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

//Copy request from the existing table
var request = Class.clone(table1.metricSetBindings[0].dataResult.request);
//Set paging to retrieve at max 10000 rows in case there's no filters
request.pagingOptions.pagingKind = "None";
//request.pagingOptions.rowSequenceSize = 100;

var def = dataRetrievalService.getData(request);

//Resolve the promise object when the data is returned by the server.
//If successful an array is returned to the Cellset.
def.done(function(dataResult) {
    CellSet = dataResult[0].cellset;
}); 

The CellSet will return 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 returns only 9743 data points instead of the total 12173 data points.

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

The data from any visualization is always retrieved in the same format. So, the same script is applicable for all the visualization types except that the correct adapter has to be fetched to build a request. 

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