How to get rank in SSRS? Similar to DENSE_RANK Function in SQL Server

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

Hi CodeProject fella's!

Is there a way on how to get ranking in SSRS? Are there any available functions in SSRS for me to be able to rank my data? If so, can you please share your knowledge about it? I want the ranking to be just the way as SQL Server DENSE_RANK Function.

Here's a table of what I need to do:

  AGENT          SHIFT          TOTAL          SHIFT RANK      ALL RANK
John Doe 1     9AM - 5PM      42.31                   1                    1
John Doe 2     9AM - 5PM      28.39                   3                    4
John Doe 3     9AM - 5PM      37.25                   2                    2
Jane Doe 1     5PM - 1AM      34.48                   1                    3
Jane Doe 2     5PM - 1AM      22.50                   3                    6
Jane Doe 3     5PM - 1AM      26.19                   2                    5

I want the ranking in two ways:

1.) Rank agents according to their shift based on the total.
2.) Rank all of the agents regardless of shift based on the total.

Any help, comments, and suggestions will be highly appreciated!

Thank you very much.

Posted 16-Jan-12 2:54am

ernieball_26

Add a Solution

2 solutions

Solution 1

you can use rank over to re order in ranking order here is the detailed method

http://umairaslam.blogspot.com/2011/02/ranking-query.html[^]

Posted 6-Feb-12 17:13pm

umairaslam22

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amir Mahfoozi · Accepted Answer · 2012-02-06T19:03:00

Solution 2

Here it is :

SQL

SELECT *
      , rank() over (partition by  shift order by total desc) ShiftRank
      , rank() over (order by total desc) AllRank
  FROM Agents

Hope it helps.

Posted 6-Feb-12 19:03pm

Amir Mahfoozi

Comments

ernieball_26 7-Feb-12 3:33am

I did it this way Amir, I guess it would be a hard time for me doing a custom code in SSRS. Thanks for the help buddy! (",)

Amir Mahfoozi 7-Feb-12 3:38am

You're welcome :)