Search in (multiple value) Parameter (for usage in WHERE-clause)
Hi there, I'd like to give you a little bit of a background story first in order to explain my problem better: I have created a report, where there is a parameter with multiple values - in this case country codes like DE, DK, FR, GB etc. . These values are selected with the help of a query out of a MS SQL 2008 database (Navision). Now the problem is, that in the customer-database where the single lines contain the information of the country code, there is a country code for each foreign customer, but not for the domestic customers. These are simply blank. Apparently this can not be changed easily and I have to cope with it. Example of the problem: Let's say that the domestic country is DK. So there is no actual value for customers of DK, just a blank 'value'. The parameterlist contains DK because it is in the country database, where I get the information from. So if the reportingservices user selects something in the parameter like "DK, GB", I could query something like: SELECT bla, blub, [Country Code] FROM Customer WHERE [Country Code] IN (@Country_Paramater, '') The , '' because I also want to have the empty country codes. This works fine... until I decide NOT to show domestic data in the parameters. Example: Reportingservices user select 'DE, GB' The same query would now return not only DE and GB but also DK. First I have tried to create a temp-table where I replace the empty info with 'DK', but that would slow down the report from 1sec to 120sec - not good enough for my boss ;-) I then have tried something in the query like: [..] WHERE( ( @Country_Parameter LIKE '%DK%' AND [Country Code] IN (@Country_Parameter, '') ) OR ( @Country_Parameter NOT LIKE '%DK%' AND [Country Code] IN (@Country_Parameter) ) ) In the query (e.g. in SQL Management Studio) itself it works fine, but as soon as I start the report, it throws an error like "There was a non-boolean expression next to the ',' where there was expected a condition" (I translated that part). So now I am at a point where I really don't know how to get the result. I hope anyone is able to understand what I am trying to explain and might also have a solution for my problem ;-) Thanks in advance for your time!
October 29th, 2010 3:14am
Hi R.Hinrichs, From your information, I think you should have a judgment to see whether the Parameter@Country_Parameter contains DK in advanced in the T-SQL, and then execute the relevant T-SQL chunk based on the parameter value selected. Thanks, Challen Fu
November 3rd, 2010 6:26am
Why dont you try you SQL in following Way SELECT T.* FORM (SELECT X,Y,Z, CASE WHEN CountryCode ='' THEN 'Native' ELSE CountryCode END AS CountryCode ) AS T WHERE T.CountryCode = @Parameter OR T.CountryCode !='Native' Gaurav Gupta http://sqlservermsbiblog.blogspot.com/
November 5th, 2010 10:42pm