February 27, 2007 at 4:23 am
Hi All,
I am using SSRS last 6 months.. In my sales report I want to assign the rank of the sales voulmn dynamically.. what i have come to know that i can use only RowNumber function.( I am not going to use RANK or DENSE_RANK function in T-SQL in data section).. Can u please suggest me .
Thanks
Rahul das
February 27, 2007 at 7:21 am
I have used this logic in T-SQL to produce rankings.
--Drop table #T1
Create Table #T1
(
Rank int not null,
DolAmt numeric(15,2) null,
Ent varchar(5) null,
Region varchar(5) null
)
INSERT #T1 VALUES (0,1,'A','X')
INSERT #T1 VALUES (0,1.2,'B','X')
INSERT #T1 VALUES (0,10,'C','X')
INSERT #T1 VALUES (0,10,'D','X')
INSERT #T1 VALUES (0,2,'A','Y')
INSERT #T1 VALUES (0,9,'B','Y')
INSERT #T1 VALUES (0,3,'C','Y')
INSERT #T1 VALUES (0,3,'D','Y')
select * from #t1
-- drop table #t2
Select * into #T2 from #t1 order by DolAmt desc
select * from #t2
--update #t2 set Rank = 0
-----------------------------------------------------------------------------------------------------------------
DECLARE @Region varchar(6)
DECLARE DcRank CURSOR FOR
SELECT distinct Region
From #t2
OPEN DcRank
FETCH NEXT FROM DcRank
INTO @Region
WHILE @@FETCH_STATUS = 0
BEGIN
declare @count numeric(12, 0)
set @count = convert(numeric(12,0), 0)
update #t2
set Rank = @count, @count = @count + 1
Where #t2.region = @Region
FETCH NEXT FROM DcRank
INTO @Region
END
CLOSE DcRank
DEALLOCATE DcRank
select * from #t2 order by region,rank
----------------------------------------------------------------------------------------------------------------
-- update statments below utalizes row level processing
declare @count numeric(12, 0)
set @count = convert(numeric(12,0), 0)
update #t2
set Rank = @count, @count = @count + 1
--update #t2 set lineNum = 0
select * from #t2
--------------------------------------------------------------------------------------------------------------
-- allow ties
-- drop table #T3
Create Table #T3
(
DolAmt numeric(15,2) null,
Ent varchar(5) null,
Rank int not null)
insert into #t3
SELECT t.DolAmt,T.Ent,
(SELECT COUNT(*)
FROM (select distinct DolAmt From #t1 ) t1
WHERE t1.DolAmt >t.DolAmt
)+1 AS Rank
FROM #t1 t
select * from #t3 order by rank
February 27, 2007 at 9:25 pm
Hi George,
Thanks for the mail. Actually i want to assign the Ranking function in SSRS. Not in T-SQL.But your Ranking logic in T-SQL is really efficient.Please give some suggettion in SSRS how i can use Ranking .
Thanks
Rahul das
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply