In Birt Reports - Displaying the most common string value in group.

Question

For this example data:

Index, State, Food
1, CA, Hamburger
2, NY, Lettuce
3, CA, Cheese
4, NY, Lettuce
5, NY, Cheese
6, AR, Cheese

I would like to group by State and show the most common food for each state. So the result for the example should be:

State, Popular Food
CA, Hamburger
NY, Lettuce
AR, Cheese

The problem is I can't find an aggregation that would return the most common string. There is the 'mode' function but it only works on integers. Am I missing Something? Thanks!

Answer 1

The following should work:

• Set up two groups - one group by state, and a sub-group by food
• Add an aggregation, to count at the food sub-group level
• Sort the food sub-group by the count aggregation
• Output state and food in the state group footer