percentile and PercentRank

Hello All,

Does anyone know how to do an expression that calculate percentrank and percentiles? I have a table with a list of transactions and their response time. I need to find the 75th, 90th, 95th, and 98th percentile as well as the percentrank of <=5 seconds, 10 seconds, 30 seconds and 60 seconds.

Any help is appreciated!

Those are all SQL functions, not SSRS ones

NTILE(N) can give you the percentiles

so NTILE(100) will give you 1-100, while NTILE(10) is 1-10

SQL Ranking Functions - RANK, NTILE, DENSE_RANK, ROW_NUMBER

http://msdn2.microsoft.com/en-gb/library/ms189798(SQL.90).aspx

|||

Hmmmm. So how do I do it in Reporting Services? Do I need to put the NTILE function in my base SQL statement? Or are you saying the Microsoft does not have the ability to percent rank and percentiles in SSRS? I am starting to find it odd that Excel creates better reports than SSRS.

Thanks for your help

|||

yes, you use it in the base SQL statement in the "Dataset" tab (or Stored Proc)

should be much faster too

I haven't explored whether ranking is available in SSRS, but knowing Excel doesn't have ranking function either, I will assume minimum from SSRS

Note: if you have filter or parameterized report, ranking is RELATIVE in the "selected" dataset, not the "whole" dataset

e.g. if you pick only 1 person, his/her rank will always be #1; when you pick 10 person, his/her rank may be #1 to #10

|||

A query that uses the ROW_NUMBER() function might look something like:

Code Snippet

declare @.tranTable table
( tranID varchar(20),
elapsedTime integer
)
insert into @.tranTable
select 'Transaction # 1', 37.1403174930829 union all
select 'Transaction # 2', 26.0536172199012 union all
select 'Transaction # 3', 36.928245609864 union all
select 'Transaction # 4', 25.9599151436726 union all
select 'Transaction # 5', 57.9377841191627 union all
select 'Transaction # 6', 62.0332552986104 union all
select 'Transaction # 7', 42.2948297227879
--select * from @.tranTable

;with maxRank as
( select count(*) as maxRank
from @.tranTable
), ranker as
( select TranId,
elapsedTime,
100.0 * row_number() over
( order by elapsedTime, tranId ) / maxRank
as percentile
from maxRank
cross join @.tranTable
)
select * from ranker

With results that look something like:

Code Snippet

TranId elapsedTime percentile
-- --
Transaction # 4 25 14.285714285714
Transaction # 2 26 28.571428571428
Transaction # 3 36 42.857142857142
Transaction # 1 37 57.142857142857
Transaction # 7 42 71.428571428571
Transaction # 5 57 85.714285714285
Transaction # 6 62 100.000000000000

|||Excel does have both a PERCENTRANK and PERCENTILE functions which are very valuable. For example if you want to to find the 90th Percentile you would have ...

Code Snippet

=PERCENTILE(AF26:AF34,0.9)

So I would think SQL would have something like

Code Snippet

=PERCENTILE (fieldnamehere,0.9)

But it doesnt. Likewise with percentrenk, in excel you would have

Code Snippet

=PERCENTRANK(AF26:AF34,25.5,4)

But with either, SSRS or a SQL query does not appear to have a function. Still baffling to me. They do provide a standard deviation, SUM, AVG, and other basic reporting functions.

|||but no PRODUCT() aggregate