Read data from a Visualization by script

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.

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:

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

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.