Skip to content

getKendoDataSource

The getKendoDataSource method creates a KendoUI Datasource from a set of data retrieved using the other report API functions.

Syntax

getKendoDataSource (data, asGrid, columnList, schema)

Arguments

data : JSON (required)

The data to be used for the KendoUI Datasource. Data must be retrieved using report API methods such as getRange or getCalculatedRange.

asGrid : boolean (required)

Specify if the data being passed is in the grid format.

columnList : object (required)

JavaScript array of values that represent the columns of your data in order.

These values can not have any spaces or special characters in them

schema : object

A KendoUI Schema object that will be assiged as the schema for the datasource.

Return

This method returns a KendoUI Datasource.

Examples

Example 1

In this simple example we are retrieving a range of data from the current report (this is hidden) and constructing a KendoUI Grid:

// Setup a PostRender binding
report.api.bind("postrender", function(){

    // Get the column headings from the report
    var columnHeadings = report.api.getValues("B2:M2");
 
    // Get the data from the report in Grid format
    var data = report.api.getRange("B3:M33", true);

    // Create a schema for the data so that we can do things like formatting
    var mySchema = {
        model:{
            fields:{
                Jan: {type: "number"},
                Feb: {type: "number"},
                Mar: {type: "string"},
                Apr: {type: "string"},
                May: {type: "string"},
                Jun: {type: "string"},
                Jul: {type: "string"},
                Aug: {type: "string"},
                Sep: {type: "string"},
                Oct: {type: "string"},
                Nov: {type: "string"},
                Dec: {type: "string"}
            }
        }
    };
 
    // Get the KendoUI Datasource
    var dataSource = report.api.getKendoDataSource(data, true, columnHeadings, mySchema);

    // Build a KendoUI Grid in R1C1 using the data as a datasource
    report.api.find("#R1C1").kendoGrid({
        dataSource : dataSource        
    });
}); 

How to get Example 1 Working:

  • Publish the worksheets below
  • Add the above Javascript to the Javascript Asset on the report getKendoDataSource-Ex1-Main
  • Save the Javascript and recalculate the report.

Example 1 Worksheet

Example 2

In this example, we have wired up some Cube data with Member Pickers so that the report is dynamic and you can change the member selections in the report and see the grid update.

We pass a schema into the call to getKendoDataSource so that we can get nicely typed data for formatting in the grid.

We are getting a bit fancy with the definition of the grid by providing specifics for aggregations, grouping, page sizes and formatting.

var ds;
var schema;
var dsHeading;
var dsData;
var asGrid = true;
var queryString;
$(document).ready(function(){
   $("#reportArea").bind("report.PostRender",function(){

         $(".reportData").css("width","100%");

        // Retrieve the currently select members
        queryString = report.api.getSelectedMembersEncoded();

        // Retrieve the headings and data from the source report
        dsData = report.api.getCalculatedRange("getKendoDataSource-Ex2-Data", "ProductData", asGrid, queryString);
        dsHeading = report.api.getCalculatedValues("getKendoDataSource-Ex2-Data", "ReportHeadings");
        // Define the schema to ensure that numbers are treated as numbers in the Grid
        schema = {
               model:{
                  fields: {
                     Category:{type: "string"},
                     Model:{type: "string"},
                     Product:{type: "string"},
                     Qty:{type: "number"},
                     Sales:{type: "number"},
                     GP:{type: "number"}
                  }
               }
         };

        // Convert the returned data to a Kendo DataSource object
        ds = report.api.getKendoDataSource(dsData, asGrid, dsHeading, schema);
        // Define Initial Grouping for the DataSource
        ds.group(
            {
                field: "Category", aggregates: [
                    { field: "Product", aggregate: "count" },
                    { field: "Qty", aggregate: "sum" },
                    { field: "Sales", aggregate: "sum" },
                    { field: "GP", aggregate: "sum" }
                ]
            }
         );
        // Define Aggregations for the DataSource
        ds.aggregate([
            { field: "Product", aggregate: "count" },
            { field: "Qty", aggregate: "sum" },
            { field: "Sales", aggregate: "sum" },
            { field: "GP", aggregate: "sum" } ]
         );

        // Define PageSize for the DataSource
        ds.pageSize(15);
        
        $("#trange").css("width","99%");

        // Create the Kendo Grid from the DataSource
        $("#trange").kendoGrid({
            dataSource: ds,
            groupable: true,
            sortable: true,
            selectable: "multiple cell",
            filterable: true,
            resizable: true,
            reorderable: true,
            pageable: {
                refresh: true,
                pageSizes: true
            },
            columns: [ 
            {
                field: "Category",
                groupable: true,
                width: 90,
                title: "Category"
            } , {
                field: "Model",
                groupable: true,
                width: 90,
                title: "Model"
            } , {
                width: 90,
                field: "Product",
                title: "Product",
                footerTemplate: "Total Count: #=kendo.toString(count,'n0')#",
                groupable: false,
                aggregates: ["count"],
                groupFooterTemplate: "Count: #= kendo.toString(count,'n0') #"
            } , {
                width: 90,
                field: "Qty",
                format: "{0:n0}",
                title: "Qty",
                template: "<span style='float:right'>#=kendo.toString(Qty,'n0')#  </span>",
                footerTemplate: "Total Sum: #=kendo.toString(sum,'n0')#",
                groupable: false,
                aggregates: ["sum"],
                groupFooterTemplate: "Sum: #=kendo.toString(sum,'n0')#"
            } , {
                width: 90,
                field: "Sales",
                format: "{0:c}",
                title: "Sales",
                template: "<span style='float:right'>#=kendo.toString(Sales,'c')#  </span>",
                footerTemplate: "Total Sum: #=kendo.toString(sum,'c')#",
                groupable: false,
                aggregates: ["sum"],
                groupFooterTemplate: "Sum: #=kendo.toString(sum,'c')#"
            } , {
                width: 90,
                field: "GP",
                format: "{0:c}",
                title: "GP",
                template: "<span style='float:right'>#=kendo.toString(GP,'c')#  </span>",
                footerTemplate: "Total Sum: #=kendo.toString(sum,'c')#",
                groupable: false,
                aggregates: ["sum"],
                groupFooterTemplate: "Sum: #=kendo.toString(sum,'c')#"
            },
                {title: "&nbsp;", width: "210px" }
            ]
        });
    });
});

How to get Example 2 Working:

  • Publish both of the worksheets below
  • Add the above Javascript to the Javascript Asset on the report getKendoDataSource-Ex2-Main
  • Save the Javascript and recalculate the report.

Example 2 Main Sheet  Example 2 Data Sheet

Back to top