question

David 2 1 avatar image
David 2 1 asked

SSRS Expression Multiple Like Parameter

Hi, I am trying to write an SSRS expression to an OLE DB sourced database that will pass in multiple like postcode values. For example the below works if I only pass in 1 postcode, eg 'EH1', however if I change the parameter setting to allow multiple values such as 'EH1' and 'EH2' it errors: =" select custid, home_postcode, office_postcode " + " from customer c left join business b on c.custid = b.custid " + " where " + " (c.home_postcode like '" & Parameters!Postcode.Value & " %' " + " or b.office_postcode like '" & Parameters!Postcode.Value & " %' ) " I need the query to execute if the user passes in only 1 postcode, or multiple postcodes. TIA
ssrsparameterssql2012likeexpression
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
0 Likes 0 ·
Mart avatar image
Mart answered
I think the cleanest way to achieve this is to allow the postcodes to be written in separated by a comma (I'll clean this up a bit later). This parameter is then passed into a table valued function that splits them up ro give you a table that can be joined to ( http://www.sqlservercentral.com/articles/Tally+Table/72993/). Obviously you're only concerned with the first half of the postcode so you'll need do some magic to strip that out of your data to do the join - you could use a view or a computed column, or just store it or something: --Can use Charindex to find position of the space SELECT Charindex(' ', 'AA11 1AA', 1) AS Space_Position --we don't want the space so could use RTRIM or just take off 1 SELECT Charindex(' ', 'AA11 1AA', 1) - 1 AS Last_Character_Position --Can then use that within a substring to get the first half SELECT SUBSTRING('AA11 1AA', 1, (Charindex(' ', 'AA11 1AA', 1) - 1)) To improve the input you could have all available postcodes in a dropdown list (1st parameter) which then populates a string parameter (2nd parameter) and this is the one that is passed to the function, this would provide a level of validation and enforce a consistent string set up to be passed. OR The other option, would be to write the SQL code in the report, SSRS can then dynamically create the SQL statement for you. OR Execute your code without the where clause and allow SSRS to Filter the values using the multi pick, once they are all returned. Hope that helps!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mart avatar image Mart commented ·
Hi David, any chance you can review and vote up/mark as correct answer, cheers!
0 Likes 0 ·
kiranmath avatar image
kiranmath answered
One Possible solution is create pass a string of Postcodes to stored procedure and then dynamically create the query inside the stored procedure. ~Kiran
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.