none
[Forum FAQ] How to show Top N selected parameter values from a specified parameter on the report?

    General discussion

  • Introduction

    In a SQL Server Reporting Services report, if multiple items on the parameter’s drop-down list (such as 1000 items) were selected and displayed, it will distort the whole report. Therefore, you can show maximum of 5 values to user. If a user selects below that number, those much values will be shown but if user selects more than 5, only 5 values will be shown.
    In this article, I will demonstrate how to show Top N selected parameter values from a specified parameter on the report.

    Solution

    We can insert the selected values into a temp table and then get the top 5 values to show it.

    1. Create a dataset using the query below.
      CREATE TABLE #TOP5 (COL INT)
      INSERT INTO #TOP5 VALUES(1)
      SELECT * FROM #TOP5
    2. Change the dataset using the expression below:
      ="CREATE TABLE #TOP5(COL INT)" &
      "INSERT INTO #TOP5 VALUES (" & Join(Parameters!Value1.Value,"),(") &")" &
      "SELECT TOP 5 * FROM #TOP5 ORDER BY COL DESC"
      In SQL Server 2005, please use the expression:
      ="CREATE TABLE #TOP5(COL INT)" &
      "INSERT INTO #TOP5 VALUES (" & Join(Parameters!Value1.Value,") INSERT INTO #TOP5 VALUES ( ") &")" &
      "SELECT TOP 5 * FROM #TOP5 ORDER BY COL DESC"
    3. Create a parameter named Value2, set it’s visibility to hidden and get available values and default values from the new dataset COL.
    4. Use the expression below on the page header and footer to show the top values.
      =”Top Values:” & Join(Parameters!Value2.Value,”,”)

    Results:

    Applies to

    Microsoft SQL Server 2005
    Microsoft SQL Server 2008
    Microsoft SQL Server 2008 R2
    Microsoft SQL Server 2012

    Monday, January 13, 2014 6:35 AM