【Publisher】KENDO Template can be prepared with Sharperlight expression

# Introduction #
This is a simple KENDO Grid example with using its template attribute to define the columns.

I wanted to mention we can define the template for the columns with Sharperlight expression and pass it to the KENDO Grid definition as a part of the datasource.

When KENDO Grid is defined, we have to configure the columns and we give special format such as displaying an icon etc to them sometimes.
In that case, we basically use a template and define it in the code directly like this .
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6f3edccb-5ba7-1414-58c5-098ebf6ca17d.png)
It uses sometimes the reference of the column(s) in the given dataset. `#={reference name}#`

An example is here.
```
$("#gridCompanies").kendoGrid({
    height: '100%',
    columns: [{
        field: "CompanyName",
        title: "Company",
        width: 160
    },{
        title: "Link",
        template: 
            "<button class='compButton button1' id='#=Number#' onclick='Company_Click(this)' 
                data-id='#=Number#' data-dur='0'>
                <img class='compButtonImg' id='compButtonImg#=Number#' src='{_System.Rest.URL}Image/?icode=Refresh_big'>
            </button>",
        width: 60
    },{
      ...
      ...
    }]
}).data("kendoGrid");
```
However, alternatively we can define the template with Sharperlight expression and pass it to the KENDO Grid definition as a part of the dataset.

This expression named as **Icon** has the definition of a template.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/794adcf6-02e7-febd-eda5-722feda18081.png)
This is the actual template definition.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a925f335-72fd-b2c8-735a-48440e5917d1.png)
We can see that the template **Icon** is a part of the dataset.
This datset is passed to KENDO Grid via Sharperlight RESTful service.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/19e76e66-d47e-ffaf-b7fd-72b571211e4d.png)
At the KENDO Grid definition side, we just specify its name.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/84933a03-313b-2737-8684-227de569dbc6.png)

Well, let's have a look through to what I have done here. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a5241312-e6d3-fd96-132e-d53565815cd3.png)



### Resources ###
The following things I was prepared.
+ A HTML file
+ Two published Reports

A html file which the KENDO Grid is defined in is imported to a sharperlight published report.
The published report works as a container of the HTML code.

Then the KENDO datasource is also designed with a Sharperlight published report(query).
The datasource feeds a list of SAP Business One companies to the grid.



::: note 
**Sharperlight Published Report** is a report being published via Sharperlight RESTful service.
A published report has a query and it fetches a dataset from a connected database, and presents it as a table, a chart or a pivot table etc to the browser.
A published report can be fully customised by using it as the container of a HTML code written.
It also can behave as the endpoint of API service so it can return the dataset in JSON format etc instead of displaying it as a report.
:::


### Two Published Reports ###
One works as a container of the HTML file, and the other works as a provider of a dataset for the KENDO Grid.

#### Published Report - A Container of the HTML file ####
This published report has a HTML file imported, and it displays the imported HTML in the browser when it is called rather than displaying the result of the query associated.

The query associated to the published report can be used as passing some values to the HTML code at runtime.

In this example, I defined a URL with Sharperlight Expression in the query and the URL is passed to the HTML by referencing its name from the HTML.
**{\*Row.dsCompanies.text}**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/dd57b2be-fb2b-5f29-dc9c-a271bcfb5531.png)
The URL defined as an expression is for calling another published report, and its query associated fetches a dataset from SAP Business One database for the KENDO Grid.


#### Published Report - Designed for getting data from the database ####
This published report works as the endpoint of Sharperlight RESTful service.
It is called from the HTML by using its URL, and returns a dataset populated with the assicated query in JSON format.
```
{_System.Rest.URL}DataSource/?query=QiitaSample.KENDOTemplateExpressionDS&dfmt=jsonarray&dcat=UseNames
```

### A HTML File ###
This HTML code is saved as a file and imported to the published report which behaves as a container.

The HTML file is imported as a resource.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a9a592e9-3831-da41-3720-69fdc2ac0497.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d7f4747b-5b7f-fe89-df2e-7c51c60ec09c.png)
The reference code is used in the Custom HTML option here so that the HTML is displayed in the browser when it is called.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8991fe49-38fb-96c1-3428-9ea108200e83.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/66c9d331-e0a4-cfa2-314e-e00837b45629.png)



The whole HTML code is here.
```
<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=Edge" other="IE11">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="author" content="Sharperlight" />
    <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" />
    <link rel="manifest" href="manifest.json">

    <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" />
    <style>
        body{
            font-family: sans-serif;
            height: 400px;
            width: 600px;
        }
        .GridList{
            font-size: 12px;
        }
        .compButtonImg{
            position: relative;
            width: 20px;
            height: 20px;
            top: 2px;
        }
        .compButton{
            width: 50px;
            cursor: pointer;
            text-align: center;
            text-decoration: none;
            outline: none;
            color: #fff;
            border: none;
            border-radius: 7px;
            box-shadow: 0 3px #999;
            height: 25px;
        }
        .button1{
            background-color: #bfbfbf;
		    width: 50px;
	    }
	    .button1:hover {
		    background-color: #a6a6a6;
	    }
	    .button1:active {
  		    background-color: #a6a6a6;
  		    box-shadow: 0 5px #4d4d4d;
  		    transform: translateY(4px);
	    }
    </style>

    <title>KENDO Grid Sample</title>
    
	<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>
</head>
<body>
    <div class="GridList" id="gridCompanies"></div>
    
        
    <script>
        var gDS_Companies = "{*Row.dsCompanies.text}";
        var kGridCompanies = null;
        var _dataSource = null;

        $(document).ready(function() {
            // executes when HTML-Document is loaded and DOM is ready
            console.log("document is ready");
  
            kGridCompanies = $("#gridCompanies").kendoGrid({
                height: '100%',
                columns: [{
                    field: "CompanyName",
                    title: "Company",
                    width: 160
                },{
                    title: "Link",
                    template: "#=Icon#",
                    width: 60
                },{
                    title: "Modified DateTime",
                    template: "#=ModifiedDateTime#",
                    width: 95
                }]
            }).data("kendoGrid");
            
            _Populate_Companies(gDS_Companies);
            
        });

        
        function _Populate_Companies(dsURL){
            try{
                _dataSource = _Datasource_Companies_Create(dsURL);
                if (_dataSource != null){				
                    _dataSource.read().then(function (){
                        try{
                            kGridCompanies.setDataSource(_dataSource);
                            kGridCompanies.refresh();      
                        }catch (err){
                            alert("Error in _Populate_Companies(): " + err.message);
                        }
                    });
                }
            }catch (err){
                alert("Error in _Populate_Companies(): " + err.message);
            }
        };
        
        function _Datasource_Companies_Create(dsURL){
            try{
                var dsItems = new kendo.data.DataSource({
                    serverFiltering: false,
                    transport: {
                        read: {
                            datatype: "jsonp",
                            url: dsURL
                        }
                    },
                    schema: {
                        model: {
                            id: "Number",
                            fields: {
                                Number: { type: "number" },
                                CompanyName: { type: "string" },
                                ModifiedDateTime: { type: "string" },
                                Icon: { type: "string" }
                            }
                        }
                    }
                });
                return dsItems;
            }catch (err){
                alert("Error in _Datasource_Company_Create(): " + err.message);
                return null;
            }
        };
        

        function Company_Click(e){
            try{
                alert("It's number is " + e.id );
            }catch (err){
                throw "Error on Company_Click():" + err.message;
            }
        };
  </script>
</body>
</html>
```
As you can see, the template attribute for the Grid definition refers to the name of the expressions in the dataset given.


# Conclusion #
It is not the way we must use, but it may make you to maintain the code and also may worth to know.