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

Percentage Expression

Hi,
How do I calculate a % for the Group Totals ?
%age = ((CurrentYear - PreviousYear) / PreviousYear)
Thankstry sum( ((CurrentYear - PreviousYear) / PreviousYear) )|||If you want the percentage for the entire group, I'd do it as %age =(sum(current year) -sum(previous year))/sum(previous year).
if you do %age = sum((current - previous)/previous) you'll get the sum
of all the individual percentages|||If you want the percentage for the entire group, I'd do it as %age =(sum(current year) -sum(previous year))/sum(previous year).
if you do %age = sum((current - previous)/previous) you'll get the sum
of all the individual percentages|||If you want the percentage for the entire group, I'd do it as %age =(sum(current year) -sum(previous year))/sum(previous year).
if you do %age = sum((current - previous)/previous) you'll get the sum
of all the individual percentages

Percentage Column in Matrix

Hi,
I would like to generate following table with reporting service matrix.
But if I use the expression : percentage coulmn=sum(field!qty.value) / sum(field!qty.value, "region_group")
It will become a percentage of a row total, instead of a group total (store group). Please see the second table.
Any ideas?

Thank you!

(Correct!)

store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC

10

10%

20

20%

70

70%

100

100%

25

50%

25

50%

50

100%

EURP C2 CHINA

(Wrong)

store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC

10

6%

20

13%

70

46%

100

25

16%

25

16%

50

EURP C2 CHINA

Can somebody help me or give me a hint? (or my question is not clear enough?)
If this kind of report is doable, I will ask my team member change their JSP report to MS RS.
Thank you!!