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
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!