June 12, 2012 at 6:13 am
Hi All,
I have a report that shows figures for branches and the sales manager want to see the top 3 and bottom 3 branches based on the figures.
Is there a way I can create a column in SSRS that will show the rank of a particular figure?
Here is an example of a view that I would like (just a copy of what I currently have in Execl).
Sales Ranking
5648
7732
7112
3555
2939
6419
5217
7452
5938
5541
8423
2264
5651
7817
7112
6419
8731
7649
6122
5544
1312
5925
5627
2558
3135
588
4946
Thank you for your input.
June 12, 2012 at 6:23 am
I dont think there is in SSRS, but you could change the data set to include the RANK or DENSERANK function depending what you need then show that in the report.
declare @tab table (branch int, sales int)
insert into @tab (branch,Sales) values
(1,56),(2,77),(3,71),(4,35),(5,29),(6,64),(7,52),(8,74),(9,59),(10,55),(11,84),(12,22),(13,56),
(14,78),(15,71),(16,64),(17,87),(18,76),(19,61),(20,55),(21,131),(22,59),(23,56),(24,25),(25,31),(26,58)
select
branch,
sales,
RANK() OVER(ORDER BY SALES DESC) AS Ranking --If a matching value it skips to the rank value + number of matches, eg 3 branchs sell 19 and its rank 14, then the next rank will be 17, not 15
from
select
branch,
sales,
DENSE_RANK() OVER(ORDER BY SALES DESC) AS Ranking --If a matching value it skips to the next rank value eg 3 branchs sell 19 and its rank 14, then the next rank will be 15 not 17
from
June 12, 2012 at 6:49 am
Thanks Anthony, that works for me.
anthony.green (6/12/2012)
I dont think there is in SSRS, but you could change the data set to include the RANK or DENSERANK function depending what you need then show that in the report.
declare @tab table (branch int, sales int)
insert into @tab (branch,Sales) values
(1,56),(2,77),(3,71),(4,35),(5,29),(6,64),(7,52),(8,74),(9,59),(10,55),(11,84),(12,22),(13,56),
(14,78),(15,71),(16,64),(17,87),(18,76),(19,61),(20,55),(21,131),(22,59),(23,56),(24,25),(25,31),(26,58)
select
branch,
sales,
RANK() OVER(ORDER BY SALES DESC) AS Ranking --If a matching value it skips to the rank value + number of matches, eg 3 branchs sell 19 and its rank 14, then the next rank will be 17, not 15
from
select
branch,
sales,
DENSE_RANK() OVER(ORDER BY SALES DESC) AS Ranking --If a matching value it skips to the next rank value eg 3 branchs sell 19 and its rank 14, then the next rank will be 15 not 17
from
June 12, 2012 at 6:53 am
No problem, as I said in the comments as well make sure you pick the right function depending on what your want your ranking column to look like.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply