【Publisher】Creating KENDO DataSource with Sharperlight Query

I would like to introduce how to use the query created in Sharperlight as the Sharperlight RESTful Service API endpoint to acquire a dataset and create a KENDO DataSource object.

Sharperlight is typically installed on an application server, which can be in your company's network or in the cloud. And it maintains a connection to the database where the data necessary for the business is stored.

Published queries created with the Sharperlight publisher are not only published as web reports via the service, but also have functions such as supplying data sets in JSON format.

This time, I will introduce how to use the Sharperlight RESTful service to get data in JSON format and create a KENDO DataSource object. A KENDO DataSource object is used as an object that supplies data to various controls provided by the KENDO JavaScript Framework.

Published Query

Use the Sharperlight publisher to create published query. This query will return the required dataset.

Launch Publisher from the Sharperlight application menu.
Start creating published query with the New button.
Fill in Code, Group and Title.
The Code and Group entered here will be used as part of the endpoint URL address specified later.

Creating a query

Design the query with the Edit Query button.

Filter

After the query builder is started, begin from setting the filter.
Products: Sets which data source to use. Select SAP Business One here.
Company: Select demo company OEC Computers Australia.
Table：A/R Sales Invoice
As soon as you select a value in the table, the mandatory filters are displayed. The background color is red.
It also displays a list of selectable fields.
Specify N for the mandatory filter: Canceled?, and <ALL> for other mandatory filters.
Drag and drop the Document Date field from the selection list and set it as a filter.
Also, name the Document Date and Customer Code filters DocumentDate and CustomerCode respectively.

Output items

Drag and drop the following fields from the selection list or double-click to set them in the output area as output items. The name of the output item is later used as a reference code.

Now try the query with the preview button.
The query worked fine and I got the data back.

Save the query with the OK button.
Now that the query has been created, save the published query with the OK button.

Endpoint URL

The format of the query endpoint URL is as follows.
{Sharperlight Service URL}/DataSource/?query={Group}.{Code}&dfmt=jsonarray&dcat=UseNames

http(s)://xxxxx/mdService1Rest/DataSource/?query=QiitaSample._ds_SAPSalesInvoicesE&dfmt=jsonarray&dcat=UseNames

KENDO DataSource

You can create a KENDO DataSource using Sharperlight published query with the code below.
Specify the published query endpoint URL in the url attribute. The trailing &usid={_System.Rest.Usid} is a Sharperlight control tag and will be replaced with the session id at runtime (If this code is embedded in a Sharperlight published query).
The schema -> model -> fields attribute specifies the published query output fields by their name.

function _Datasource_Create(){
    try {
        var ds = new kendo.data.DataSource({
            transport: {
                read: {
                    datatype: "jsonp",
                    url: "http://xxxxx/mdService1Rest/DataSource/?query=QiitaSample._ds_SAPSalesInvoicesE&dfmt=jsonarray&dcat=UseNames&usid={_System.Rest.Usid}"
                }
            },
            schema: {
                model: {
                    fields: {
                        DocNum: { type: "number" },
                        DocDat: { type: "datetime" },
                        CusCod: { type: "string" },
                        CusNam: { type: "string" }
                    }
                }
            }
        });
        return ds;
    }catch(err){
        console.log("Error in _Datasource_Create(): " + err.message);
        return null;
    }
};

It is also possible to set values for filters in published query as optional parameters in the endpoint URL.
&flt{filter name}=xxx&flt{filter name}_2=yyy.
For example, &fltDocumentDate=2023/01/01&fltDocumentDate_2=2023/05/31
This is passed respectively to this location in the image below.

The entire URL looks like this.

http://xxxxx/mdService1Rest/DataSource/?query=QiitaSample._ds_SAPSalesInvoicesE&fltDocumentDate=2023/01/01&fltDocumentDate_2=2023/05/31&dfmt=jsonarray&dcat=UseNames&usid={_System.Rest.Usid}

I am happy if this helps when you develop web applications.
P.S. Sharperlight ships with the KENDO JavaScript Framework library.