Community
Participate
Working Groups
We have a Top N function. We need a function that aggregates the rest of the rows that do not fall into the top n.
Hi, Can you provide more details on your proposed function? Thanks. Lin
Consider a report that groups order $ sale amount information by customers. Let us assume that there are 20 customers in the database. In this table, lets say we limit the group to the top 5 spending customers. I.e in the group definition we define a filter that limits the # of customers listed to the top 5 customers by order $ sale amount. When you view the report you get a list of the top 5 customers by $ of the order sale amount. I also want to aggregate the orders $ sale amounts for all the remaining 15 customers into 1 "remainder" row. Perhaps I could insert another group header row below the first one that lists the top 5 customers. In this second group header row I could place an aggregation for the remaining customers $ sale amount. The exact details of the GUI would need to be decided but I hope I am conveying the usage scenario here. So the final table output would look like: 1 Cust1 $100 2 Cust2 $90 3 Cust3 $80 4 Cust4 $70 5 Cust5 $60 Others $120
This is not only a remainder function. It is a complete new style of grouping. Need to investigate.
Lin, This may not need to be that complicated. Can we add this functionality to the Filter dialog where you would apply the Top N filter? In the example below, after grouping, I would need to use the table level filter to limit the results to just the top 5 customers. In this same dialog, we could look at adding the appropriate UI gestures to allow me to aggregate the remainder. Another suggestion is to take a look at the aggregation builder where we have various functions such as sum, count, is-top-n functions, etc. We could add a remainder function here that would work in conjunction with sum.
(In reply to comment #4) > Lin, > > This may not need to be that complicated. Can we add this functionality to the > Filter dialog where you would apply the Top N filter? > > In the example below, after grouping, I would need to use the table level > filter to limit the results to just the top 5 customers. In this same dialog, > we could look at adding the appropriate UI gestures to allow me to aggregate > the remainder. Another suggestion is to take a look at the aggregation builder > where we have various functions such as sum, count, is-top-n functions, etc. We > could add a remainder function here that would work in conjunction with sum. > Hi Krishna, I do not think this can be apply as a filter. The filter is used to decide which rows should be appeared in final results, and only those rows can be aggregated.None do I think think this works in Aggregation builder for all the aggregations are against all the group instances rather than "some" of group instances(as "remainder") The key problem to support your use scenario is that, in current grouping support in BIRT, we make groups depend on the data value of a row solely. However, what you requested is actually a "grouping" in which the groups are decided by not only the data values in a row, but also the context of it, that is, all the other rows in target Result Set. This is completely missing in BIRT, and the adding of it need extra efforts. Thanks. Lin