Filter Dropdown SSRS Parameters with a Comma-Delimited List Search Box

Filter Dropdown SSRS Parameters with a Comma-Delimited List Search Box

I recently encountered an interesting problem when working with a client.  I made an SSRS report that used a multi-select drop down box parameter that used a textbox to search and filter the items in the dropdown parameter. 

The query for the drop down list was pretty standard:

SELECT    e.LastName + ‘, ‘ + e.FirstName AS Name

FROM    DimEmployee e

              INNER JOIN DimSalesTerritory st

              ON e.SalesTerritoryKey = st.SalesTerritoryKey

WHERE    e.LastName LIKE ‘%’ + @SearchBox + ‘%’

               OR e.FirstName LIke ?%? + @SearchBox + ?%?

ORDER BY Name

 

However, my client had an interesting request.  Since they were multi-selecting, and since some items they wanted to include in the report may not have anything in common, they wanted a way to filter the drop down box based on multiple search filters.  This means my previous method would not work.  Instead, I came up with this function to parse over a delimited string, similar to a Split function, and insert matches in a table that could then be queried for the drop down list. 

This is (an AdventureWorks version of) the function I came up with:

 

USE [AdventureWorksDW2008R2]

GO

/****** Object:  UserDefinedFunction [dbo].[MultiParamSearch]    Script Date: 04/05/2012 17:13:39 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[MultiParamSearch](@String VARCHAR(8000), @Delimiter CHAR(1))    

RETURNS @temptable TABLE (items varchar(8000))    

AS    

BEGIN    

      DECLARE @idx INT

      DECLARE @slice VARCHAR(8000)

   

      SELECT @idx = 1    

            IF LEN(@String)<1 or @String IS NULL  return    

      WHILE @idx!= 0    

      BEGIN    

            SET @idx = CHARINDEX(@Delimiter,@String)    

            IF @idx!=0    

                  SET @slice = left(@String,@idx – 1)    

            ELSE

                  SET @slice = @String    

           

            IF(LEN(@slice)>0)

                  INSERT INTO @temptable(Items) (SELECT   DISTINCT e.EmployeeKey

                                                 FROM  DimEmployee e

                                                 WHERE e.FirstName LIKE ‘%’ + @slice + ‘%’

                                                         OR e.LastName LIKE ‘%’ + @slice + ‘%’

                                                 )

            SET @String = RIGHT(@String,LEN(@String) – @idx)    

            IF LEN(@String) = 0 BREAK

      END

RETURN   

 
Comments
Richard D'Angelo

Can you please share your .rdl file for both solutions?