create another column in grid with de sum of column1 and 2.

[oluwabusola]

I need to create another column in grid with the sum of column1 and 2.

[oluwabusola]

here is the razor code:

    .Columns(c =>
    {
        c.Bound(p => p.RSAPin);
        c.Bound(p => p.EmployeeName).Title("Name And Surname");
        c.Bound(p => p.ctype).Title("Ctype");
        c.Bound(p => p.ERContribution).Title("ER Con");
        c.Bound(p => p.ERAVC).Title("ER Vol");
        c.Bound(p => p.EEContribution).Title("EE Con");
        c.Bound(p => p.EEAVC).Title("EE Vol");

        c.Bound(p => p.TotalContribution).Title("Total").ClientTemplate("#=calculate(data)#");
       
        c.Bound(p => p.FileID);
        // c.Bound(p => p.Useed);
        c.Command(command => 
        {
            command.Edit(); command.Destroy();
        })
        .Width(210);
        
    })



.DataSource(d => d
.Ajax()
.Read(r => r.Action("Get", "ManualImport")
.Type(HttpVerbs.Get))
.PageSize(5)

.Model(model => model.Id(s => s.Useed))
    .Read(read => read.Action("Index", "ManualImport"))
    .Create(create => create.Action("Create", "ManualImport"))
    .Update(update => update.Action("Edit", "ManualImport"))
    .Destroy(delete => delete.Action("Delete", "ManualImport"))


)
.Pageable(pager => pager.PageSizes(new int[] { 10, 20, 30, 40, 50 }))
.Resizable(resize => resize.Columns(true))
.Filterable()
.Sortable(sortable => sortable.AllowUnsort(true))
.Groupable()
.Editable(e => e.Mode(GridEditMode.InLine))
.ToolBar(t => t.Create())


        )

<script>
    function calculate(data) {

        var result = p.EEContribution + p.ERContribution;
        return result;

</script>

 

 

[Georgi]

Hello Oluwabusola,

Thanks for sharing your solution with the community.

Indeed a possible solution is to use a ClientTemplate and within the template execute a function which returns the sum of the two columns.

e.g.

// column with template

columns.Template(@<text></text>).ClientTemplate("#=calculate(data)#").Title("Sum");

// calculate function

    function calculate(data) {
        var result = data.FirstField + data.SecondField;
        return result;
    }

Regards,
Georgi
Progress Telerik

[oluwabusola]

Thanks Georgi for the reply,

If the Value of the FIRSTFIELD OR SECONDFIELD changes, i want to see the effect on the SUMFIELD  immediately without refreshing the page. and the values of the SUMFIELD  must be updated in the database.

[Georgi]

Hi Oluwabusola,

I would recommend you to use incell editing and enable the AutoSync property of the dataSource. Thus when the user updates a value, the data source will automatically save the changes and the grid will be rebound to the updated data.


Regards,
Georgi
Progress Telerik

[oluwabusola]

Thank you Georgi ,

That works fine.

 

I have some challenges 

1              How do I make right click functions in kendo Grid work?
2              how do I exclude some of the columns from sorting, filtering
3              I want to make one of the columns a lookup I.e.   making it a dropdown that fetching data from another table

What I have tried so far:
@(Html.Kendo().Grid<EMS>(Model).Name("ems")
        .Columns(c =>
        {
        c.Bound(p => p.RSAPin);
        c.Bound(p => p.EmployeeName).Title("Name And Surname");
        c.Bound(p => p.ctype).Title("Ctype");//make it a lookup column
       //and except it from sorting
        c.Bound(p => p.ERContribution).Title("ER Con");
        c.Bound(p => p.ERAVC).Title("ER Vol");
        c.Bound(p => p.EEContribution).Title("EE Con");
        c.Bound(p => p.EEAVC).Title("EE Vol");
        c.Template(@<text></text>).ClientTemplate("#=calculate(data)#").Title("Total");
            c.Bound(p => p.FileID);
            c.Command(command =>
            {
                command.Edit();
                command.Destroy();
            })
            .Width(220);
        })
.DataSource(d => d
.Ajax()
.ServerOperation(false)
.Read(r => r.Action("Get", "ManualImport")
.Type(HttpVerbs.Get))
.PageSize(20)
.AutoSync(true)
.Model(model => model.Id(s => s.Useed))
    .Read(read => read.Action("Index", "ManualImport"))
    .Create(create => create.Action("Create", "ManualImport"))
    .Update(update => update.Action("Edit", "ManualImport"))
    .Destroy(delete => delete.Action("Delete", "ManualImport"))


)
.Pageable(pager => pager.PageSizes(new int[] { 10, 20, 30, 40, 50 }))

.Resizable(resize => resize.Columns(true))
.Filterable()
.Sortable(sortable => sortable.AllowUnsort(true))
.Groupable()
.Editable(e => e.Mode(GridEditMode.InCell))
.ToolBar(t => t.Create())
 .Selectable(selectable => selectable
            .Mode(GridSelectionMode.Single)
            .Type(GridSelectionType.Row))
 )


<ul id="ctxMenu">
                    <li data-action="fmatch"><a href="#" onclick=onRowSelected();>Force Match</a></li>
                    <li data-action="duplicate">Un-Force Match/Process</li>
                    <li data-action="duplicate">To Contribution Control</li>
                    <li data-action="fmatch">Recall Exported Schedule</li>
                    <li data-action="duplicate">Re-Process</li>
                    <li data-action="duplicate">Un Delete</li>
                    <li data-action="duplicate">Bank Statement</li>
                    <li data-action="duplicate">On / Off: Schedule Block</li>
                    <li data-action="duplicate">Match</li>
                    <li data-action="duplicate">Mass Match</li>
                </ul>

<script>
    // Right Click Event
    $("#ems").on("mousedown", "tr[role='row']", function (e) {
        if (e.which === 3) {
            $(this).addClass("k-state-selected");
            var gridData = $('#ems').data("kendoGrid");
            var selectedRowData = gridData.dataItem($(this));
            var SelectedScheduleID = selectedRowData.ScheduleID;
            //alert('Right click ' + SelectedScheduleID );
        }
    });
    //Selected Row that populate Con Header
    function onRowSelected(e) {
        debugger;
        var gview = $("#ems").data("kendoGrid");
        //Getting selected item
        var selectedItem = gview.dataItem(gview.select());
        var ticketId = selectedItem["Useed"];
        window.location.href = "/PreScheduleMatching/Edit/" + ticketId;
        grid.select(e.currentTarget);
    }
    // Context Menu
    $("#ctxMenu").kendoContextMenu({
        orientation: "vertical",
        target: "#ems",
        filter: "tr[data-uid]",
        select: function (e) {
            var action = $(e.item).attr("data-action");
            var row = e.target;
        }
    });

</script>

[Georgi]

Hello Oluwabusola,

Straight to your questions:

1. I assume that the requirement is to attach an event handler to the right click of a row in the Kendo Grid. Correct me if I am wrong. A possible solution is to attach a handler to the contextmenu event when the document is ready.

e.g.

$(function(){
  $('#grid').on('contextmenu', 'tr', function(e){
    e.preventDefault();
    alert(' row with index: ' +$(e.currentTarget).index() + ' clicked');
    // do stuff
  })
})

2. You can disable filtering and sorting for a certain column as shown below:

columns.Bound(x => x.FieldName).Filterable(x=> x.Enabled(false)).Sortable(false);

3. The Kendo Grid supports lookup columns out of the box. Please refer to our Foreign Key Column demo for further information. 


Regards,
Georgi
Progress Telerik

[oluwabusola]

How do i optimize the kendo grid  performance when loading large volume of data OF ABOUT 1MILLION ROWS from data-source?

 

 KENDO GRID CODE:

@(Html.Kendo().Grid(Model).Name("SetupEmployers")
              // .Events(events => { events.Change("onRowSelected"); })
              .Columns(c =>
              {
                  c.Bound(p => p.Pencom_Nr);
                  c.Bound(p => p.Name);
                  c.Bound(p => p.AddLine1);
                  c.Bound(p => p.Tel);
                  c.Bound(p => p.Sector);
                  c.Bound(p => p.Category);
                  c.Bound(p => p.LongName);
                  //c.Command(command =>
                  //{
                  //    command.Edit();
                  //    command.Destroy();
                  //});
              })
    .DataSource(d => d.Ajax()
    .ServerOperation(false)
    
    .Model(model => model.Id(s => s.ER_Code))
    .Read(read => read.Action("Index", "Setup_Employers"))
    .Create(create => create.Action("Create", "Setup_Employers"))
    .Update(update => update.Action("Edit", "Setup_Employers"))
    .Destroy(delete => delete.Action("Delete", "Setup_Employers"))
    )
    
    .Pageable(pager => pager.PageSizes(new int[] { 20, 30, 40, 50, 60,70,80,90,100 }))
    .Pageable()
    .Filterable()
    .Sortable()
    .Groupable()
    .Editable(e => e.Mode(GridEditMode.InLine))
     //.ToolBar(t => t.Create())
     .Selectable(selectable => selectable
            .Mode(GridSelectionMode.Single)
            .Type(GridSelectionType.Row))
            
    )

 

[Georgi]

Hello Oluwabusola,

In general, the Grid can bind to large collections of data but it all depends on the configuration used and the machine of the client. I will try to describe the main features, which are usually used when working with large data and some suggestions.

For displaying large amounts of data paging is essential. Best performance is achieved with server-side paging, which loads only the currently visible page of data and with virtual and endless scrolling, which load data on demand. Here are some demos, which you can see:

• https://demos.telerik.com/kendo-ui/grid/remote-data-binding
• https://demos.telerik.com/kendo-ui/grid/virtualization-remote-data
• https://demos.telerik.com/kendo-ui/grid/endless-scrolling-remote

Other things that matter when binding to large amounts of data:

• Number and content of columns: if there are many columns or if they contain templates with other Kendo UI widgets, the Grid page size needs to be set to as small a number as your design allows.
• Respectively, the page size. It shouldn't be too big, to avoid loading to much DOM content in the page, slowing down its performance. I would recommend a page size not bigger than 50, unless you are using virtualization, when 100 is also ok.
  Respectively, the page size. It shouldn't be too big, to avoid loading to much DOM content in the page, slowing down its performance. I would recommend a page size not bigger than 50, unless you are using virtualization, when 100 is also ok.Respectively, the page size. It shouldn't be too big, to avoid loading to much DOM content in the page, slowing down its performance. I would recommend a page size not bigger than 50, unless you are using virtualization, when 100 is also ok.
  

Regards,
Georgi
Progress Telerik

[oluwabusola]

Many thanks to you Georgi for your timely response,

The actual Challenge am facing is that the page took about 4 to 5 minutes to load which is not good for UX.

how do i resolved this.

Thanks in advance

[Georgi]

Hello Oluwabusola,

I have investigated the provided code and noticed that server operations are disabled. Therefore the grid requests the whole set of data with a single request which is not an efficient approach.

Please enable server operations and set the page size to be as small as your design allows.

e.g.

@(Html.Kendo().Grid(Model).Name("SetupEmployers")
              // .Events(events => { events.Change("onRowSelected"); })
              .Columns(c =>
              {
                  c.Bound(p => p.Pencom_Nr);
                  c.Bound(p => p.Name);
                  c.Bound(p => p.AddLine1);
                  c.Bound(p => p.Tel);
                  c.Bound(p => p.Sector);
                  c.Bound(p => p.Category);
                  c.Bound(p => p.LongName);
                  //c.Command(command =>
                  //{
                  //    command.Edit();
                  //    command.Destroy();
                  //});
              })
    .DataSource(d => d.Ajax()
    .ServerOperation(true)
    .PageSize(20)
    .Model(model => model.Id(s => s.ER_Code))
    .Read(read => read.Action("Index", "Setup_Employers"))
    .Create(create => create.Action("Create", "Setup_Employers"))
    .Update(update => update.Action("Edit", "Setup_Employers"))
    .Destroy(delete => delete.Action("Delete", "Setup_Employers"))
    )

    .Pageable(pager => pager.PageSizes(new int[] { 20, 30, 40, 50, 60,70,80,90,100 }))
    .Pageable()
    .Filterable()
    .Sortable()
    .Groupable()
    .Editable(e => e.Mode(GridEditMode.InLine))
     //.ToolBar(t => t.Create())
     .Selectable(selectable => selectable
            .Mode(GridSelectionMode.Single)
            .Type(GridSelectionType.Row))

    )

Regards,
Georgi
Progress Telerik

[oluwabusola]

Thanks Georgi,

When .ServerOperation(true)  the following functions stop working:

.Filterable()
.Sortable()
.Groupable()

and the .Pageable() didn't return data when next button is pressed.

 

What i am doing wrong???

Thanks in advance.

 

[Georgi]

Hi Oluwabusola,

Thanks for the update.

Is your server configured to handle server operations? When the Kendo grid uses server operations, when the user navigates to a page, groups, sorts, etc... A request to the server is sent which contains data about the operation. Therefore the server should handle that request and respond with the corresponding data.

Kendo provides an extension method (ToDataSourceResult) which handles such requests. Please refer to the following article which demonstrates how to configure the Kendo grid for Ajax binding and server operations step by step:

• https://docs.telerik.com/aspnet-mvc/helpers/grid/binding/ajax-binding

Please examine steps 6, 7 and 8 which explain how to use the ToDataSourceResult method.


Regards,
Georgi
Progress Telerik

[oluwabusola]

Hello Georgi,

using incell editing and enabling the AutoSync property of the dataSource works just fine on my machine. But when deployed on the server the AutoSync stoped working. I think it has to do with the security constraint on the database level. how do i configure the kendoGrid to work with sa and password of the database???

[Georgi]

Hi Oluwabusola,

Based on the provided information I assume that the action method which the dataSource requests requires authorization headers. Please correct me if I am wrong.

A possible solution is to use the beforeSend event of the DataSource.

e.g.

$(function() {
    var grid = $("#grid").data("kendoGrid");

    grid.dataSource.transport.options.read.beforeSend = function (req) {
        req.setRequestHeader('Authorization', 'auth token goes here');
    };

    grid.dataSource.read();
});

Regards,
Georgi
Progress Telerik

[Gregory]

Hello Georgi,

The kendo grid stop working and i cant figure out what went wrong. 

Here is my Action Method:

 public async Task<ActionResult> Read_Index([DataSourceRequest]DataSourceRequest request)
        {
            using (var db = new OminiraEntities())
            {
                IQueryable<Setup_Employers> employer = db.Setup_Employers.Include(m => m.Setup_Categories);
                DataSourceResult result = await employer.ToDataSourceResultAsync(request);
                return Json(result, JsonRequestBehavior.AllowGet);
            }

}

 

And the view :

 

 @(Html.Kendo()
        .Grid(Model)
        .Name("SetupEmployers")
        .DataSource(d => d  //Configure the Grid data source.

        .Ajax()  //Specify that Ajax binding is used.
        .Read(read => read.Action("Read_Index", "Setup_Employers")) // Set the action method which will return the data in JSON format
        
    .AutoSync(true)
    .Model(model => model.Id(s => s.ER_Code))
    )
              .Columns(c =>
              {
                  c.Bound(p => p.Pencom_Nr);
                  c.Bound(p => p.Name);
                  c.Bound(p => p.AddLine1);
                  c.Bound(p => p.Tel);
                  c.Bound(p => p.Sector);
                  c.Bound(p => p.Category);
                  c.Bound(p => p.LongName);
                  //c.Command(command =>
                  //{
                  //    command.Edit();
                  //    command.Destroy();
                  //});
              })


    .Pageable(pager => pager.PageSizes(new int[] { 20, 30, 40, 50, 60,70,80,90,100 }))
     .Pageable()
     .Filterable()
     .Sortable()
     .Groupable()
     .Editable(e => e.Mode(GridEditMode.InLine))
     .Selectable(selectable => selectable
            .Mode(GridSelectionMode.Single)
            .Type(GridSelectionType.Row))

    )

[Gregory]

And the output

 

[Georgi]

Hi Gregory,

I have examined the provided configuration and noticed that the grid is configured for both AJAX and server binding. It is not possible to use both bindings as they are not compatible. When bound on the server, the HTML table is rendered on the server and the dataSource is not populated. However, when bound via AJAX, the data is requested on the client, the table is rendered on the client and the data is stored in the dataSource.

Looking at the configuration I assume that the requirement is to bind the grid via AJAX. Could you please remove the parameter from the Grid method and let me know if the issue still occurs?

e.g.

@(Html.Kendo()

       .Grid<Setup_Employers>()

       .Name("SetupEmployers")
          ...

Regards,
Georgi
Progress Telerik

[Gregory]

Thank you Georgi for the response,

i tried it like this  .Grid<Setup_Employers>()  and got this error   "The type or namespace name 'Setup_Employers' could not be found (are you missing a using directive or an assembly reference?)"

 

so i tried it this way  ".Grid<CCM1.Models.Setup_Employers>()"  but still not loading the data

[Gregory]

Thank you Georgi for the response,

i tried it like this  ".Grid<Setup_Employers>()"  and got this error   "The type or namespace name 'Setup_Employers' could not be found (are you missing a using directive or an assembly reference?)"

so i tried it this way  ".Grid<CCM1.Models.Setup_Employers>()"  but still not loading the data

 

This how the namespace was imported:

@model IEnumerable<CCM1.Models.Setup_Employers>

[Georgi]

Hi Gregory,

It is necessary to add a using directive to the namespace of the model. Kendo.Grid is a generic method and it expects the type of the model as a type parameter.

Nevertheless, could you please make sure there are no errors in the console of your browser?

Having said that, sharing a demo that clearly replicates the issue would definitely help us fully understand the case and we will be able to provide further assistance to the best of our knowledge.


Regards,
Georgi
Progress Telerik

[oluwabusola]

Hello Georgi,

how do I pass the result set of a kendo grid as a parameter to a stored procedure by clicking of a button?

[Georgi]

Hi Oluwabusola,

What you mean by the `result set`? Do you mean the data of the grid? 

If that is the case you could send an ajax to the server as follows:

$.ajax({
  data: {items: grid.dataSource.data().toJSON()},
  url: '/controller/action',
  method: 'POST',
  ...
})

Regards,
Georgi
Progress Telerik

[oluwabusola]

hello Georgi

Either filtered base on PFA column or any of the columns. I need to update the records of the filtered rows only.

I want to update the result set of the kendo grid by clicking a button, and this button make a call to a stored procedure to perform the update using the ScheduleID as a key.

[Georgi]

Hello Oluwabusola,

If I understand correctly you would like to submit the filtered items to the server. Correct me if I am wrong.

Since all items are already available on the server, I would suggest you to send the PFA filter to the server instead of sending the dataItems. This way you could filter the items on the server and omit sending a request which contains a lot of data. After you filter the items you can modify them as per your requirement.

Is this an option for you?


Regards,
Georgi
Progress Telerik

[oluwabusola]

Hello Georgi

Thanks for your response.

Here is how i got it fixed

 

The Action Method:

using (var db = new MyDb())
                {
                    IQueryable<T> t= db.T.Where(x => x.Status == filter ).OrderByDescending(x => x.ScheduleID);

                    DataSourceResult result = await t.ToDataSourceResultAsync(request, models=> new ViewModel
                    {
                        ...............some codes

                    });

                  //save the result to a global variable
                    System.Web.HttpContext.Current.Session["filteredResult"] = result;
                    return Json(result, JsonRequestBehavior.AllowGet);
                }
            }

           THEN THE UPDATE BUTTON CALL THIS ACTION METHOD:

public ActionResult Update()
        {
            var cnnString = new SqlConnection();
           cnnString.ConnectionString = ConfigurationManager.AppSettings["DbConnection"];

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cnnString;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "SP_UpdateTable";

            DataSourceResult filteredResult = new DataSourceResult();
            filteredResult=(DataSourceResult)System.Web.HttpContext.Current.Session["filteredResult"];

            foreach (var item in filteredResult.Data)
            {
                conheaderViewModel conheader = (conheaderViewModel)item;
                cmd.Parameters.Add(new SqlParameter("@SID", SqlDbType.Int) { Value = conheader.ScheduleID });
                cnnString.Open();
                try
                {
                    int o = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.Write(ex.Message);
                }

            }
            cnnString.Open();
            try
            {
                int o = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }

            cnnString.Close();
            return RedirectToAction("Edit");

 

is there any better way to get this done???

[Georgi]

Hi Oluwabusola,

Storing the result in the current session is also an option. Furthermore, this way you omit sending a request from the client which should improve the performance. Currently I cannot provide an alternative solution as I do not fully understand the case. If you doubt that the current solution is not good enough, please provide me with a little more details about the use case and I will think of other solutions.

 

Regards,
Georgi
Progress Telerik

[Neha]

Hi...

I want to do the same thing but don't want to use AutoSync. The added value should dynamically change without saving it. Just like in Excel sheet

[Georgi]

Hi Neha,

If using AutoSync is not suitable for you, you could update the sum column within the cellClose event handler. Is that an option for you?

Regards,
Georgi
Progress Telerik