Answered by:

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?

Answers

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.

All replies

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.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.