Table Summary totals for dataGrid using DynamicModel

[Jonathan Cranford]

How might I create totals for each numeric column on a dataGrid created from dynamic data?

I am using the Dictionary<string, object> method based on this example: 

     https://www.syncfusion.com/kb/7828/how-to-load-sfdatagrid-dynamically-with-json-data-without-poco-classes

I have tried various things in a custom aggregate class but have not been able to get it to display totals.

[Pradeep Kumar Balakrishnan]

Hi Jonathan, 

 

Thank for contacting Syncfusion support. 

 

We have checked your query “Table summary Sum aggregate is not working in Dynamic data” . we are currently validating the reason for this issue. We will validate and update you further details in two days March 6,2019. We appreciate your patience until then. 

 

Regards, 

Pradeep Kumar B 

[Pradeep Kumar Balakrishnan]

Hi Jonathan. 

 

Thank you for your patience. 

 

Still we are working on this issue “Table summary Sum aggregate is not working in Dynamic data”. we will update the solution in two business days March 11,2019. We appreciate your patience until then. 

 

Regards, 

Pradeep Kumar B 

[Pradeep Kumar Balakrishnan]

Hi Jonathan, 

 

Thank you for your patience. 

 

We have prepared the sample to achieve your requirement “How to display Total value for an column in Dynamic model” in Xamarin forms. We have achieved this requirement using Customer summary aggregate. Please refer the following code snippet for reference. 

 

Code snippet: 

public class CustomAggregate : ISummaryAggregate 

{ 

    public CustomAggregate() 

    { 

    } 

    public double DynmaicValue { get; set; } 

    public Action<System.Collections.IEnumerable, string, PropertyInfo> CalculateAggregateFunc() 

    { 

        return (items, property, pd) => 

        { 

            var enumerableItems = items as IEnumerable<DynamicModel>; 

            var collection = new ObservableCollection<double>(); 

            foreach (var item in enumerableItems) 

            { 

                var dict = item.Values.Where(x => x.Key == "OrderID"); 

                foreach (var value in dict) 

                { 

                        collection.Add(double.Parse(value.Value.ToString())); 

                } 

            } 

            if (pd.Name == "DynmaicValue") 

            { 

                this.DynmaicValue = collection.Sum(); 

            } 

        }; 

    } 

} 

 

//Adding Table summary to the row. 

GridTableSummaryRow summaryRow = new GridTableSummaryRow(); 

summaryRow.Title = "Toal : {TableSummary}"; 

summaryRow.Position = Position.Top; 

summaryRow.ShowSummaryInRow = true; 

summaryRow.SummaryColumns.Add(new GridSummaryColumn 

{ 

    Name = "TableSummary", 

    CustomAggregate = new CustomAggregate(), 

    MappingName = "OrderID", 

    Format = "{DynmaicValue}", 

    SummaryType = Syncfusion.Data.SummaryType.Custom 

}); 

grid.TableSummaryRows.Add(summaryRow); 

 

We have attached the sample for your reference in following location. 

 

Sample link : http://www.syncfusion.com/downloads/support/forum/143046/ze/DataGridDemo2125951099 

 

Please let us know if you require any further assistance on this. 

 

Regards, 

Pradeep Kumar B 

 

 

[Jonathan Cranford]

Thanks very much for the code sample. I notice that you are hard-coding the field name 'OrderID'. In our case, we don't know the name of the fields that we want to aggregate before runtime. We download JSON data and parse it into a DynamicModel for this reason, and then loop through each element in the first 'row' of data in order to add the columns. We also need to sum more than one column. Our JSON field names are preceded by 'dec' for decimal or 'str' for string, to save having to parse the values to check what type they are.

I have tried to amend the code to calculate based on dynamic columns. Within the CalculateAggregateFunc() method, I can see that each Collection.Sum() is being correctly calculated. However, when the table is displayed, all the summary totals are set to that of the last calculated column. It seems like the whole table uses a single instance of CustomAggregate rather than one for each column, so the totals are overwritten.

ObservableCollection<DynamicModel> data = GetJSONDataFromWeb();

Dictionary<string, object> dictOfValues = (Dictionary<string, object>)data[0].Values;

// Loop through all columns and add them to the table

             foreach (string key in dictOfValues.Keys)

                {

                    if (key.Substring(0, 3) != "dec")

                    {

                        Table.Columns.Add(new GridTextColumn()

                        {

                            HeaderText = key.Substring(3),

                            MappingName = "Values[" + key + "]",

                            CellTextSize = 10

                        });

                    }

                    else

                    {

                        Table.Columns.Add(new GridNumericColumn()

                        {

                            HeaderText = key.Substring(3),

                            MappingName = "Values[" + key + "]",

                            CellTextSize = 10,

                         // we also specify a dynamic number of decimal places here depending on data, not shown

                        });

                            summaryRow.SummaryColumns.Add(new GridSummaryColumn()

                            {

                                Name = "Total",

                                MappingName = "Values[" + key + "]",

                                Format = "{DynamicValue}",

                                CustomAggregate = new CustomAggregate(),

                                SummaryType = SummaryType.Custom

                            });

                    }

                }

                Table.TableSummaryRows.Add(summaryRow);

  // Within CustomAggregate class...

   public Action<System.Collections.IEnumerable, string, PropertyInfo> CalculateAggregateFunc()

    {

        return (items, property, pd) =>

        {

            var enumerableItems = items as IEnumerable<DynamicModel>;

            var collection = new ObservableCollection<double>();

            var propertyName = property.Substring(0, property.Length - 1).Substring(7);

                // turns values[columnName] into columnName

            Console.WriteLine("CALCULATING SUM OF: " + propertyName + " / " + pd.Name);

            foreach (var item in enumerableItems)

            {

                var dict = item.Values.Where(x => x.Key == propertyName);

                foreach (var value in dict)

                {

                    collection.Add(double.Parse(value.Value.ToString()));

                    Console.WriteLine("VALUE: " + value.Value.ToString() + " RUNNING TOTAL: " + collection.Sum());

                }

            }

            if (pd.Name == "DynamicValue")

            {

                this.DynamicValue = Math.Round(collection.Sum(),2);

            }

        };

    }

[Jonathan Cranford]

Pradeep,

I managed to solve this issue, the problem was I was using the Name 'Total' for every summary column. Once I made the 'Name' field unique, the totals displayed correctly.

[Pradeep Kumar Balakrishnan]

Hi Jonathan, 

 

Thank you for the update. We are happy that you are requirement is achieved using given solution.  

 

Please let us know, if you need any further assistance.  

 

Regards, 

Pradeep Kumar B