Sub-Total Row

[Lee]

Hi,

I'm using the currently using the Grid with server side processing, grouped by Project Name, and using the ClientGroupFooterTemplate to display totals. This works great, but is there a way to add a sub-total row, for some other calculations?

I'm currently querying the items needed and then query the sub-totals row then concatenating the queries this is drastically slowing the load time.

Any ideas?

public ActionResult ProjectTaskListAjax(Guid id, [DataSourceRequest] DataSourceRequest request)
{
    var tasks = DataService.GetQueryableProjectTasksByUploadId(id);

    var queries = new[]
    {
        // This gets all the current tasks
        tasks.Select(x => new GridProjectTaskModel
        {
            [...]
            LabourCostTotal = x.LabourCostTotal,
            MaterialCostTotal = x.MaterialCostTotal,
            [...]
        }),

        // This gets all the tasks then groups by the Project and then calculates two columns that need to be displayed on the 'sub-total' row.
        tasks.GroupBy(x => x.Project.Name).Select(g => new GridProjectTaskModel
        {
            [...]
            LabourCostTotal = g.FirstOrDefault().PurchaseOrders.Where(x => x.SupplierType == "Sub-Contract Labour").Sum(x => x.TotalEx),
            MaterialCostTotal = (g.FirstOrDefault().PurchaseOrders.Where(x => x.SupplierType == "Sub-Contract Labour").Sum(x => x.TotalEx) * -1),
            [...]
        })
    };

    // Concat vs Union makes no difference here
    var mergedQuery = queries.Select(query => query.AsEnumerable()).Aggregate(Enumerable.Union);

    return Json(mergedQuery.ToDataSourceResult(request), JsonRequestBehavior.AllowGet);
}

[Viktor Tachev]

Hi Lee,

If you would like to display multiple aggregates for a given column you can use an approach similar to the one illustrated in the example below:

• https://demos.telerik.com/aspnet-mvc/grid/aggregates

With that said, based on the provided description it seems that the slow performance is due to the complexity of the query. Have in mind that this is not directly related to the Grid component. Nevertheless, I can suggest checking out the article below that discusses optimization techniques for Entity Framework queries.

• https://msdn.microsoft.com/en-us/library/hh949853(v=vs.113).aspx

Regards,
Viktor Tachev
Progress Telerik

[Neil]

Does the Aggregate functions nee to have a group
 
.Group(groups => groups.Add(p => p.UnitsInStock))

Because I can't get the sum.. I just want to get the Overall Sum, i.e. Qty of items in the grid.

 

[Viktor Tachev]

Hello Neil,

 

Out of the box aggregates will be displayed for the entire Grid and for each group. If data in the Grid is not grouped only the total aggregates will be shown.

 

Regards,
Viktor Tachev
Progress Telerik

[Neil]

I have something like this, but no suma is displayed..

 

col.Bound(c => c.Qty).Width(100)
                    .FooterTemplate(@<text>Total: @item.Sum</text>);

[Viktor Tachev]

Hello Neil,

 

The FooterTemplate option will be rendered on the server. Use ClientFooterTemplate instead and see how the behavior changes:

col.Bound(c => c.Qty).Width(100)
                    .ClientFooterTemplate("Total: #= sum #");

 

Regards,
Viktor Tachev
Progress Telerik

[Neil]

I got "Uncaught Reference Error: sum is not defined", when using .ClientFooterTemplate
.

With my issue, i only got a blank data here. 

Take a look at my grid, and see what did I miss..

[Preslav]

Hello Neil,

As I cannot see the full code of the Grid, I am not sure what is causing this issue.

I assume that the Aggregates calculation in the DataSource is missing. For example, in this demo - https://demos.telerik.com/aspnet-mvc/grid/aggregates the Sum of the UnitPrice is calculated like this:

    .DataSource(dataSource => dataSource
        .Ajax()
        .Aggregates(aggregates =>
        {
            aggregates.Add(p => p.UnitsInStock).Min().Max().Count();
            aggregates.Add(p => p.UnitsOnOrder).Average();
            aggregates.Add(p => p.ProductName).Count();
            aggregates.Add(p => p.UnitPrice).Sum();
        })
        .Group(groups => groups.Add(p => p.UnitsInStock))
        .Read(read => read.Action("Aggregates_Read", "Grid"))
    )

Please, check if adding the above resolves the problem.

 

Regards,
Preslav
Progress Telerik

[Neil]

Here's the snippet of the grid I've been having....

@(Html.Kendo().Grid<TelerikMvcApp2.Models.vw_Detail>()
        .Name("grid")
        .AutoBind(false)
        .Columns(col =>
        {
        col.Bound(c => c.SNo);
        col.Bound(c => c.ItemCode);
        col.Bound(c => c.Description);
        col.Bound(c => c.Qty).Width(100).HtmlAttributes(new { style = "text-align:right;" })
              .FooterTemplate(@<text> Total: @item.Sum </text>)
;
col.Bound(c => c.Unit);
})
.Pageable()
.DataSource(ds =>
ds.Ajax()
.Aggregates(ag =>
{
ag.Add(p => p.Qty).Sum();
})
.PageSize(5)
.Read(a => a.Action("Read_DetailGrid", "DASH"))

)
)

 

[Viktor Tachev]

Hello Neil,

 

I suggest using ClientFooterTemplate to display the aggregate value. Like this:

 

@(Html.Kendo().Grid<TelerikMvcApp2.Models.vw_Detail>()
	.Name("grid")
	.AutoBind(false)
	.Columns(col =>
	{
		col.Bound(c => c.SNo);
		col.Bound(c => c.ItemCode);
		col.Bound(c => c.Description);
		col.Bound(c => c.Qty).Width(100).HtmlAttributes(new { style = "text-align:right;" })
			  .ClientFooterTemplate("Total: #= sum #");
		col.Bound(c => c.Unit);
	})
	.Pageable()
	.DataSource(ds =>
		ds.Ajax()
		.Aggregates(ag =>
		{
			ag.Add(p => p.Qty).Sum();
		})
		.PageSize(5)
		.Read(a => a.Action("Read_DetailGrid", "DASH"))
	)
)

 

In case the behavior persists please send us a runnable project where it is replicated so we can examine it.

 

Regards,
Viktor Tachev
Progress Telerik