Hi I was trying to figure out if we could write expression in SSRS which will work similar to IN Keyword in TSQL. T-SQL: City IN ('Seattle', 'Phoenix', 'San Jose'). How could we re-write this in SSRS expression for Fields!City.Value ? Thanks in advance....................

Hi, You can pass value of parameter like this in data set designer. =JOIN(Parameters!City.Value, ",") You'll then be able to use WHERE Column IN (@City) in your query..Map @city to above expression..- Chintak (My Blog)

Hi Chintak, Thanks for your response. Well based on the city I am either hiding or displaying only certain fields. There are 4 or 5 different cities which can be grouped together for certain criteria instead writing (Fields!City.Value = "Phoenix" or Fields!City.Value = "Seattle") in the column visibility expression? Regards......

oh, just understood what you want hehe! In your hidden expression, you need to do a big IIF() or make a custom function. something like =IIF(Fields!City.Value = "SteaksonVille" OR Fields!City.Value = "CakeTown" ,True , False) You can also replace the True and the false by an other IIF! Hope this helps, Alex

Hi ione721, If you are planning to pass selected parameters (e.g. "India, Singapore" etc.) to the Visible expression for comparision then it will not work. Sadly there is no option available like IN query. if you know the fixed number of cities on which you wanted to make this column visible/hidden then you have use multiple IIF conditions by manually comparing each city with collection of parameters in VB Script expression. (you have to split the collection of parameter for comparing each value which is separated by comma ... i am considering that you have used JOIN() to read multiple parameters) Thanks, Khilit http://www.bigator.com

Hi ione721, Thanks for your posting. I think if you would like to hide or show your column based on multivalued parameter then you can use this expression, I hope this will resolve your problem =iif(instr(join(Parameters!City.Value),"Yourcolumnname")> 0,FALSE,TRUE) =iif(instr(join(Parameters!City.Value),"Yourcolumnname")> 0,FALSE,TRUE) If you have any questions please do ask. Many thanks Syed Qazafi Anjum

Thanks syed for your response. =iif(instr(join(Fields!City.Value),"Yourcolumnname")> 0,FALSE,TRUE) I was looking for something like that as I am not doing it against a parameter value but for a field value. I know the above expression would not work if the value is not from a parameter. I sure can use multiple OR conditions but was curious enough to know if there's a compact way of writing it. Regards..............

Hi Ione721, Base on my research, you cannot use a function directly on a field because Fields!Column.value returns the value for that row rather than an array of values. In your scenario, I am afraid there is not a compact way of writing it. You have to use multiple OR conditions to set the visibility property of this field to hide or display. Hope this helps. Regards, Charlie Liao

If you have 2008R2 then you can use the lookup functions Lookup(value to look for, field to look in, what to return if found, reference dataset) They work in a similar way to vlookup in excel but a bit more powerful http://msdn.microsoft.com/en-us/library/ee210531.aspx I've used them in a similar way to what I think you're after You have a reference list in a dataset and you can check values in your main dataset against that list example in your tablix thats bound to your main dataset you'd do something like =lookup( Fields!City.Value, Fields!RefCity.Value, "bingo", "ReferenceCity") and you'd have a ReferenceCity dataset that looks like RefCity Seattle Phoenix San Jose

Hi Ione721 Thanks for your posting. What you could do actually you can create a custom function which will convert your string values to array values the function might look like this Public Shared Function ConvertStringtoArray(sourceString as String) As string() Dim arrayOfStrings() As String = sourceString.Split(",") return arrayOfStrings End Function. Now you can convert you string fields which is comma delimited like this and use the similar fashion as we used the multivalued parameter =iif(instr(join(code.ConvertStringtoArray(Fields!Country.Value),","),"China"),true,false) Public Shared Function ConvertStringtoArray(sourceString as String) As string() Dim arrayOfStrings() As String = sourceString.Split(",") return arrayOfStrings End Function. Now you can convert you string fields which is comma delimited like this and use the similar fashion as we used the multivalued parameter =iif(instr(join(code.ConvertStringtoArray(Fields!Country.Value),","),"China"),true,false) I hope this will help Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

Thanks all for your response. @Syed how do we put in multiple countries, still it would be able to compare against that country which is hard-coded. Please correct me if I understood wrong.

Hi Ione721 Thanks for your posting again. I could not understand your question fully "how do we put in multiple countries, still it would be able to compare against that country which is hard-coded" Your Fields!Country.Value is now behaving exactly as multivalued parameter if you have string with comma delimited as function above will convert that into array of strings You do not need to hard code any thing you can even use this expression and I am sure it will work =iif(instr(join(code.ConvertStringtoArray(Fields!Country.Value),","),Parameters!Country.value),true,false) =iif(instr(join(code.ConvertStringtoArray(Fields!Country.Value),","),Parameters!Country.value),true,false) If you have any questions or if I misunderstood your question please let me know I hope this will help Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

Hi Ione721 Thanks for your posting again. I could not understand your question fully "how do we put in multiple countries, still it would be able to compare against that country which is hard-coded" Your Fields!Country.Value is now behaving exactly as multivalued parameter if you have string with comma delimited as function above will convert that into array of strings You do not need to hard code any thing you can even use this expression and I am sure it will work =iif(instr(join(code.ConvertStringtoArray(Fields!Country.Value),","),Parameters!Country.value),true,false) =iif(instr(join(code.ConvertStringtoArray(Fields!Country.Value),","),Parameters!Country.value),true,false) If you have any questions or if I misunderstood your question please let me know I hope this will help Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

Thanks Syed for your inputs was able to fix it. Regards............