【Publisher】Extended Report with JavaScript - KENDO kendoDropDownList control

[Sharperlight](https://www.sharperlight.com/) ships with the [Kendo](https://www.telerik.com/) JavaScript library.
The Kendo JavaScript library provides various WEB UI controls that can be combined with Sharperlight's published queries to create various web interfaces.
In this article, we will use the kendoDropDownList control.
There are three things to prepare: the HTML code (including JavaScript code) where the kendoDropDownList control is defined, the published query that is the container for the HTML code, and the published query that supplies dataset to the kendoDropDownList control.

The completed report looks like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b6c6ebd2-c56b-e7bc-e875-ccd62176de20.png)
## Writing HTML code (including JavaScript code) ##
### Header ###
Write code that references components of the Kendo JavaScript library.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6e9a20e7-2914-09a6-ad2e-750710ea9db4.png)

### Body of HTML code ###
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3b641f0d-52ee-d689-4600-fc8c18431f22.png)
➊: This is where Kendo DropDownList control is created.
➋: This represents the URL for the Sharperlight logo and the actual URL is defined the published query which is the container of this HTML code.
➌: They are Sharperlight specific tags and {*DIctionary.Code.Version} gets the translation of the word 'Version', and {*Application.Version} gets the version of Sharperlight installed.
### The Script ###
Here we define the Kendo DropDownList control and call the data supply functions.
#### The main body of the Script #### 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f84d43c5-00c2-912b-65e3-2ab67d576f45.png)
We define some global variables and place the code for building KENDO DropDownList control.
We also call a functuon which fetch the data and feed it to the control at the end. 
#### The function to create datasource for KENDO DropDownList control ####
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/bcb20864-75d7-f768-74cf-173aa120d681.png)
We create KENDO datasource definition here.
The global variable which holds the datasource URL i.e. the URL of a Sharperlight published query is used.
The output name of the query must be specified for the Schema Model definition. 
#### The function to fetch and feed the data to KENDO DropDownList control ####
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1ace8118-9107-d7c6-95be-54a5d6548276.png)
This function does fetching data with the KENDO datasource definition created by the function mentioned above and feed it to the KENDO DropDownList control.
#### Actual Code ####
```
<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8"/>
        <base href="{*Url.Root}"/>
        <link rel="shortcut icon" type="image/x-ico" href="favicon.ico"/>
        <link rel="icon" type="image/x-ico" href="favicon.ico"/>
        <title>Qiita KENDO</title>
        
        <link rel="stylesheet" type="text/css" href="{*Url.Root}Resources/kendo/styles/kendo.common.min.css" />
        <link rel="stylesheet" type="text/css" href="{*Url.Root}Resources/kendo/styles/kendo.default.min.css" />
        <link rel="stylesheet" type="text/css" href="{*Url.Root}Resources/kendo/styles/kendo.default.mobile.min.css" />
        <link rel="stylesheet" type="text/css" href="{*Url.Root}Resources/kendo/styles/kendo.silver.min.css"/>
        <link rel="stylesheet" type="text/css" href="{*Url.Root}Resources/kendo/styles/kendo.silver.mobile.min.css" />
        
        <script type="text/javascript" src="{*Url.Root}Resources/jquery/jquery.min.js"></script>
                
        <script type="text/javascript" src="{*Url.Root}Resources/kendo/js/kendo.all.min.js"></script>
        <script type="text/javascript" src="{*Url.Root}Resources/kendo/js/kendo.timezones.min.js"></script>
        
    </head>
    <body style="font-family: arial; font-size:13px">
        <div>
            <table class="QiitaSample_Table">        
                <tr>    
                    <td class="QiitaSample_Table_ColA"><div>States in Australia:</div></td>
                    <td class="QiitaSample_Table_ColB"><input id="KDD_ListOfStates" /></td>
                </tr>
            </table>
        </div>
        
        <div id="poweredBySharperlight" style="position: absolute; top: 1px; left: 300px;">
            <a href="https://www.sharperlight.com" target="_blank">
                <img src="{*Row.imgURLSharperlight.text}" title="{*Dictionary.Code.Version} {*Application.Version}" style="width: 48px; height: 48px;"/>
            </a>
            <div style="font-size:9px; font-weight: bold; position: relative; top: -32px; left: 48px;">Powered By Sharperlight</div>
        </div>

        <script>
            var gURL_ReadListOfStates = "{*Row.ReadListOfStates.text}";
            var gKddl_ListOfStates;
            
            $(function () {
                $("#KDD_ListOfStates").kendoDropDownList({
                    dataTextField: "dsStateName",
                    dataValueField: "dsStateId",
                    optionLabel: {
                        dsStateName: "Please select State here ...",
                        dsStateId: 0
                    },
                    change: function(e) {
                        var value = this.value();
                    },
                    optionLabelTemplate:'<span style="color:lightgray">-- Please select --</span>'
                });
                gKddl_ListOfStates = $("#KDD_ListOfStates").data("kendoDropDownList");
                
                _ListOfStates_Refresh();
            });    


            function _Datasource_States_Create(){
                try{
                    var dsStates = new kendo.data.DataSource({
                        serverFiltering: false,
                        transport: {
                            read: {
                                datatype: "jsonp",
                                url: gURL_ReadListOfStates
                            }
                        },
                        schema: {
                            model: {
                                fields: {
                                    dsStateId: { type: "number" },
                                    dsStateCode: { type: "string" },
                                    dsStateName: { type: "string" },
                                }
                            }
                        }
                    });
                    return dsStates;
                }catch (err){
                    alert("Error in Datasource_States_Create(): " + err.message);
                    return null;
                }
            };

            function _ListOfStates_Refresh(){
                try{
                    var ds = _Datasource_States_Create();
                    if (ds != null){				
                        ds.read().then(function (){
                            var data = ds.data();
                            if (data.length > 0){
                                gKddl_ListOfStates.setDataSource(ds);
                                gKddl_ListOfStates.refresh();
                                gKddl_ListOfStates.value(0);
                            }else{
                                throw ("Error in _ListOfStates_Refresh(): " + "States Dataset has not been populated yet.");
                            }
                        });
                    }
                }catch (err){
                    throw ("Error in _ListOfStates_Refresh(): " + err.message);
                }
            };

        </script>
    </body>
</html>
```
Once the HTML code is ready, it is set to the published query prepared as its container.
## Setting the HTML code to a Published Query ##
### Creating a Published Query ###
Launch **Publisher** from the Sharperlight application menu.
Create a new published query. After entering the `Group Name` and `Code`, start creating the query with `Edit Query` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/09d6a67c-589f-bf8c-89ab-e4fee5ba0de9.png)
Since this is a query as a container, we create a query that returns only one row.
Therefore, we use a **Custom Defined Dataset** and define one record.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/06ccace7-cc98-4c21-7b7e-e62f40073025.png)
As output items, define the published query URL for the data source and the URL for the logo using expressions.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/af60f381-83c1-aa8f-1b01-d061a3b16021.png)
The expression for the datasource
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cefaf3fd-d0b4-ea6c-b06f-bb52fa1737ae.png)
The expression for the logo
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4b561304-f35d-985d-41de-c9cce82f8b44.png)
### Importing HTML code to a Published Query ###
Once finish creating the query, go to the **Options** tab.
Here we use **Resources** and **Custom HTML** properties.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e28bd7fc-b75f-54a6-89a0-8bb6b132a7e4.png)
First, open the **Resources** property and import the HTML code file you created.
➊New、➋Specify file path、and➌copy the resource reference code
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cc47c9f5-20c3-00f5-d9e6-7bf5232df09f.png)
Go to Custom HTML property. Paste the copied resource reference code and you're done.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/37f01aa9-a3e1-f9d5-9e7d-45cd82f50fdc.png)
Press OK to save. You have now imported your HTML code into the container published query.
## Creating a Published Query for the datasource ##
Finally, create a published query that feeds the KendoDropDownList control. This is one of ways for using a published query.
A published query can be an endpoint that uses URL parameters to return a dataset in JSON format.
Start creating new published query with Sharperlight Publisher.
Its query definishon is something like this for this time. The dataset is created with Custom Defined Dataset.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8a7de21c-816d-58e9-b069-ef919ef93789.png)
Then its output items are like this.
Give name for each output items and their name is used in the JavaScript code to refer to the output fields.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d7c757e2-db03-77c3-1c13-5b4362fd9517.png)
This completes the definition of the published query for data supply. Press OK to save.
## Operation check ##
Start the Sharperlight service and select View Report menu item from the right-click menu in Publisher.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c8e1ea5f-e424-617d-d141-8f781c46ea02.png)
A page like this will appear. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d897d951-822f-1832-1472-1a8777b79de7.png)
When I click on the dropdown list, I would expect it to show selectable data.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7f8c0ec0-2e96-411e-ce15-c2cb42d0c03c.png)
Please have fun creating a variety of interactive reports by combining Sharperlight's Published Query with KENDO UI Controls.

Thank you for reading this article.