SSRS - Parameters - Dynamic with include, exclude and like.

I may make a few more changes to this blog but I think it will due for now.
I've created hundreds and hundreds of SSRS reports of years and have sometimes used an advanced report parameter selection methodology I am going to review here.  These user defined functions are used to populate SSRS drop down boxes and designed to provide filter options dependent on other filters and the ability to make the filters inclusive, exclusive or like statements.

The following user defined tables will need to be called by three data sources in the SSRS report.

•  udt_rpt_p_Make 
•  udt_rpt_p_Model

_{Default values are passed to these procedures as strings of comma separated values (CSV). The function}

CREATE FUNCTION [dbo].[udt_CommaSeparatedListToSingleColumn](
@cslist

VARCHAR(MAX) ,@Delimiter varchar(5))
RETURNS @t TABLE ( ItemId [int]
IDENTITY(1,1) NOT NULL
, Item VARCHAR(max)
, PRIMARY KEY CLUSTERED (ItemId))
BEGIN
DECLARE @spot integer, @str VARCHAR(max), @sql VARCHAR(max)

WHILE @cslist <> ''
BEGIN
SET @spot = CHARINDEX(@Delimiter, @cslist)
IF @spot > 0
BEGIN
SET @str = LEFT(@cslist, @spot-1)
SET @spot = @spot + DataLength(@Delimiter) - 1
SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot)
END
ELSE
BEGIN
SET @str = @cslist
SET @cslist = ''
END
INSERT INTO @t(Item) SELECT @str
END
RETURN /*
SELECT Item FROM dbo.udt_CommaSeparatedListToSingleColumn('Table1,Table2,Table3', ',')
SELECT Item FROM dbo.udt_CommaSeparatedListToSingleColumn('Table1,;,Table2,;,Table3', ',;,')
SELECT Item FROM dbo.udt_CommaSeparatedListToSingleColumn('Table1;Table2;Table3', ';')
*/ENDGO
_{is used to transform CSV lists into tables so they can be used in SQL statements.} 

udt_commaseparatedlisttosinglecolumn   _{is also used by the function udf_str_getnthparm to return a single value by it's position in the list..}

The function dbo_udf_wc_all_value  is acting as the constant we will use to denote ' no filter' or
' all records' to the user. The function udf_wc_is_all_value will call dbo_udf_wc_all_value to see if a value passed to it is the value designated as the ' all value'. Yes it is prefixed with a space.  

udt_rpt_p_Make is the least complicated function of the two as it was not designed to include the complex in and like logic.  The function basically unions together the look up values along with the desired defaults from either the default CSV list parameter or the default values we have specified in the udf_wc_all_value function . It includes the parameters 

@version_Make               which is passed to the udf_wc_all_value function
@scdcsv_default_Make     used to pass in default values to appear on the list
@defaulttype_Make           methodology type
@multiselecttype_Make     designates the type of list to be returned. (0 none,1 user def or 2 for built-in). 

select

   Makeid = t1.Makeid
   , Makename = t1.Makename
from (

-- return default values if they were passed in
   select
      Makeid = dbo.udf_str_getnthparm(t1.item, 1,',')
      , Makename = dbo.udf_str_getnthparm(t1.item, 2,',')
   from [dbo].[udt_commaseparatedlisttosinglecolumn](@scdcsv_default_Make ,';') t1
   where len(coalesce(@scdcsv_default_Make ,'')) > 0

   union
-- return default values if none were passed in

   select
      Makeid = [dbo].[udf_wc_all_value](@version_Make,1)
      , Makename = [dbo].[udf_wc_all_value](@version_Make,2)
   where len(coalesce(@scdcsv_default_Make ,'')) = 0
   and (@multiselecttype_Make = 1 and @defaulttype_Make <> 1)

   union

-- return lookup values
   select distinct
      Makeid = ltrim(rtrim([Makeid]))
      , Makename = ltrim(rtrim([Makeid]))
   from [dbo].[Make]
   where len(coalesce(@scdcsv_default_Make ,'')) = 0
   and @defaulttype_Make <> 1
   and not (@multiselecttype_Make = 1 and @defaulttype_Make = 2)

) t1
where len(Makename) > 0
order by
t1.Makename


udt_rpt_p_Model contains the two additional filter parameters @Makeid_mvprm_Make is used to provide a list of values that can be acted on and @Makeid_mvprm_Make_whereclauseid which specifies how to handle the values.

e.x.

0 denotes IN(@Makeid_mvprm_Make)
1 denotes NOT IN(@Makeid_mvprm_Make)
2 denotes LIKE (@Makeid_mvprm_Make)
and 3 denotes NOT LIKE(@Makeid_mvprm_Make) ).

You may also interested in some of my other ssrs information sources.

That's it for now! Hope this helps.