Combine group and count

[Jon]

Hi there,

I have a report with 2 groups and want to use a count from the inner group in the grouping of the outer group.

For example, the raw data is like this:

Customer          Order ID
Customer A        Order 1
Customer B        Order 2
Customer B        Order 3
Customer C        Order 4
Customer C        Order 5
Customer D        Order 6
Customer D        Order 7
Customer D        Order 8

I can group by customer and use the COUNT aggregate function to get the following:

Customer          Order Count
Customer A        1 order
Customer B        2 orders
Customer C        2 orders
Customer D        3 orders           

However, I want to add another group outside this where the grouping is based on the Order Count from the inner group:

Order Count        Customer Count
1 order                    1 customer
2 orders                  2 customers
3 orders                  1 customer

But you can't use an aggregate function in the grouping field when you set a group up.

Do you know of any work arounds?

Thanks, Jon

[Elian]

Hello Jon,

You can create two separate data-sources that will retrieve the data (one for the grouped and one for the rest). For example you can have outer data source which will return the number of customers for each count of orders:
Order Count        Customer Count
1 order                    1 customer
2 orders                  2 customers
3 orders                  1 customer 

and an inner data source that will filter and group the data according to the current context (of the outer data source). 

Regards,
Elian
the Telerik team