如何在SSRS中获得排名?类似于SQL Server中的DENSE_RANK函数

问题描述

嗨CodeProject家伙！

有没有办法在SSRS中获得排名? SSRS中是否有任何可用的功能可以对我的数据进行排名?如果是这样，可以请您分享一下有关它的知识吗?我希望排名只是SQL Server DENSE_RANK函数的方式.

这是我需要做的事情的表格:

            SHIFT                总计           SHIFT RANK   ;    所有排名
约翰·杜1号(   )      42.31 ;              b ;   1
John Doe 2         28.39    bsp ;              b ;   4
约翰·多伊(John Doe)3上午9点-下午5点          b    ;              b ;   2
简·多(Jane Doe)1下午5点-凌晨1点(      34.48       ;              b ;   3
Jane Doe 2       22.50 ;              b ;   6
Jane Doe 3下午5点-1 AM     26.19   b ;              b ;   5

我希望通过两种方式进行排名:

1.)根据总人数对他们进行排班.
2.)对所有座席进行排名，而不考虑基于总数的班次.

任何帮助，评论和建议将不胜感激！

非常感谢.

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.

推荐答案

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

希望对您有所帮助.

Hope it helps.

这篇关于如何在SSRS中获得排名?类似于SQL Server中的DENSE_RANK函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！