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; } } }

Ranking aggregated fielsd in a table

mogwai

I can't get this right and feel stupid about it.
I have a data set with lots of rows per health facility, one row per patient.
I created a table with grouping on health facility and created an aggegated field giving me the sum of patients per health facility.
Now I need to find the health facilities with the highest and second highest number of patients.
Using RANK must be the right solution but how do I find the rank of group aggregated fields?

Find more posts tagged with

Comments

pricher

Hi,
 
In the Group definition of your table, add a sort key on the aggregation you created, as shown is the attached screen shot. I have also attached a sample report.

mogwai

Thank you for the quick response Pricher. Unfortunately it doesn't solve my problem. I am compiling a report with lots of text, including dynamic values. One paragraph says "The health facility with the highest number of patients is [dynamic_value] and the health facility with the second highest is [another_dynamic_value]".
So I need to create column bindings that contain the name of the health facilities ranked 1 and 2.
With the aggregation function RANK I can find the ranking of data set rows, but not of aggregated values at group level.

wwilliams

Can you do it in SQL?
Something like
 
<div> SELECT facility ,cnt, </div>
<div>RANK() OVER (ORDER BY cnt DESC) AS TheRank</div>
<div>FROM (</div>
<div> select COUNT(*) cnt, facility from pr group by facility </div>
<div>) as a</div>
<div>order by 3 asc</div>

mogwai

I guess I could. It means running an additional query on the same tables. I really thought BIRT would allow this at group level in a table. I just need a double aggregation (RANK(COUNT)) but I can't figure out how to do this with BIRT aggregation.

pricher

Hi again,
 
It's a bit convoluted, but you can do it in BIRT. You need tho create the following bindings in your table:

mogwai

This is exactly what I needed. I didn't know you could aggregate at the table level and put the aggregation back to the group level. Thanks a lot P.

mogwai

I'm not out of the woods yet. I have the table with the ranking for each health facility, but I can't isolate the health facility in second place for my dynamic text. I now need a column binding at table level that isolates that health facility.

pricher

Is it possible to get a copy of your design? That's going to help my understanding.
 
P.

mogwai

It's not necessary. The solution is in your report design but I overlooked it. Everything I need is there. Thanks again.

pricher

Great!

shamo

Pricher, why did you have to create a binding column Rank1, Rank2, Rank1 aggr and Rank2 Aggre? Just Aggregation_1 with Rank solves it for me. Is there a reason for the way you went about it?

pricher

This was needed to display the 1st and 2nd product line by rank in the table header.
 
P.