Summary Column

[Richard Dublon]

Hi,

I am having some trouble getting my summary column to work.  All I ever see is a 0.

Here is my grid declaration:

    @(Html.EJ().Grid<object>("MasterGrid")

                    .ToolbarSettings(toolBar => toolBar.ShowToolbar().ToolbarItems(items =>

                    {

                        items.AddTool(ToolBarItems.ExcelExport);

                    }))

                    .Mappers(map => map.ExportToExcelAction("Reports/ExportMasterGrid"))

                   .ClientSideEvents(events => events.Load("onLoad").DataBound("dataBound"))

                   .Datasource(ds => ds.URL(@Url.Action("DataSource"))

                   .Adaptor(AdaptorType.UrlAdaptor))

                   .AllowPaging()

                   .AllowSorting()

                   .AllowFiltering()

                   .EnableAltRow()

                   .ShowSummary()

    .SummaryRow(row =>

    {

        row.Title("Count").SummaryColumns(col => { col.SummaryType(SummaryType.Sum).DisplayColumn("LeadCount").DataMember("LeadCount").Add(); }).Add();

    })

        .Columns(col =>

        {

            col.Field("State").HeaderText("State").Add();

            col.Field("MailDate").HeaderText("Mail Date").Format("{0:M/d/yyyy}").Add();

            col.Field("LeadCount").HeaderText("Lead Count").Add();

        })

        .ClientSideEvents(eve => { eve.RowSelected("rowSelected"); })

    )

Here is my c# code:

 public ActionResult DataSource(Syncfusion.JavaScript.DataManager dm)

        {

            int count = 0;

            DataSet ds = new DataSet();

            DataTable dt = new DataTable();

            string JSONString = null;

            using (SqlConnection cn = new SqlConnection(connStr))

            {

                cn.Open();

                //Getting the records based on skip and top value. 

                SqlCommand cmd = new SqlCommand("GetLeadsByState", cn);

                cmd.CommandType = CommandType.StoredProcedure;

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(ds);

                dt = (DataTable)ds.Tables[0];

                JSONString = JsonConvert.SerializeObject(dt, new JsonSerializerSettings

                {

                    ReferenceLoopHandling = ReferenceLoopHandling.Ignore,

                    Formatting = Formatting.Indented

                });

            }

            IEnumerable Data = (from DataRow row in dt.Rows

                                select new DKDatabaseModelClass.GrossLeadsByStateModel

                                {

                                    State = row["State"].ToString(),

                                    MailDate = Convert.ToDateTime(row["MailDate"]),

                                    LeadCount = (int)row["LeadCount"]

                                }).ToList();

            Syncfusion.JavaScript.DataSources.DataOperations operation = new Syncfusion.JavaScript.DataSources.DataOperations();

            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 

            {

                Data = operation.PerformSorting(Data, dm.Sorted);

            }

            if (dm.Where != null && dm.Where.Count > 0) //Filtering 

            {

                Data = operation.PerformWhereFilter(Data, dm.Where, dm.Where[0].Operator);

            }

            count = Data.AsQueryable().Count();

            if (dm.Skip != 0)

            {

                Data = operation.PerformSkip(Data, dm.Skip);

            }

            if (dm.Take != 0)

            {

                Data = operation.PerformTake(Data, dm.Take);

            }

           

            return Json(new { result = Data, count = count }, JsonRequestBehavior.AllowGet);

        }

Thanks for your help.

[Saravanan Arunachalam]

Hi Richard, 

Thanks for contacting Syncfusion’s support. 

We have analyzed your query, the cause of the issue is that you have not handle the aggregation on server side and we have already discussed this query in the following UG documentation link. 

https://help.syncfusion.com/aspnetmvc/grid/summary#handling-aggregation-in-server-side  

Regards, 

Saravanan A. 

[Richard Dublon]

Hi,

I was able to get the summary column to work but it doesn't update when I filter the grid.

Here is my view code:

 @(Html.EJ().Grid<object>("StateGrid")

                    .ToolbarSettings(toolBar => toolBar.ShowToolbar().ToolbarItems(items =>

                    {

                        items.AddTool(ToolBarItems.ExcelExport);

                    }))

                    .Mappers(map => map.ExportToExcelAction("ExportStateGrid"))

                   .ClientSideEvents(events => events.Load("onLoad").DataBound("dataBound"))

                   .Datasource(ds => ds.URL(@Url.Action("LeadsByState"))

                   .Adaptor(AdaptorType.UrlAdaptor))

                   .AllowPaging()

                   .AllowSorting()

                   .AllowFiltering()

                   .FilterSettings(filter => { filter.FilterType(FilterType.Excel); })

                   .EnableAltRow()

                   .ShowSummary()

    .SummaryRow(row =>

    {

        row.Title("Count").SummaryColumns(col => { col.SummaryType(SummaryType.Sum).DisplayColumn("LeadCount").DataMember("LeadCount").Add(); }).Add();

    })

        .Columns(col =>

        {

            col.Field("State").HeaderText("State").Add();

            col.Field("MailDate").HeaderText("Mail Date").Format("{0:M/d/yyyy}").Add();

            col.Field("LeadCount").HeaderText("Lead Count").Add();

        })

        .ClientSideEvents(eve => { eve.RowSelected("rowSelected"); })

    )

Here is my controller code:

            DataResult result = new DataResult();

            DataTable dtResult = new DataTable();

            int count = 0;

            dtResult = GetGridData("spGetLeadsByState");

            IEnumerable Data = (from DataRow row in dtResult.Rows

                                select new DKDatabaseModelClass.GrossLeadsByStateModel

                                {

                                    State = row["State"].ToString(),

                                    MailDate = Convert.ToDateTime(row["MailDate"]),

                                    LeadCount = (int)row["LeadCount"]

                                }).ToList();

            Syncfusion.JavaScript.DataSources.DataOperations operation = new Syncfusion.JavaScript.DataSources.DataOperations();

            List<string> aggregateFields = new List<string>();

            if (dm.Aggregates != null)

            {

                for (var i = 0; i < dm.Aggregates.Count; i++)

                    aggregateFields.Add(dm.Aggregates[i].Field);

                result.aggregate = operation.PerformSelect(Data, aggregateFields);

            }

            if (dm.Search != null)

                Data = operation.PerformSearching(Data, dm.Search);

            if (dm.Where != null)

                Data = operation.PerformWhereFilter(Data, dm.Where, dm.Where[0].Condition);

            result.count = Data.AsQueryable().Count();

            if (dm.Skip != null && dm.Skip != 0)//skiped while rendering checkbox

                Data = operation.PerformSkip(Data, dm.Skip);

            if (dm.Take != null && dm.Take != 0)//skiped while rendering checkbox

                Data = operation.PerformTake(Data, dm.Take);

            result.result = Data;//passed the whole dataSource directly for checkbox rendering

            return Json(result, JsonRequestBehavior.AllowGet);

[Mani Sankar Durai]

Hi Richard, 

We have checked your code example and we are able to reproduce the reported issue. Since you are performing server side filtering operation after the aggregation. So while handling aggregation before filtering it will retrieve the result based on all data. To avoid the issue we suggest you to handling aggregation based on filtered record instead of whole data (set the aggregation code after performing filtering). 

Please refer the code example 

  public ActionResult UrlDataSource(DataManager dm) 

        { 

 

            IEnumerable Data = new NorthwindDataContext().OrdersViews.ToList(); 

            DataResult result = new DataResult(); 

            Syncfusion.JavaScript.DataSources.DataOperations operation = new Syncfusion.JavaScript.DataSources.DataOperations(); 

            List<string> aggregateFields = new List<string>(); 

            if (dm.Search != null) 

                Data = operation.PerformSearching(Data, dm.Search); 

            if (dm.Where != null) 

                Data = operation.PerformWhereFilter(Data, dm.Where, dm.Where[0].Condition);       //perform filtering  

 

            if (dm.Aggregates != null) 

            { 

                for (var i = 0; i < dm.Aggregates.Count; i++) 

                    aggregateFields.Add(dm.Aggregates[i].Field); 

                result.aggregate = operation.PerformSelect(Data, aggregateFields); 

            }           //perform aggregation. 

            result.count = Data.AsQueryable().Count(); 

          

            if (dm.Skip != null && dm.Skip != 0)//skiped while rendering checkbox 

                Data = operation.PerformSkip(Data, dm.Skip); 

            if (dm.Take != null && dm.Take != 0)//skiped while rendering checkbox 

                Data = operation.PerformTake(Data, dm.Take); 

            result.result = Data;//passed the whole dataSource directly for checkbox rendering 

            return Json(result, JsonRequestBehavior.AllowGet); 

        } 

 

Refer the online sample link 

Link: http://mvc.syncfusion.com/demos/web/grid/urladaptor 

Please let us know if you need further assistance. 

Regards, 

Manisankar Durai. 

[Andrias Abadi]

Hi Richard, 

We have checked your code example and we are able to reproduce the reported issue. Since you are performing server side filtering operation after the aggregation. So while handling aggregation before filtering it will retrieve the result based on all data. To avoid the issue we suggest you to handling aggregation based on filtered record instead of whole data (set the aggregation code after performing filtering). 

Please refer the code example 

  public ActionResult UrlDataSource(DataManager dm) 

        { 

 

            IEnumerable Data = new NorthwindDataContext().OrdersViews.ToList(); 

            DataResult result = new DataResult(); 

            Syncfusion.JavaScript.DataSources.DataOperations operation = new Syncfusion.JavaScript.DataSources.DataOperations(); 

            List<string> aggregateFields = new List<string>(); 

            if (dm.Search != null) 

                Data = operation.PerformSearching(Data, dm.Search); 

            if (dm.Where != null) 

                Data = operation.PerformWhereFilter(Data, dm.Where, dm.Where[0].Condition);       //perform filtering  

 

            if (dm.Aggregates != null) 

            { 

                for (var i = 0; i < dm.Aggregates.Count; i++) 

                    aggregateFields.Add(dm.Aggregates[i].Field); 

                result.aggregate = operation.PerformSelect(Data, aggregateFields); 

            }           //perform aggregation. 

            result.count = Data.AsQueryable().Count(); 

          

            if (dm.Skip != null && dm.Skip != 0)//skiped while rendering checkbox 

                Data = operation.PerformSkip(Data, dm.Skip); 

            if (dm.Take != null && dm.Take != 0)//skiped while rendering checkbox 

                Data = operation.PerformTake(Data, dm.Take); 

            result.result = Data;//passed the whole dataSource directly for checkbox rendering 

            return Json(result, JsonRequestBehavior.AllowGet); 

        } 

 

Refer the online sample link 

Link: http://mvc.syncfusion.com/demos/web/grid/urladaptor 

Please let us know if you need further assistance. 

Regards, 

Manisankar Durai. 

Hi sir, 

I used the code for server side aggregation just like the one you provided

 if (dm.Where != null) 

                Data = operation.PerformWhereFilter(Data, dm.Where, dm.Where[0].Condition);       //perform filtering  

 

            if (dm.Aggregates != null) 

            { 

                for (var i = 0; i < dm.Aggregates.Count; i++) 

                    aggregateFields.Add(dm.Aggregates[i].Field); 

                result.aggregate = operation.PerformSelect(Data, aggregateFields); 

            }           //perform aggregation. 

but this thing returned very large amount of data back to the client, it selected the aggregate field on each entries...

this is the result passed back from the controller to the browser

1. {,…}
1. aggregate:[{TotalAmount: 3159800}, {TotalAmount: 916300}, {TotalAmount: 2054400}, {TotalAmount: 1850000},…]
1. [0 … 99]
1. 0:{TotalAmount: 3159800}
2. 1:{TotalAmount: 916300}
3. 2:{TotalAmount: 2054400}
4. 3:{TotalAmount: 1850000}
5. 4:{TotalAmount: 5794200}
6. 5:{TotalAmount: 1683500}
7. 6:{TotalAmount: 296000}
8. 7:{TotalAmount: 6040000}
9. 8:{TotalAmount: 2250000}
10. 9:{TotalAmount: 2625000}
11. 10:{TotalAmount: 29772150}
12. 11:{TotalAmount: 3360000}
13. 12:{TotalAmount: 2294000}
14. 13:{TotalAmount: 902000}
15. 14:{TotalAmount: 1056000}
16. 15:{TotalAmount: 798000}
17. 16:{TotalAmount: 11312320}
18. 17:{TotalAmount: 4345200}
19. 18:{TotalAmount: 1343000}
20. 19:{TotalAmount: 9700000}
21. 20:{TotalAmount: 2752000}
22. 21:{TotalAmount: 1150000}
23. 22:{TotalAmount: 19600000}
24. 23:{TotalAmount: 6612900}
25. 24:{TotalAmount: 0}
26. 25:{TotalAmount: 0}
27. 26:{TotalAmount: 7896000}
28. 27:{TotalAmount: 577600}
29. 28:{TotalAmount: 3700000}
30. 29:{TotalAmount: 4200000}
31. 30:{TotalAmount: 52500000}
32. 31:{TotalAmount: 588000}
33. 32:{TotalAmount: 23058400}
34. 33:{TotalAmount: 1410000}
35. 34:{TotalAmount: 12680000}
36. 35:{TotalAmount: 3850000}
37. 36:{TotalAmount: 3666000}
38. 37:{TotalAmount: 3650000}
39. 38:{TotalAmount: 5327600}
40. 39:{TotalAmount: 19900000}
41. 40:{TotalAmount: 1110000}
42. 41:{TotalAmount: 4526000}
43. 42:{TotalAmount: 8640000}
44. 43:{TotalAmount: 2500000}
45. 44:{TotalAmount: 4200000}
46. 45:{TotalAmount: 2016000}
47. 46:{TotalAmount: 966000}
48. 47:{TotalAmount: 7140000}
49. 48:{TotalAmount: 2816000}
50. 49:{TotalAmount: 2856000}
51. 50:{TotalAmount: 6279000}
52. 51:{TotalAmount: 893760}
53. 52:{TotalAmount: 294000}
54. 53:{TotalAmount: 12807500}
55. 54:{TotalAmount: 14926800}
56. 55:{TotalAmount: 3240000}
57. 56:{TotalAmount: 3290000}
58. 57:{TotalAmount: 15540000}
59. 58:{TotalAmount: 666000}
60. 59:{TotalAmount: 12468100}
61. 60:{TotalAmount: 6764000}
62. 61:{TotalAmount: 580500}
63. 62:{TotalAmount: 11450000}
64. 63:{TotalAmount: 1596000}
65. 64:{TotalAmount: 196000}
66. 65:{TotalAmount: 1017500}
67. 66:{TotalAmount: 17475000}
68. 67:{TotalAmount: 2508000}
69. 68:{TotalAmount: 400000}
70. 69:{TotalAmount: 10819400}
71. 70:{TotalAmount: 704000}
72. 71:{TotalAmount: 13036400}
73. 72:{TotalAmount: 1998000}
74. 73:{TotalAmount: 7150000}
75. 74:{TotalAmount: 3132000}
76. 75:{TotalAmount: 20160000}
77. 76:{TotalAmount: 199750}
78. 77:{TotalAmount: 1680000}
79. 78:{TotalAmount: 1865500}
80. 79:{TotalAmount: 4149900}
81. 80:{TotalAmount: 2290000}
82. 81:{TotalAmount: 1096500}
83. 82:{TotalAmount: 15870000}
84. 83:{TotalAmount: 5838000}
85. 84:{TotalAmount: 13463200}
86. 85:{TotalAmount: 8492500}
87. 86:{TotalAmount: 540000}
88. 87:{TotalAmount: 17020200}
89. 88:{TotalAmount: 298200}
90. 89:{TotalAmount: 5676000}
91. 90:{TotalAmount: 249000}
92. 91:{TotalAmount: 12025000}
93. 92:{TotalAmount: 1900000}
94. 93:{TotalAmount: 967200}
95. 94:{TotalAmount: 0}
96. 95:{TotalAmount: 15190000}
97. 96:{TotalAmount: 3840000}
98. 97:{TotalAmount: 209400}
99. 98:{TotalAmount: 1664000}
100. 99:{TotalAmount: 4680000}
2. [100 … 199]
3. [200 … 299]
4. [300 … 399]
5. [400 … 499]
6. [500 … 599]
7. [600 … 699]
8. [700 … 799]
9. [800 … 899]
10. [900 … 999]
11. [1000 … 1099]
12. [1100 … 1199]
13. [1200 … 1299]
14. [1300 … 1399]
15. [1400 … 1499]
16. [1500 … 1599]
17. [1600 … 1699]
18. [1700 … 1799]
19. [1800 … 1899]
20. [1900 … 1999]
21. [2000 … 2099]
22. [2100 … 2199]
23. [2200 … 2299]
24. [2300 … 2399]
25. [2400 … 2499]
26. [2500 … 2599]
27. [2600 … 2699]
28. [2700 … 2799]
29. [2800 … 2899]
30. [2900 … 2999]
31. [3000 … 3099]
32. [3100 … 3199]
33. [3200 … 3299]
34. [3300 … 3399]
35. [3400 … 3499]
36. [3500 … 3599]
37. [3600 … 3699]
38. [3700 … 3799]
39. [3800 … 3899]
40. [3900 … 3999]
41. [4000 … 4099]
42. [4100 … 4199]
43. [4200 … 4299]
44. [4300 … 4399]
45. [4400 … 4499]
46. [4500 … 4599]
47. [4600 … 4699]
48. [4700 … 4799]
49. [4800 … 4899]
50. [4900 … 4999]
51. [5000 … 5099]
52. [5100 … 5199]
53. [5200 … 5299]
54. [5300 … 5399]
55. [5400 … 5499]
56. [5500 … 5599]
57. [5600 … 5699]
58. [5700 … 5799]
59. [5800 … 5899]
60. [5900 … 5999]
61. [6000 … 6099]
62. [6100 … 6199]
63. [6200 … 6299]
64. [6300 … 6399]
65. [6400 … 6423]
2. count:6424
3. groupDs:null
4. result:[{ID: 8785, OrderNumber: "GBM/SO/032918/0054", SalesUsername: "",…},…]
5. 
   

this obviously caused the query to slow down.. it went up to 1.5~2.5s with 6400 records which is really not practical

is there anyway to simply do the SUM in the server side instead of selecting the value like this?

something like 

result.aggregate = operation.PerformSum(Data, aggregateFields); 

Thanks 

[Farveen Sulthana Thameeztheen Basha]

Hi  Andrias, 

If you want to get the Totalvalue of the summary column, you need to return the value from the serverside as like the above code example and link:- 

https://help.syncfusion.com/aspnetmvc/grid/summary?cs-save-lang=1&cs-lang=csharp 

Please get back to us if you need any further assistance. 

Regards, 

Farveen sulthana T