Ranking

Hi,

I was thinking about creating rankings in BIRT. One way is in the SQL but, is it possible to do it in a way different of the SQL?

Thanks in advance

Find more posts tagged with

Comments

mwilliams

Hi pavlicic,

What do you mean by ranking? You want to rank rows in a table based off of a column value?

Lived_Ip

I don't see why you couldn't, but I can't see how it would be even 1/1000th as fast as using a query.

I'd probably do something like the following, if I had to as a last resort:

initialize:
var rankingArray = new Array(0);
reportContext.setPersistentGlobalVariable("rankingArray", rankingArray.toString());

Element onCreate :

var rankingArray = new Array(reportContext.getPersistentGlobalVariable("rankingArray").split(","));
//implement a sort function
newArray = quickSort(rankingArray, row["value"], row.__rownum);
reportContext.setPersistentGlobalVariable("rankingArray", newArray.toString());

Element onRender:
var rankingArray = new Array(reportContext.getPersistentGlobalVariable("rankingArray").split(","));
this.text = rankingArray[row.__rownum].toString();

...or something like that. Maybe there's an easier way, but again unless you're doing this for a very small number of rows, I would think the overhead is going to get high fast. I would stick with doing it in the query.

pavlicic

Hi everybody,

Michael: yes, i want to create a rank (maybe ranking was not the correct word, apologizes).

Lived_Ip: i was just trying to know if it was possible to do the rank in an "easy way" (not programming), but i think it is going to be difficult. I will keep doing ranks I need in query.

Thanks a lot for your answers

mwilliams

pavlicic,

In your dataSet, you can create a computed column based off of another column's value. If your data comes in sorted, you can do a ranking of 1 to numRows by doing an aggregation of running count. If you want to base the rank off of another column's value, you can create an expression that checks for certain criteria.

srinivast

Hi, 
 
Currently i am also working on to get the ranks displayed. I can able to display the the ranks But, I have a situation like if 3 students got the same marks the 3 students should have the same rank. 
 
For Ex: 
Student Marks Rank 
AAA 89 1 
BBB 88 2 
CCC 88 2 
DDD 88 2 
EEE 87 3 
 
Kindly help me out How i can achieve the same ranks. 
 
I am using BIRT 2.5.1 version. 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="38805" data-time="1225730574" data-date="03 November 2008 - 09:42 AM">
pavlicic, 
 
 
In your dataSet, you can create a computed column based off of another column's value. If your data comes in sorted, you can do a ranking of 1 to numRows by doing an aggregation of running count. If you want to base the rank off of another column's value, you can create an expression that checks for certain criteria. </blockquote>

mwilliams

You could use variables to store the last rank value and the last score value on your ordered dataSet. In your computed column, you'd only increase the rank value if the score was different, then you'd set the variable values to the new current rank and new current score.

Hope this helps.

srinivast

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="68501" data-time="1284662663" data-date="16 September 2010 - 11:44 AM">
You could use variables to store the last rank value and the last score value on your ordered dataSet. In your computed column, you'd only increase the rank value if the score was different, then you'd set the variable values to the new current rank and new current score. 
 
Hope this helps. </blockquote>
 
Hi Thanks a lot for your response, 
 
I have tried to use variables.But, For the first row the condition or assignments not executing. 
Ex: 
I have initailzed score and rank as 0 in report initialize script and for the rank field i have written 
 
if(score != row["theory_marks"]) 
{ 
rank++; 
score = row["theory_marks"]; 
 
} 
 
For the first record it is ot executing. 
 
To observe this when i tried to print row["theory_marks"] its printed like in the attachment. 
 
Kindly let me know where i have done the wrong here. 
 
It would be most appreciated if you can provide any example.

mwilliams

Srinivast,

Take a look at the attached example. Ranks 23 and 92 repeat. All of the code is done in the computed column expression builder except initializing the variables I use in the beforeOpen method of the dataSet.

Let me know if you have questions.

madereg

Hi MWilliams,

I too am working on a report where I need to add ranking on multiple different columns within my Data Set. I see that BIRT has the built in RANK Aggregation. I have it kind of working for the first column but not totally. The fields I have a question about are the "Expression", "Ascending" and "Filter" fields in the Edit Computed Column dialog box. In my particular case for the "Expression" I entered row["ALL_CLAIMS"]. However, I'm not sure what format to enter the value for the "Ascending" field function or if I even need something in the "Filter" function. Can you point me to where I can find some examples or documentation on how to use the RANK function within BIRT?

Thanks,
Mike

mwilliams

Hi Mike,

Expression: The field you want to rank.

Ascending: Use a value of true if you want the rank to go from low to high values, false if you want to rank larger values first.

Filter: Expression to disclude values you don't want to rank. Say, you want to ignore values with 0 as the value, you'd put row["ExpressionRow"] != 0. This would ignore the 0 values. Now you can filter these values out in your table if you don't want them and they won't affect your rankings.

Let me know if you have questions.

madereg

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="77338" data-time="1305923915" data-date="20 May 2011 - 01:38 PM">
Hi Mike, 
 
Expression: The field you want to rank. 
 
Ascending: Use a value of true if you want the rank to go from low to high values, false if you want to rank larger values first. 
 
Filter: Expression to disclude values you don't want to rank. Say, you want to ignore values with 0 as the value, you'd put row["ExpressionRow"] != 0. This would ignore the 0 values. Now you can filter these values out in your table if you don't want them and they won't affect your rankings. 
 
Let me know if you have questions. </blockquote>
 
Thanks! Worked like a charm. The only thing I would like to be able to do though is actually have non-repeating rank values for ties. The only way I can see to do that is actually to write a little code in the function as opposed to using the RANK function. Thank you for all your help! 
 
Best Regards, 
Mike

mwilliams

What do you want for ties? If you want it to be blank, in your table when you display it, you can just use the "suppress duplicates" option.

madereg

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="77345" data-time="1305945103" data-date="20 May 2011 - 07:31 PM">
What do you want for ties? If you want it to be blank, in your table when you display it, you can just use the "suppress duplicates" option. </blockquote>
 
Hi Michael, 
 
For ties I would actually like to continue on sequentially. So instead of ranking for example like 1,2,3,3,3,6,7,8,8,9. I would actually like to have the ranking as 1,2,3,4,5,6,7,8,9,10. For the values that actually tied, I would like to default back to their default order in how they just happen to fall within the results. 
 
This report that I'm creating is actually an existing report but we are converting over our reports to utilize BIRT. I actually prefer the ranking how the BIRT RANK function is producing the results but since this is an existing report, I'd like to duplicate how the users are used to seeing the ranking as it exists today in this particular report. 
 
I hope that all made sense. 
 
Thanks, 
Mike

mwilliams

Mike,

The best way to add that kind of ranking will be to just sort your dataSet by the field you want to rank and then add a "runningcount" aggregation. So, if you're using SQL, you can sort your results in your query and add a computed column to do the aggregation. If you're using any other dataSet, just add a column to your dataSet in your design and drag an aggregation element from the palette into your new column and use the "runningcount" option.

madereg

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="77383" data-time="1306154983" data-date="23 May 2011 - 05:49 AM">
Mike, 
 
The best way to add that kind of ranking will be to just sort your dataSet by the field you want to rank and then add a "runningcount" aggregation. So, if you're using SQL, you can sort your results in your query and add a computed column to do the aggregation. If you're using any other dataSet, just add a column to your dataSet in your design and drag an aggregation element from the palette into your new column and use the "runningcount" option. </blockquote>
 
Hi MIchael, 
 
Thanks. The downside of this report is that it has multiple rankings with the data in the first column ranked sequentially from 1-to-n. The other columns are ranked separately based on their respective values in that column. Therefore, those values in those columns will not appear sequentially, they just show their particular rank based on the how they fall for that particular column. Here's a quick mockup of what I'm talking about, maybe this will explain it better... 
 
NAME TOTAL TOTAL OPEN TOTAL SETTLED TOTAL FOR TOTAL AGAINST 
BBB 1 50 1 30 2 20 2 15 3 5 
DDD 2 40 3 10 1 30 1 20 2 10 
FFF 3 30 2 18 3 12 3 1 1 11 
 
The numbers in front of their respective tallies are their ranks respective to that column of data with the greatest value in each column receiving the lowest ranking number. 
 
I am using Oracle SQL as the source for the data. 
 
I hope that example makes sense as to how this report is structured. The results table is comprised of a single data set. 
 
Thanks, 
Mike

mwilliams

Mike,

I don't know that you'll be able to do this with BIRT's rank function then. This could be a good enhancement request. Have you tried using the Oracle rank function in your query? Maybe that will work?

madereg

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="77393" data-time="1306159508" data-date="23 May 2011 - 07:05 AM">
Mike, 
 
I don't know that you'll be able to do this with BIRT's rank function then. This could be a good enhancement request. Have you tried using the Oracle rank function in your query? Maybe that will work? </blockquote>
 
Hi Michael, 
 
I've tried getting the Oracle Rank function to work within my query but was not having much luck with it. The Rank function within BIRT seems to be working on my results with the exception of how I would like it to handle ties (not duplicating ranks but using distinct values). I'm going to see if I can get the users to buy-off on how BIRT's Rank function is producing the results. I personally do not mind how the results are currently duplicating rank values for ties...actually makes more sense to me. However, it ultimately comes down to the user's buying off on it. lol I may give another go at trying to get the Oracle Rank or Dense Rank function to work. 
 
Thank for very much for all your help and insight. It is greatly appreciated. 
 
Best regards, 
Mike

mwilliams

Mike,

You could script a solution possibly, or use multiple tables or datasets. None will be very straightforward, though, I don't think. Definitely give the oracle function another try and talk to your users first!

bcs0629

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="38805" data-time="1225730574" data-date="03 November 2008 - 09:42 AM">
pavlicic, 
 
 
In your dataSet, you can create a computed column based off of another column's value. If your data comes in sorted, you can do a ranking of 1 to numRows by doing an aggregation of running count. If you want to base the rank off of another column's value, you can create an expression that checks for certain criteria. </blockquote>
 
Hey, 
I hate to be bringing up an old thread, but I am having the dilemma of needing to rank based off another column's value. I've been trying to get the rank to be based on a certain group in the dataset. I have Column A, which is a person's team (1,2,3) and Column B, which is a the value of a score. I need to rank the person's score based on the team they're on. Column A is dynamic, the team names can be anything, so I can't hard code a team name. Attached is an example of what I would need the output to be. It seems like it should be fairly simple, but I haven't quite grasped it apparently. 
 
Thanks, 
Bryan

mwilliams

Group your table by team and then add an aggregation in the detail column that uses the rank function. Be sure to aggregate on your group. That should do it. Hope this helps. Let me know if you have questions or something doesn't work right with this.

bcs0629

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="95097" data-time="1328567158" data-date="06 February 2012 - 03:25 PM">
Group your table by team and then add an aggregation in the detail column that uses the rank function. Be sure to aggregate on your group. That should do it. Hope this helps. Let me know if you have questions or something doesn't work right with this. </blockquote>
 
Thanks for the reply, I had been trying to do this in the dataset so I could get the result from there. My table is actually inside of a list, so I have that filtered to match the personid of the list. Which in turn results in all aggregations being '1' because the data is filtered to one row since it's only for that person. I'm not using a full table, I'm using a table within a cell so I can get one specific value for one person, I cannot return all values for the group.

bcs0629

Hey Mike, I've actually figured out a method that works. Using what you suggested, I'll take off the filter by person, and return the whole set, and then use visibility to match the person, works like a charm. Thanks for the help.

Bryan

mwilliams

Glad to help! Let me know if you need to try to get the rank another way!