Hi All,
I currently have a problem with the rank and running-count functions, where I'm trying to only display the top 10 rows based on a column, however some of the rows have equal values (as displayed in the picture below)
(http://i.imgur.com/T2BY2cm.png)
Where the value is equal it ends up displaying 13 rows, rather than my desired 10, even though I have [rank]<=10. I have also tried doing a running-count([rank]) and then using a detail filter running-count([rank])<=10, but this just shows the first 10 records, rather than the 10 top records.
Can anybody help me with this please?
Thanks in advance,
Will.
Bump,
Has anyone got an idea why this wouldn't work?
Can you let us know what you'd expect? If you had 13 records that were all ranked equally as #1 how would you want the report to select just 10 of them?
I'm not fully sure to be honest, I don't know the full extent of the functions available. But say there were #13 records all ranked #1, if possible I would just like to select 10 of those, and increment the ranking every time regardless of whether the values were equal.
So, for your example of 13 records, I'd like it to select 10, and still rank them 1-10 instead of all just ranked as 1. Is this possible?
Thanks.
I don't know of you can change the ranking (someone else might be able to offer a solution), but if you just want to have the first top ten records you could probably a filter that combines rank and running-count. So it would probably look something like:
Rank([Field]) <= 10 and running-count([Field]) <= 10
I'm in the middle of something else so i can't test it at the moment, but have a go and let me know if you can't make it work.
Thanks for your help, when I try and have the rank & running-count in the same filter, the report then doesn't want to return anything 'No Data Available' :(
Try this It may help you :
[DATA ITEM 1] in expression use Rank( [Field] DESC )
[DATA ITEM 2] in expression use running-count([Field])
In Detail Filter use DATA ITEM 2 <=10
I have already tried using the running-count as a detail filter, but instead of showing the top 10 values, when I use running-count as the detail filter, it just shows the first 10 values in the database (regardless of the data).
Apologies if I'm telling you how to suck eggs, but did you specify that the detail filter needs to be done after aggregation?
Yes I have tried both approaches, before & after but no success :-\