We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

DataGrid aggregates

Thread ID:

Created:

Updated:

Platform:

Replies:

143663 Mar 30,2019 03:22 PM UTC Apr 24,2019 10:59 AM UTC ASP.NET Core - EJ 2 12
loading
Tags: DataGrid
Costa
Asked On March 31, 2019 03:49 AM UTC

Hi. In Thread 136540 a request was made about the non-working code from Syncfusion help, in particular about the "customAggregate" tag in the DataGrid ASP.NET Core - EJ 2. More than a year has passed, the code from the help is still not working. I have 2 questions: 

1) How and when do you plan to eliminate this discrepancy (if you are planning)?
2) If I use UrlAdaptor and Paging, then aggregate values ​​are displayed only for the records that are on the page. How to make so that the totals are displayed for all records?

 Thanks.

Thavasianand Sankaranarayanan [Syncfusion]
Replied On April 1, 2019 09:25 AM UTC

Hi Costa, 

Greetings from Syncfusion support. 

Query 1 : A request was made about the non-working code from Syncfusion help, in particular about the "customAggregate" tag 
We are sorry for the inconvenience caused. We have already logged this as a documentation improvement task, and the corrections are in progress. We have planned to refresh these changes in our upcoming release. Until then we appreciate your patience. We also request you to follow our new documentation link below and replace only highlighted code shown below to render a Grid with custom aggregate,  
 
 
<e-aggregate-column field="ShipCountry" type="Custom" footerTemplate="Brazil Count:${Custom}" customAggregate="@("customAggregateFn")"></e-aggregate-column> 
 

 
Query 2 : If I use UrlAdaptor and Paging, then aggregate values ​​are displayed only for the records that are on the page. How to make so that the totals are displayed for all records? 
We would like to inform you that, by default when you are using a custom aggregate in Grid, only the data in the current view will be taken for calculating the aggregate. This is the default behavior of custom aggregate. When you use the default aggregate, then aggregate  will be calculated based on the entire data in all the pages in Grid. 

From the screenshot we could see that you would like to display “2000” in Total. So we suggest you to use the default aggregate, which calculates the aggregate based on entire data in Grid. Please refer the documentation and code example below, 

 
<e-aggregate-column field="ShipCountry" type="Count" footerTemplate="Count:${Count}"></e-aggregate-column> 



If we have misunderstood your query, we need more details to further proceed on this. So could you please share with us the detailed description of your exact to further proceedon this and provide you a solution as early as possible. 

Regards, 
Thavasianand S. 


Costa
Replied On April 1, 2019 12:06 PM UTC

"... we suggest you to use the default aggregate, which calculates the aggregate based on entire data in Grid. Please refer the documentation and code example below.
<e-aggregate-column field="ShipCountry" type="Count" footerTemplate="Count:${Count}">e-aggregate-column> "


If this method worked, I would not ask such a question here. But it does not work. To make it easier to understand, I enclose an example. See Attachment.

Attachment: AspNetCore3_EJ2_aa504d24.zip

Pavithra Subramaniyam [Syncfusion]
Replied On April 3, 2019 12:40 PM UTC

Hi Costa, 
 
Thanks for the sample. 
 
From your sample we found that you did handle the aggregation in server side. We must need to handle the aggregate action in server side while working with remote data only. Please refer the following code snippet for further assistance,  
 
public IActionResult UrlDatasource([FromBody]DataManagerRequest dm) 
        { 
 
            IEnumerable DataSource = order; 
            DataOperations operation = new DataOperations(); 
 
            if (dm.Search != null && dm.Search.Count > 0) 
            { 
                DataSource = operation.PerformSearching(DataSource, dm.Search);  //Search 
            } 
            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 
            { 
                DataSource = operation.PerformSorting(DataSource, dm.Sorted); 
            } 
            if (dm.Where != null && dm.Where.Count > 0) //Filtering 
            { 
                DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator); 
            } 
            List<string> str = new List<string>(); 
            if (dm.Aggregates != null) 
            { 
                for (var i = 0; i < dm.Aggregates.Count; i++) 
                    str.Add(dm.Aggregates[i].Field); 
            } 
            IEnumerable aggregate = operation.PerformSelect(DataSource, str); 
            int count = DataSource.Cast<Orders>().Count(); 
            if (dm.Skip != 0) 
            { 
                DataSource = operation.PerformSkip(DataSource, dm.Skip);   //Paging 
            } 
            if (dm.Take != 0) 
            { 
                DataSource = operation.PerformTake(DataSource, dm.Take); 
            } 
            return dm.RequiresCounts ? Json(new { result = DataSource, count = count, aggregate = aggregate }) : Json(DataSource); 
        } 
 
Please let me know if you have any concern. 
 
Regards, 
Pavithra S. 
 


Costa
Replied On April 4, 2019 03:33 AM UTC

Thanks, it works fine. This is the answer that was needed.

Hariharan J V [Syncfusion]
Replied On April 5, 2019 06:31 AM UTC

Hi Costa, 

Thanks for your update.  
  
We are happy to hear that the provided solution helped you.   
  
Please contact us if you need any further assistance. As always, we will be happy to assist you.   
  
Regards, 
Hariharan 




Costa
Replied On April 10, 2019 03:27 AM UTC

Hi. I have one more question. I would like to adjust the aggregate, using UrlAdaptor, so that it display the count of unique values, so to speak, "DistinctCount" or "UniqueCount":


Currently, I do not see such a possibility when using UrlAdaptor, since the built-in aggregate types do not contain "DistinctCount or "UniqueCount", and custom aggregates allows only records displayed on the current page to be processed.

Hariharan J V [Syncfusion]
Replied On April 10, 2019 12:03 PM UTC

Hi Costa, 

We have analyzed your requirement. We suggest you to handle the distinct values from the server side. So that, only the distinct values will be returned as aggregates. We suggest you to use the below codes in your application to achieve this requirement. 

 
            List<string> str = new List<string>(); 
            //As aggregate is to be displayed for “CustomerID” column, we have fetched distinct from “CustomerID” field  
            IEnumerable aggdata = OrdersDetails.GetAllRecords().GroupBy(o => o.CustomerID).Select(o => o.FirstOrDefault());     
            if (dm.Aggregates != null) 
            { 
                ... 
           } 
            IEnumerable aggregate = operation.PerformSelect(aggdata, str);   //Provide the distinct data(aggdata) as argument instead of “DataSource” 


We are also attaching the sample for your convenience, please download the sample from the link below, 

Please get back to us if you need further assistance. 

Regards, 
Hariharan 


Costa
Replied On April 11, 2019 01:26 AM UTC

Not bad. But how at the same time on the other column to get the full count?



By the way, if the column type is string, you cannot format the aggregate. Of course, in most cases this is not required, but if, for example, it is necessary to format the aggregate in a text column with a separation of groups of digits, then this will not be possible. But, as usual, there is a workaround: to display the aggregate of a numeric column in a text column, formatting them as necessary. So it is not critical.


Costa
Replied On April 11, 2019 05:36 AM UTC

Okay, I'll answer my own question. I found that the dataSource argument of the method PerformSelect (IEnumerable dataSource, List <string> select) does not have to be the same type as the data type in the grid. It is only important that the names of the columns for displaying the aggregates coincide with the names of the dataSource columns of the grid. At the same time, the option with an grouping by constant (in this case it is "any") and using anonymous type is quite functional:

HomeController.cs:
...
        public IActionResult UrlDatasource([FromBody]DataManagerRequest dm)
        {
    ...
            IEnumerable aggdata = DataSource.Cast<OrdersDetails>()
                .GroupBy(e => "any")
                .Select(e => new
                {
                    CustomerID = e.Select(o => o.CustomerID).Distinct().Count(),
                    ShipCity = e.Count(),
                    Freight = e.Average(o => o.Freight),
                });

            var aggregate =
                dm.Aggregates != null ? operation.PerformSelect(aggdata, dm.Aggregates.Select(e => e.Field).ToList()) : null;
    ...

Index.cshtml:
...
                <e-aggregate-columns>
                    <e-aggregate-column field="CustomerID" type="Sum" footerTemplate="Cust Count: ${Sum}"></e-aggregate-column>
                    <e-aggregate-column field="ShipCity" type="Sum" footerTemplate="Count: ${Sum}"></e-aggregate-column>
                    <e-aggregate-column field="Freight" type="Sum" footerTemplate="Avg: ${Sum}" format="C"></e-aggregate-column>
                </e-aggregate-columns>
...


With this method, we will always receive from the server only one record with totals (therefore, e-aggregate-column type = "Sum" in all columns with aggregates) and correctly calculates the aggregates when the filter changes:



As a result, we have the following:


Currently, I still use this method until a better solution appears. The only flaw that I found at the moment is that when exporting to Excel, instead of these calculated totals, some other incorrect ones are sent:


If I'm wrong in something, you can correct me.

Dhivya Rajendran [Syncfusion]
Replied On April 14, 2019 07:06 AM UTC

Hi Costa, 

Sorry for the delay response, 

Excel sheet perform Aggregation on the string type column with Count property of Aggregate only, this is cause of the defect. By default, you can achieve the all the aggregates on number type columns and if you try to all the aggregates on the string type column return 0 except the “Count” property in Excel exporting. In the below code example, you can achieve the aggregate on the string type column with Count property. 
[index.cshtml] 
<div> 
    <ejs-grid id="Grid" allowPaging="true" allowExcelExport="true" toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update","ExcelExport" })" toolbarClick="toolbarClick" actionBegin="actionBegin" load="load"> 
        <e-data-manager url="/Home/UrlDatasource/" adaptor="UrlAdaptor" crossdomain="true"></e-data-manager> 
        <e-grid-editsettings allowEditing="true" allowDeleting="true" allowAdding="true" mode="Normal"></e-grid-editsettings> 
        <e-grid-aggregates> 
            <e-grid-aggregate> 
                <e-aggregate-columns> 
                    <e-aggregate-column field="CustomerID" type="Count" footerTemplate="Cust Count: ${Count}"></e-aggregate-column//String type column 
                    <e-aggregate-column field="ShipCity" type="Count" footerTemplate="Count: ${Count}"></e-aggregate-column// String type column 
                    <e-aggregate-column field="Freight" type="Sum" footerTemplate="Avg: ${Sum}" format="C"></e-aggregate-column> // Number type column 
                </e-aggregate-columns> 
            </e-grid-aggregate> 
        </e-grid-aggregates> 
        <e-grid-columns> 
            <e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" validationRules="@(new { required=true})" textAlign="Right" width="120"></e-grid-column> 
            .   .   .   . 
       </e-grid-columns> 
    </ejs-grid> 
</div> 

   
Please refer the below sample and documentation for more information. 



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

Regards, 
R.Dhivya. 
 


Costa
Replied On April 17, 2019 10:06 AM UTC

Hi. Your reply does not answer the question. I have to repeat the question.

1)  Costa
Replied On April 10, 2019 03:27 AM UTC

Hi. I have one more question. I would like to adjust the aggregate, using UrlAdaptor, so that it display the count of unique values, so to speak, "DistinctCount" or "UniqueCount":

2) Costa
Replied On April 11, 2019 01:26 AM UTC
 
Not bad. But how at the same time on the other column to get the full count?



Hariharan J V [Syncfusion]
Replied On April 24, 2019 10:59 AM UTC

Hi Costa, 

Thanks for your update. 

We have analyzed your query and created a sample based on your requirement. In the below sample, we have used customAdaptor and return the aggregate data(all records) in server side based on the data we have performed distinct(unique) in client side using DataUtil Distinct method of data manager. 

Also we have used custom aggregate for CustomerID field to display the distinct value. Please refer the below code example and updated sample for more information. 

<div> 
    <ejs-grid id="Grid" allowPaging="true" created="created"   toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update" })" actionBegin="actionBegin" load="load"> 
        <e-grid-editsettings allowEditing="true" allowDeleting="true" allowAdding="true" mode="Normal"></e-grid-editsettings> 
        <e-grid-aggregates> 
            <e-grid-aggregate> 
                <e-aggregate-columns> 
                    <e-aggregate-column field="OrderID" type="Count" footerTemplate="Sum: ${Count}"></e-aggregate-column> 
                    <e-aggregate-column field="CustomerID" type="Custom" footerTemplate="Sum: ${Custom}" customAggregate="@("customAggregateFn")"></e-aggregate-column> 
                </e-aggregate-columns> 
            </e-grid-aggregate> 
        </e-grid-aggregates> 
        <e-grid-columns> 
            <e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" validationRules="@(new { required=true})" textAlign="Right" width="120"></e-grid-column> 
            <e-grid-column field="CustomerID" headerText="Customer Name" validationRules="@(new { required=true})" width="150"></e-grid-column> 
            . . . . . 
   </ejs-grid> 
</div> 
 
<script> 
    var aggregateData; 
    function created(args) { 
         //extending the default UrlAdaptor  
        class CustomAdaptor extends ej.data.UrlAdaptor { 
            processResponse(data, ds, query, xhr, request, changes) { 
                aggregateData = ej.data.DataUtil.distinct(data.aggregate, 'CustomerID', true); 
                return super.processResponse(data, ds, query, xhr, request, changes); 
            } 
        } 
        var grid = document.querySelector('#Grid').ej2_instances[0]; 
        grid.dataSource = new ej.data.DataManager({ 
            url:"/Home/UrlDatasource/", 
            adaptor: new CustomAdaptor() 
        }); 
    } 
 
    function customAggregateFn(data) { 
        return aggregateData.length; 
    } 
</script> 
 





Regards, 
Hariharan 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;