Count Distinct Aggregate Function

[Guru]

Using a datafilter.filteredsource(datatable.defaultview) as grid itemsource and adding column.AggregateFunctions at runtime is it possible to make the CountFunction count only distinct items (strings or numbers) or do you have to create a custom EnumerableSelectorAggregateFunction inherited class?

If you do have to create the custom aggregate function, how would one go about that in my conditions?

[Vlad]

Hi,

 It will be much easier for you if you use generic aggregate function where you can call Distinct().

Regards,
Vlad
the Telerik team

[Vlad]

Hello,

 Just a quick reply. You can check this demo for more info about generic aggregate function:
http://demos.telerik.com/silverlight/#GridView/ExpressionColumn 

Regards,
Vlad
the Telerik team

[Guru]

Maybe I'm missing your point but it seems to me your solution doesn't apply to my question very well... it appears to be expressions on rows but I need aggregate function for group footers.

If I do use something like your example can you show how it would apply to a datatable as a source..?

Let me map out my goal basically here:
In the example I will show my use of the "CountDistinctFunction" aggregate function I want to use.

(When generating columns for our grid (manually, not autogenerated))

foreach (DataColumn dc in gridData.Columns) // gridData is the DataTable
{
    GridViewBoundColumnBase gc = radGridColumn(dc); //function that returns GridViewBoundColumnBase based on the datatable.column
    foreach (ScheduleColumn c in def.Columns) // custom class of column settings collection
    {
        if (c.ID.Equals(gc.UniqueName))
        {
            // column data
            gc.Header = c.Name;
            // when conditions are right add aggregate (group footer value)
            gc.AggregateFunctions.Add(new CountDistinctFunction() { Caption = c.Name + " (Total): " });
        }
    }
    grid.Columns.Add(gc);
}

[Vlad]

Hello,

 Have you checked the demo code? There is a custom Sum function for the calculated column.

Greetings,
Vlad
the Telerik team

[Guru]

Yes and I was trying to say in my latest response I am not sure how to apply this to my situation.

The function in your sample:
a.) uses object model collection (typeof(Products))
b.) is used for row expressions

The function I need:
a.) uses a datatable (with dynamic columns but the one I want to count is sure to be there)
b.) is used for group footer aggregates

I just can't see how to apply this example to our needs.
Please see my last response code example.
Can you please tell me generally how the example could be applied to my code situation?
You don't have to write the code, just a general idea will do please if you can.

[Vlad]

Hello,

 As I said in my previous reply. The idea is to write your own custom aggregate function to count distinct. 

Greetings,
Vlad
the Telerik team

[Guru]

That's a little more "general" than I was hoping for :)
Since I am using a datatable as a grid itemsource I am just not sure about the idea of the inner workings of the function...

Here is how the example shows it according to a "Products" model:

public class MyTotalFunction : AggregateFunction<Products, double>
{
    public MyTotalFunction()
    {
        this.AggregationExpression = products => products.Sum(p => p.UnitPrice * p.UnitsInStock);
        this.ResultFormatString = "Total: {0:C}";
    }
}

To get this to work with a datatable.defaultview itemsourced radgrid as a GridViewBoundColumnBase AggregateFunction...

When you add AggregateFunction to GridViewColumnBase what object type would the custom AggregateFunction receive the grid itemsource based on a datatable.defaultview ?

Would a lambda aggregationexpression still work for a datatable?

Have you guys ever done custom aggregate functions with grids that have datatable as itemsource? I figured that would be sort of common.

[Vlad]

Hello,

 DataTable.DefaultView is a DataView - collection of DataRowView. Indeed aggregates can work on both DataRow and DataRowView as well. 

Greetings,
Vlad
the Telerik team

[Guru]

Got it.

public class CountDistinctFunction : AggregateFunction<DataRowView, double>
{
    public CountDistinctFunction(string columnName)
    {
        this.AggregationExpression = dv => (from dr in dv select dr.Row.Field<string>(columnName)).Distinct().Count();
        this.ResultFormatString = "Distinct " + columnName + " Count: {0}";
    }
}

[Daniel B.]

How would you make this work via just xaml?

 

We want to keep gridview definition in xaml, especially as we have variable number of gridviews.

 

I was also looking at http://www.telerik.com/forums/footer-sum-of-one-or-more-fields, so far all other approaches ive seen dont seem quite up to par as we also desire the typeless (or at least overloads for 'string','double','date',ect.) and have this on literally hundreds of columns through our application, though most are just slight variations of the core 10+ collection types.

[Daniel B.]

Implemented a code-behind approach to test this, but using code example seen above in itself causes exceptions

 

Currently attempting to activate it using something like this:

 

columns.Add(new GridViewDataColumn()
{
    Header = "Customer",
    DataMemberBinding = new Binding("Customer.CustomerName"),
    IsVisible = (parentEnumVal != ModelEnum.Customer)
});
//cdcol - causes null ref exception following initComponent// columns.Last().AggregateFunctions.Add(new CountDistinctFunction("Customer.CustomerName"));

[Stefan Nenchev]

Hello Daniel,

I have tested the generic approach that Brian Graves has provided in the other thread - Footer Sum One or More Fields. Can you please test with it? You can add the custom aggregate function in XAML like so:

<telerik:GridViewDataColumn DataMemberBinding="{Binding StadiumCapacity}"
 CellStyleSelector="{StaticResource stadiumCapacityStyle}"
 Header="Stadium"
 DataFormatString="{}{0:N0}">
                    <telerik:GridViewDataColumn.AggregateFunctions>
                       <my:CountDistinctFunction></my:CountDistinctFunction>
                    </telerik:GridViewDataColumn.AggregateFunctions>
</telerik:GridViewDataColumn>

Where "my" is the namespace where the custom function is declared.  

Regards,
Stefan Nenchev
Telerik

[Daniel B.]

@Stefan - i will do, but the question was more why the approach listed here caused an exception as it seems cleaner than the other.

I will keep you posted when i get back to aggregates, but still curious about this threads approach

[Stefan Nenchev]

Hi Daniel,

I was not able to reproduce the exception that you are observing. Can you provide more details on your setup and the NullReferenceException you get? Do you use a DataTable as ItemsSource as well? The best case scenario would be to raise a ticket and provide us with a sample project so we can further investigate and advise you accordingly.

Regards,
Stefan Nenchev
Telerik

[Daniel B.]

@Stefen - thanks for that, caught up in other functionality atm, but as promised will keep you posted here and will raise support ticket if unable to implement, coming back here to share working solution if relevant to others