CODEWITHSUNDEEP
t-sqlreporting-servicesssrs-2008-r2
user3068288
user3068288

Reputation: 11

TSQL / SSRS : Using LIKE with multi-valued parameters

I am trying to determine a solution to filter records using LIKE with a multi-valued parameter. In a simplistic example a user wants to return a set of 5-digit Object codes by entering the following in a parameter window in a SSRS report:

@parm_Object

1,24,333,34567

This ideally would return Object codes satisfying the following criteria:

1 : All Object codes starting with '1'

24: All Object codes starting with '24'

333: Similar

34567: Object code '34567'

I guess a starting point for me would be to determine whether this could be handled in the actual query, or should I do it on the SSRS side.

Upvotes: 1

Views: 53

Answers (1)

busytools
busytools

Reputation: 371

general good practice is to get rid of the data you don't need ASAP. so that would be in the query.

a SSRS multivalued parameter will show up as a comma separated list in the query.

the first step is to get from this comma separated list to a table (or table function), then you can join this table and apply like operators

for example

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS a
INNER JOIN  dbo.split1('test,fafa,tata') b
ON 1=1
WHERE a.COLUMN_NAME like b.Value + '%'

will return rows having column names starting with test, fafa or tata. the dbo.split1 function you have to write your own or get one from the internet. the link suggested by Tab Alleman for example.

Upvotes: 1

Related Questions