【Publisher】Creating KENDO DataSource with Sharperlight Query

I would like to introduce how to use the query created in [Sharperlight](https://sharperlight.com/) 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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2c6fbeae-13c1-16ee-a62c-150ca7efc923.png)
### 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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4b16c340-940b-313a-4349-05c72a62edf5.png)
#### 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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a113f091-3be5-4905-741a-dc8881a0114f.png)

Now try the query with the `preview` button.
The query worked fine and I got the data back.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b1c4751c-96aa-315f-2083-19a11aafc90e.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b8960915-4c2a-4e90-6d8b-392a21b39dd0.png)

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.