Use Result Set APIs

The result set API in SAP Analytics Cloud, analytics designer lets you get a result set based on an input data selection in a table or chart, so that you can traverse and get each data cell in the result set.

Before the result set API is introduced, you can retrieve individual data cells using DataSource.getData(). However, it can't retrieve all members for a dimension in a specific result set.

In a result set API, the input data selection specifies all or an arbitrary subset of the initial data set, for example:

Sample Code
//A selection that retrieves values of "sales", "profit" if "maritial_status" is in "M" or "S".
 {"marital_status": ["M", "S"], "(MEASURES_DIMENSION)": ["sales", "profit"]}

If the input data selection is left empty, all values of the initial data set will be retrieved.

For a chart with multiple measures per data point, for example, a bubble or scatterplot chart, the result set API will return all available measures of each data point as different records. And if the source data has a hierarchy, parent member information will also be returned.

Some changes made by end users at runtime will affect data returned by the result set API as well, including:

  • Adding rows or columns to a table

  • Hiding or excluding rows or column in a table.

    Only data of visible rows or columns will be returned.

  • Collapsing parent node in a table.

    All its children won't be returned.

  • Showing total value in a chart

  • Showing parent in a chart

  • Getting the new dimension or measure added by smart grouping and new forecast data in a predictive scenario

  • Adding or removing comments in a chart or table

Note
If data source is set to lazy load, getResultset(), getDataSelections() returns [ ] and getResultMember() returns undefined.
Note

For tables, getDataSource.getData(), getDataSource.getDataSelections(), getDataSource.getResultMember() and getDataSource.getResultSet() APIs only retrieve data from displayed cells, which are by default an upper limit of 500 rows and 60 columns. Therefore, if your table has more data to render, you can set its drill limit to unlimited so that these APIs work on all the data in it. For more information, refer to Use Tables to Visualize Data.

Script APIs

Take table as an example:

Code Syntax
// Returns the result sets according to the selections.
Table_1.getDataSource().getResultSet(selection?: Selection | Selection[] | SelectionContext, offset?: integer, limit?: integer) : Resultset[]

// Returns the context of the data cells.
Table_1.getDataSource().getDataSelections(selection?: Selection | Selection[] | SelectionContext, offset?: integer, limit?: integer) : Selection[]

//Returns the result member by dimension ID and selection. This works for data cells and header cells.
Table_1.getDataSource().getResultMember(dimension: String | DimensionInfo, selection: Selection) : ResultMemberInfo | undefined

//Returns number of visible rows and columns to help users understand current table structure.
Table.getRowCount(): integer
Table.getColumnCount(): integer

For more information about the APIs, refer to Analytics Designer API Reference.

Code Examples

Example

The first example specifies that the input parameter location equals CT1.

Sample Code
Chart_1.getDataSource().getResultset({
    "@MeasureDimension": "[Account_BestRunJ_sold].[parentId].&[Gross_Margin]",
    "Location_1": "[Location_1].[State_1].&[CT1]"
});

It returns the following result set:

Output Code
[{
"@MeasureDimension": {
"id": "[Account_BestRunJ_sold].[parentId].&[Gross_Margin]",
"description": "Gross Margin",
"formattedValue": "489000000",
"rawValue": "489000000"
},
"Location_1": {
"id": "[Location_1].[State_1].&[CT1]",
"description": "California",
"parentId": "[Location_1].[State_1].&[SouthAmerica]"
}
}]
Example

The second example leverages the getDataSelections API to get the product members of the selected data in the table.

Sample Code
// Get distinct product member of the table widget
var selections = Table_1.getDataSource().getDataSelections();
var memberIds = ArrayUtils.create(Types.string);
for (var i = 0; i < selections.length; i++) {
    var member = Table_1.getDataSource().getResultMember("Product_1", selections[i]);
    if (member && member.id && memberIds.indexOf(member.id) < 0) {
        memberIds.push(member.id); 
    }
}