How can I create a parameter which choosing a number of top of values

Question

I've created a report in SSRS 2008. The report contains fields: Number of requests, Organization of the initiator

How can I create a parameter which choosing a number of top of values from field Number of requests

Example, set value of parameter 5:

Number of requests, Organization of the initiator    
10                  initiator1    
9                   initiator2    
8                   initiator3    
7                   initiator4    
6                   initiator5

Can you edit your questions to include the SQL for your main dataset? — R. Richards, Mar 26 '17 at 11:15

score 1 · Accepted Answer · edited May 23 '17 at 12:25

There are a few ways to approach this. You can either do the work in the report as in this similar question How to get total of top 10 sales in SSRS 2012

Or you can do this in your dataset. Assuming you are using SQL Server you can do something like this.

-- declare @TopN int = 5 -- Comment this out in production this is only used for testing. This parameter will be passed in from SSRS

SELECT * FROM
    (
    select NumberOfRequests, Organization
        , RANK() OVER(ORDER BY NumberOfRequests desc) as rnk
    from MyTable
    ) q
    WHERE q.rnk <= @TopN

All we are doing here is selecting the data assigning each row a rank based on NumberOfRequests, biggest = 1 next biggest = 2 etc.....

Then we have have an outer query that simply selects anything where the rank is less than your desired number.

How can I create a parameter which choosing a number of top of values

1 Answers1