nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Limit the number of groups (TableGroup) to be displayed

lexthang

I can't believe how bad a memory I have

I know I figured this out before, but then at some point I forgot and now I can't remember what the solution was anymore.

So I have a table with a TableGroup defined. The groups are sorted. How can I limit the number of groups to only the top 3? I've tried defining a topN aggregate on the group, but it doesn't seem to apply the way I want it to. Basically I:
- Have a TableGroup called tg
- Define a TopN aggregate on tg, expression = "1", N = 3. Name that aggregate "MyTop3"
- Add a filter to the TableGroup, set "MyTop3" "IsTrue"
Doesn't work.

I also tried to add a filter to TableGroup for "row.__rownum" <= 3, it doesn't work either.

PS. Elsewhere I had another post where I wanted to limit the number of rows display WITHIN each group. That's another issue, and that is something I do know what to do about.

Thanks!

Find more posts tagged with

Comments

Tubal

In your group settings, you can filter the group based on top N.

Go into your group properties (in your table properties, go into the group tab, double click your group)

There is a filter section in there at the bottom. Filter it on your grouping column.

I just tested it on a group I had that was grouped by a string. The Top 3 gave me the last ones (X-Z). So if you're grouping on a string column, you may want to filter on the Bottom 3.

This is in BIRT 3.7.1. I'm not sure what version you're running.

lexthang

Thanks! I'm wondering about the expression used for filtering. You said we should use the same expression as used to define the group. But I saw elsewhere that we should use the expression used for _sorting_ the groups. I think the latter makes a bit more sense, but maybe it doesn't really matter?

Tubal

I guess it would depend on your table setup.

In my test i was grouping and sorting on the same field.

If your sort field is the same across the group, like you had a customer # and a customer name, you could group on either, and filter on whatever you sorted on.

I can't think of a situation where you would want to sort your groups based on a field that was different across a single group, but if you did, this wouldn't work.

So yes, you are correct, you'd filter on the sort field.

lexthang

OK, that clears things up. I need to clarify that I don't (can't) sort groups based on a scalar value that is different across different rows

That doesn't work anyway. What I have is to have groups sorted on an aggregate, for example a population listing people in a county, grouped by cities. I'd like to sort the groups based on the Average age of people in each city, and then keep only the top 3. In this case I'll specify the expression in the filter as row["AverageAgePerCity"], and then specify top N, etc.

lexthang

I've realized that this does not work when more than one grouping hierarchy is involved. It only works for the first grouping.

Say I have a table of people, first grouped by City, then by Race/Ethnicity.

I'd like to sort the groups by Average of Age, and I want to display the first 4 City only, and within each City, only the first 5 Ethnic groups.

In the City_Group, I add a filter for
Expression=row["Average_Age_grouped_by_City"], op=TopN, value=4

In the Ethnic Group, I add a filter for
Expression=row["Average_Age_grouped_by_Ethnic"], op=TopN, value=5

I've verified that both of the aggregates are defined correctly.

When I run, the report displays only the first four Cities, which is good. But within each city, it lists only 1 Ethnic, instead of 5. It looks like for some reason the Ethnicity is being controlled by the filter for City as well.

If I increase or decreate the TopN for City, then the number of Ethnic displayed will change as well, to match the number of City. I'll always have 1 Ethnic per City.

How do I have a different TopN filter for the sub-groupings?