[Solved] Multicolumn aggregates where columns only known at runtime

[Jaco]

Hi, I have checked out the forum re handling calculation of group aggregates on values from more than one column. In the examples shown the values are taken from the underlying object properties. In my case I retrieve the data and use the lightwight datatable to bind to the radgridview. The properties of the dynamic object is only known at runtime. I like to create a custom aggregate to do a calculation based on two or more properties. Is there a way to create a custom aggregate and supply the properties/columns as parameters to the aggregate function? Any help will be appreciated.

[Vlad]

Hi,

 Generally the lightweight DataTable will generate dynamically classes with normal properties and you can use aggregate functions in exactly the same way as with normal binding - you need however to add these functions runtime when you know what properties you want to use.

You can use SourceField property to specify single property for the function or you can extend the function with more property names if you want - please check our custom aggregates demo more info about this.

Kind regards,
Vlad
the Telerik team

[Jaco]

Hi Vlad,

Thanks for your response. I did previously checked out the custom aggregate demo and based my work on this. I will give some code examples of how I go about. This will probably make clear the problem I have.

Here is the code behind when autogenerating the columns based on the datatable object. Please note that the datatable is generated based on some SQL query and will differ based on the query that is called, meaning that the properties is not know at design time, but only at runtime based on some meta data objects.

private void radGridViewResults_AutoGeneratingColumn(object sender, GridViewAutoGeneratingColumnEventArgs e)
        {
            //ParentDataTable is an added property to the Dynamic object
            // that holds reference to the parent DataTable
            // Must not display this column
            if (e.Column.UniqueName == "ParentDataTable")
            {
                e.Cancel = true;
            }
            else
            {
                ReportColumnEC reportColumnEC = null; //this is the object that contains metadata about the column
                var dataTable = this.radGridViewResults.ItemsSource as Telerik.Data.DataTable;
                var dataCols = dataTable.Columns;

                foreach (var item in dataCols)
                {
                    //Go through all the columns and get the metadata object if available
                    if (item.ColumnName == e.Column.UniqueName)
                        //Added property DataObject on the DataColumn (of the DataTable) object. DataObject is a reference to
                        // the metadata object for that column
                        reportColumnEC = (ReportColumnEC)item.DataObject;
                }

                if (reportColumnEC != null)
                {
                    //based on metadata object change the column properties

                    if (reportColumnEC.ColumnCaption.Length > 0)
                        e.Column.Header = reportColumnEC.ColumnCaption;

                    if (reportColumnEC.ColumnHidden)
                        e.Column.IsVisible = false;
                    else
                        e.Column.IsVisible = true;

                    //based on metadata object determine the function to use
                    if (reportColumnEC.ColumnSubTotalFunction.ToLower() == "count")
                    {
                        CountFunction countF = new CountFunction();
                        e.Column.AggregateFunctions.Add(countF);
                    }
                    else if (reportColumnEC.ColumnSubTotalFunction.ToLower() == "count")
                    {
                        SumFunction sumF = new SumFunction();
                        e.Column.AggregateFunctions.Add(sumF);
                    }
                    else if (reportColumnEC.ColumnSubTotalFunction.ToLower() == "expression")
                    {
                        //assign the custom aggregate function to the column
                        ExpressionFunction expressionF = new ExpressionFunction();
                        e.Column.AggregateFunctions.Add(expressionF);
                    }
                }
            }

This all works fine and I can have count and sum aggregate functions.
The ExpressionFunction also works fine if I know the fields at design time:

public static decimal Expression<TSource>(IEnumerable source)
        {

            decimal sum1 = 0;
            decimal sum2 = 0;
            foreach (DynamicObject item in source)
            {
                //This works, but then I need to know that "Sales" and "Cost"
                // are properties at design time
                // Need to be able to get "Sales" at "Cost" from metadata object
                sum1 += (decimal)item.GetValue<object>("Sales");
                sum2 += (decimal)item.GetValue<object>("Cost");
            }

            return sum1 / sum2 * 100;

        }

If I can get the column name (maybe SourceField) that is calling this function then I can do something like this:

public static decimal Expression<TSource>(IEnumerable source)
        {

            string sourceColumnName = "GPPercentage"; //Get this from source field


            DynamicObject item; //...Get the first item in source and assign to item.
            DataTable parentDataTable = (DataTable)item.GetValue<object>("ParentDataTable");

            //Get the metadata object based on source column name
            ReportColumnEC reportColumnEC = null;
            var dataCols = parentDataTable.Columns;
            foreach (var col in dataCols)
            {
                if (col.ColumnName == sourceColumnName)
                    reportColumnEC = (ReportColumnEC)col.DataObject;
            }

            //reportEC will now have information I can use to do the neccessary calculations..
            // Will not go into details now..
            decimal sum1 = 0;
            decimal sum2 = 0;
            foreach (DynamicObject itemSource in source)
            {

                sum1 += (decimal)itemSource.GetValue<object>("Sales");
                sum2 += (decimal)itemSource.GetValue<object>("Cost");
            }

            return sum1 / sum2 * 100;

        }

Will really appreciate if you can help me out with this. Hopefully there is a way to get the sourceField/column name. Maybe another method signature that exposed the sourceField to the function?

[Maya]

Hello Jaco Jordaan,

I have already replied on the forum thread you posted on the same topic. Please take a look at it and let us know whether it suits your needs.
 

Greetings,
Maya
the Telerik team

[Jaco]

Hi Maya,

As mentioned in my support ticked, I did find a way to handle this. Just created a lot of aggregate functions doing the same thing, with only difference is that each aggregate method knows the column number that called it. I can get the meta data for the column based on this. On my creation of the columns in the autogeneratingcolumns event I then make sure that the column gets the correct aggregate function assigned to.

Thanks,
Jaco