how to manipulate SSRS multiple values parameters in a dataset

0
votes

i have a simple ssrs report in which i have one multiple values parameter. I want to use the returned values to execute some queries on another table, but when i create the second dataset and i try to get the list of returned values i get ssrs error.

Example:

Multiple values parameter: @MyParam1

  • Anthony
  • Michael
  • Mary

I want to select for example only the values that starts with 'M' and populate with the filtered data another multiple values parameter called @MyParam2.

When i try to parse @MyParam1 in my dataset query i get the error because teh returned value is an array type. I read on some blogs that i can use the JOIN function , but how i can use directly in the Dataset TSQL code?

thanks a lot!

1
reporting-services

1 Answers

0
votes

You could use the original table for the parameters and filter it by your selection for your main query.

SELECT FIRST_NAME, USER_LAST_NAME, USER_ID 
INTO #TEMP_USERS
FROM USERS 
WHERE FIRST_NAME IN (@MyParam1)

Then JOIN on the TEMP table to filter for the parameter selections and add your criteria.

JOIN #TEMP_USER U ON U.USER_ID = X.USER_ID AND U.FIRSTNAME LIKE ('M%')