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 by Groups for Sorting a Table

PaulCooper

Using BIRT 3.7.2 I am trying to add an Rank field to a table and sort the data by it. The ranking is to be grouped too. Trying this I run into problems.
 
(Deleted details of this post. I was being dumb and I can get round the issue by sorting by the field I used to rank on!)
 
Regards
 
Paul Cooper

Find more posts tagged with

Comments

mwilliams

Glad you found a solution to your issue! Thanks for updating your thread. Let us know whenever you have questions.

PaulCooper

One thing I did find as part of this was the limit in using aggregate filters in computed columns in the dataset:
 
I would have like to use the current row's data such as: 
row["grouping_field"] == current_row["grouping_field"]
or
 row["salary"] < current_row["salary"] 
If the "current_row" concept exists, however it is named or referenced, I can not find any mention of it. All I can use filters for is absolutes such as: 
row["salary"] > 30000 
Which is very limiting.
 
Anyway to do this?
 
Regards,
 
Paul Cooper

mwilliams

row["salary"] would be referencing the current row of the data set. Maybe I'm not understanding what you're trying to ask.

PaulCooper

<blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="136002" data-time="1430926761">
<div>
row["salary"] would be referencing the current row of the data set. Maybe I'm not understanding what you're trying to ask.
</div>
</blockquote>
 
I can see why you think it is but that is not what I meant.
 
Imagine I have a dataset that returns these four rows:
 
Id Name Team Salary
1 John Smith US 45000
2 Jenny Jones US 50000
3 Brian Black Europe 32000
4 Joe Green Europe 23000
 
If I add an aggregate Computed Column of "Count_Low_Salary" to the data set which is a Count with a Filter of row["Salary"] < 33000 I would get:
 
Id Name Team Salary Count_Low_Salary
1 John Smith US 45000 2
2 Jenny Jones US 50000 2
3 Brian Black Europe 32000 2
4 Joe Green Europe 23000 2
 
This is the same value per record.
 
What I would like to do is be able to create an aggregate such as "Rank_By_Salary_By_Team" which is a Count with a filter of row["Salary"] < current_row["Salary"] && row["Team"] == current_row["Team"] which would give:
 
Id Name Team Salary Rank_By_Salary_By_Team
1 John Smith US 45000 0
2 Jenny Jones US 50000 1
3 Brian Black Europe 32000 1
4 Joe Green Europe 23000 0
 
So for the record with Id = 1 the aggregate "Rank_By_Salary_By_Team" it would use a filter of row["Salary"] < 45000 && row["Team"] == "US"; for the record with Id=3 the filter would be row["Salary"] < 32000 && row["Team"] == "Europe"; etc.
 
This currently some of what can be done via the method I am describing above can be done using a table with groupings where you can create an aggregate in the Bindings but obviously this means you can only use one set of groupings. The other way is to use analytical functions in the SQL but this method can not use Computed Columns to aggregate on.
 
I hope it is clearer what I mean now.
 
Regards,
 
Paul Cooper

mwilliams

To do this in your data set (since there's no concept of grouping in a result set), you'd likely have to store the values of your original data set into an array or arrays and do your computations in a scripted data set. Or run your query through a connection you create in the beforeFactory method in script and store your result set in an array or arrays that you can use in your BIRT data set. In any fashion, if you don't do it in your query or in your grouped table, you'll have to pre-process your result set so that you can iterate the result set in your calculations for your aggregation/computed column. Attached is an example of using a scripted data set. I put your original data in a csv file, so that's attached as well. Run the report design to see the scripted data set contents. The preview of the scripted data set won't work as it requires the original data set to be processed (why the hidden text box bound to "Data Set" is there). Let me know if I'm still misunderstanding.