Display Top n Values in SSRS based on Parameter value

Question

So I have a simple table in SSRS with three columns. The first is Agent (the name of a call handler) and the second is populated by a parameter that controls which of 6 possible columns to display data for. The query that makes up the dataset is as follows;

SELECT CAST(Day AS DATE) AS 'Day'
, ISNULL(externalID, '**No Agent**') AS 'Agent'
, CAST((TalkTime + BreakTime + BusyonDNTime + BusyMiscTime + ConsultationTime + RingTime + Default_ACW + Wrap) / NULLIF ([Calls Ans], 0) AS DECIMAL(18, 2)) AS 'AHT'
, CAST([Comfort Break] / NULLIF (Logged, 0) AS DECIMAL(18, 6)) AS 'ComfortBreak'
, CAST([Designated Other] / NULLIF (Logged, 0) AS DECIMAL(18, 6)) AS 'DesignatedOther'
, CAST(Reflection / NULLIF (Logged, 0) AS DECIMAL(18, 6)) AS 'Reflection'
, CAST([System Failure] / NULLIF (Logged, 0) AS DECIMAL(18, 6)) AS 'SystemFailure'
, CAST(Skillset_Default_Activity_Code / NULLIF (Logged, 0) AS DECIMAL(18,6)) AS 'Skillset_Default_Activity_Code'
, [Short Calls Ans] AS 'ShortCallsAns'
, CASE WHEN CAST(([TalkTime] + [BreakTime] + [BusyonDNTime] + [BusyMiscTime] + [ConsultationTime] + [RingTime] + [Default_ACW] + [Wrap]) / NULLIF ([Calls Ans],0) AS DECIMAL(18, 2)) IS NULL
         AND CAST([Comfort Break] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL 
         AND CAST([Designated Other] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL 
         AND CAST([Reflection] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL AND CAST([System Failure] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL 
         AND CAST([Skillset_Default_Activity_Code] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL AND [Short Calls Ans] IS NULL
         THEN 0 
        ELSE 1 
        END AS DataFlag
FROM            PulseReportData
WHERE        (CAST(Day AS DATE) = @ActivityDate) AND 
                     ((CASE WHEN CAST(([TalkTime] + [BreakTime] + [BusyonDNTime] + [BusyMiscTime] + [ConsultationTime] + [RingTime] + [Default_ACW] + [Wrap]) / NULLIF ([Calls Ans],0) AS DECIMAL(18, 2)) IS NULL
         AND CAST([Comfort Break] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL 
         AND CAST([Designated Other] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL 
         AND CAST([Reflection] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL AND CAST([System Failure] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL 
         AND CAST([Skillset_Default_Activity_Code] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL AND [Short Calls Ans] IS NULL
         THEN 0 
        ELSE 1 
        END) = 1)

The report has a parameter called @Field of which the values are the column names of the columns above (apart from Date and Agent). The 2nd column data textbox on the report contains the following expression;

=Fields(Parameters!Field.Value).Value

So whichever of the column names is picked from the drop down list before the report is run will display in the second column.

What I can't figure out how to get working is this;

I need to add a parameter that will let the user select a rank number of 5, 10, 20 or 50 that will show the Top 5, 10, 20 or 50 Agents based on the numbers that come back in whichever of the columns is selected. I tried to add the following to the dataset query;

ROW_NUMBER() OVER (partition BY @Field ORDER BY @Field DESC) AS rn

And then add column rn as the third column in the report. I then applied a filter to the tablix to say where column rn < the value of a parameter I created called @rank. The values of which were 6, 11, 21 & 51 (always one higher than the actual amount of ranked values to take account of "less than". Although I guess I could use <= )

What happened was the list of Agents and values was displayed on the report with the correct number of ranked values shown in column three as selected from the @rank drop down parameter, but the values of the main column were NOT ranked in a descending order. They appeared to be completely at random. So it's like the ROW_NUMBER column I added to the dataset query isn't interested in the columns that are inside the parameter. I just can't figure a way to get those ranked values back based on top 5, 10, 20 or 50 :-S

Which is your main Column Agent? or AHT, ConfortBreak etc.? — Anup Agrawal
The only fixed column is Agent the rest are all dynamic. Kind of like a pivot table style selection but using an SSRS drop down parameter. I need to be able to order by and rank any of these columns; ComfortBreak, DesignatedOther, Reflection, SystemFailure & Skillset_Default_Activity_Code on the fly. — Phteven
I am trying to understand what do you mean by but the values of the main column were NOT ranked in a descending order. — Anup Agrawal

Randall Randall · Accepted Answer · 2015-09-23T19:40:49

Several ways to do it, the easiest would be just adding two more parameters to your sql that map to the column selected and the desired number of rows returned. The trick is that you can use an alias in an order by statement, so order by ComfortBreak is perfectly doable. In the below example I use offset fetch to isolate the changes to the end so it's a little more clear what the change is, but Select top @RowCount would also work.

SELECT CAST(Day AS DATE) AS 'Day'
, ISNULL(externalID, '**No Agent**') AS 'Agent'
, CAST((TalkTime + BreakTime + BusyonDNTime + BusyMiscTime + ConsultationTime + RingTime + Default_ACW + Wrap) / NULLIF ([Calls Ans], 0) AS DECIMAL(18, 2)) AS 'AHT'
, CAST([Comfort Break] / NULLIF (Logged, 0) AS DECIMAL(18, 6)) AS 'ComfortBreak'
, CAST([Designated Other] / NULLIF (Logged, 0) AS DECIMAL(18, 6)) AS 'DesignatedOther'
, CAST(Reflection / NULLIF (Logged, 0) AS DECIMAL(18, 6)) AS 'Reflection'
, CAST([System Failure] / NULLIF (Logged, 0) AS DECIMAL(18, 6)) AS 'SystemFailure'
, CAST(Skillset_Default_Activity_Code / NULLIF (Logged, 0) AS DECIMAL(18,6)) AS 'Skillset_Default_Activity_Code'
, [Short Calls Ans] AS 'ShortCallsAns'
, CASE WHEN CAST(([TalkTime] + [BreakTime] + [BusyonDNTime] + [BusyMiscTime] + [ConsultationTime] + [RingTime] + [Default_ACW] + [Wrap]) / NULLIF ([Calls Ans],0) AS DECIMAL(18, 2)) IS NULL
         AND CAST([Comfort Break] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL 
         AND CAST([Designated Other] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL 
         AND CAST([Reflection] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL AND CAST([System Failure] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL 
         AND CAST([Skillset_Default_Activity_Code] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL AND [Short Calls Ans] IS NULL
         THEN 0 
        ELSE 1 
        END AS DataFlag
FROM            PulseReportData
WHERE        (CAST(Day AS DATE) = @ActivityDate) AND 
                     ((CASE WHEN CAST(([TalkTime] + [BreakTime] + [BusyonDNTime] + [BusyMiscTime] + [ConsultationTime] + [RingTime] + [Default_ACW] + [Wrap]) / NULLIF ([Calls Ans],0) AS DECIMAL(18, 2)) IS NULL
         AND CAST([Comfort Break] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL 
         AND CAST([Designated Other] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL 
         AND CAST([Reflection] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL AND CAST([System Failure] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL 
         AND CAST([Skillset_Default_Activity_Code] / NULLIF ([Logged], 0) AS DECIMAL(18, 6)) IS NULL AND [Short Calls Ans] IS NULL
         THEN 0 
        ELSE 1 
        END) = 1)
order by @SelectedColumn /* acs/desc whatever is appropriate */
offset 0 rows fetch next @RowsReturned only

Display Top n Values in SSRS based on Parameter value

1 Answers