none
using Aggregate function on Parameter value

    Question

  • Hi, I am using SSRS 2008 R2 and writing an expression to get the Maximum & Minimum values from a multi value parameter. This expression does not throw any error or return anything. I looked for msdn documentation to see if it said not to use Aggregate functions on Parameters but nothing as such has been documented so far. I was wondering how would I do this?

    Thanks in advance.........


    Ione

    Thursday, September 26, 2013 6:25 PM

Answers

  • Hi lone,
     
    According to your description, it seems that you want to get the Maximum and Minimum values from a multiple value parameter. In Reporting Service, we can insert the selected values into a temp table and then get the Maximum and Minimum values to show it. After testing it in my own environment, we can refer to the following steps:

    1. Create a dataset named DataSet1 using the query below.
    CREATE TABLE #Max (COL1 INT)
    INSERT INTO #Max VALUES(1)
    SELECT * FROM #Max
    2. Change the dataset using the expression below:
    ="CREATE TABLE #Max (COL1 INT)" &
    "INSERT INTO #Max VALUES (" & Join(Parameters!Name.Value,"),(") &")" &
    "SELECT TOP 1 * FROM #Max ORDER BY COL1 DESC"
    3. Create a parameter named Max, set it’s visibility to hidden and get available values and default values from the DataSet1 COL1.
    4. Create a dataset named DataSet2 using the query below.
    CREATE TABLE #Min (COL2 INT)
    INSERT INTO #Min VALUES(1)
    SELECT * FROM #Min
    5. Change the dataset using the expression below:
    ="CREATE TABLE #Min (COL2 INT)" &
    "INSERT INTO #Min VALUES (" & Join(Parameters!Name.Value,"),(") &")" &
    "SELECT TOP 1 * FROM #Min ORDER BY COL2 ASC"
    6. Create a parameter named Min, set it’s visibility to hidden and get available values and default values from the DataSet2 COL2.
    7. Use the expression below to achieve the requirement.
    ="max:"& Parameters!Max.Value & vbcrlf & "min:" & Parameters!Min.Value

    The following screenshot is for your reference:
                 
    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong 

    • Proposed as answer by Uri DimantMVP Monday, September 30, 2013 9:45 AM
    • Marked as answer by ione721 Monday, September 30, 2013 2:30 PM
    Monday, September 30, 2013 9:31 AM

All replies

  • Hi lone,
     
    According to your description, it seems that you want to get the Maximum and Minimum values from a multiple value parameter. In Reporting Service, we can insert the selected values into a temp table and then get the Maximum and Minimum values to show it. After testing it in my own environment, we can refer to the following steps:

    1. Create a dataset named DataSet1 using the query below.
    CREATE TABLE #Max (COL1 INT)
    INSERT INTO #Max VALUES(1)
    SELECT * FROM #Max
    2. Change the dataset using the expression below:
    ="CREATE TABLE #Max (COL1 INT)" &
    "INSERT INTO #Max VALUES (" & Join(Parameters!Name.Value,"),(") &")" &
    "SELECT TOP 1 * FROM #Max ORDER BY COL1 DESC"
    3. Create a parameter named Max, set it’s visibility to hidden and get available values and default values from the DataSet1 COL1.
    4. Create a dataset named DataSet2 using the query below.
    CREATE TABLE #Min (COL2 INT)
    INSERT INTO #Min VALUES(1)
    SELECT * FROM #Min
    5. Change the dataset using the expression below:
    ="CREATE TABLE #Min (COL2 INT)" &
    "INSERT INTO #Min VALUES (" & Join(Parameters!Name.Value,"),(") &")" &
    "SELECT TOP 1 * FROM #Min ORDER BY COL2 ASC"
    6. Create a parameter named Min, set it’s visibility to hidden and get available values and default values from the DataSet2 COL2.
    7. Use the expression below to achieve the requirement.
    ="max:"& Parameters!Max.Value & vbcrlf & "min:" & Parameters!Min.Value

    The following screenshot is for your reference:
                 
    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong 

    • Proposed as answer by Uri DimantMVP Monday, September 30, 2013 9:45 AM
    • Marked as answer by ione721 Monday, September 30, 2013 2:30 PM
    Monday, September 30, 2013 9:31 AM
  • Thanks very much this helps.

    Ione

    Monday, September 30, 2013 2:30 PM