value
34
45
54
How can I write an expression to create corresponding Rank column.
value Rank
--
34 3
45 2
54 1
Thanks
well, Lest this thread should remain hanged...I have solved this at the stored procedure level. I did not find any means by which I could do it in the reporting server.
Thanks
Ragz.|||Why couldn't you sort the column by value and use the RowCount() function?|||I have the same problem.
I have many Cognos Reports to be converted in Reporting Services.
Out of which there are several Ranking Reports.
Cognos has inbuilt Rank Function while SSRS does not have.
I dont want to do grouping on Database level, rather trying to find a solution in RS.
But unfortunately I couldnt find any solution.
I sorted the group according to requirement, now only thing left is showing the rank number and limiting top 60 rows only.
I tried filters to show TopN, but always gives and error and also tried custom code to show the row number for that group but didn't work.
Anyone has any idea how to overcome this limitation of Reporting Services.
One more interesting is when I try to create a field in dataset having IIF and / within.. RS terminates, all unsaved work gone.
Sometimes I think Microsoft cannot have a product which is 100% perfact and working.
|||
If you are using SQL Server 2005 and creating based on a query you can certainly use the row_number() function; something like
Code Snippet
select value,
row_number() over
( order by value
) as rank
from yourTable
|||I wanted to implement the Rank function in Reporting Services but I couldn't,
so the only solution left is at database level, so I grouped everything in PL-SQL query itself.
I also used Partition by clause in Rank function as per the report requirement.
Anyway thank you guys for your help, I hope microsoft would add more features in its upcoming 2008 products.
Nik|||
The rowcount() feature in RS as suggested above might be able to solve this, as well as the Top N issue. I currently use it to shade every other row by combining it with Mod.
|||rowcount() and TopN in RS are for simple reports, I have very complex reports and the rank() function in PL/SQL is working perfact for me.And as I said RS (visual studio) terminates when you add a new formula column in a dataset which has sum and '/' within IIF expression.
