Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

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

  • 27 January 2014
  • Author: TyroneBrown
  • Number of views: 9810
  • 0 Comments

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   

Print
Categories: Big Data
Tags:
Rate this article:
5.0

TyroneBrownTyroneBrown

Other posts by TyroneBrown

Please login or register to post comments.