Hey, 1st question: How can we restrict the user to just enter at the most 10 values in my SSRS parameter box? So, he can probably enter like this A,B,C,D,E,F,G,H,I,J... But if he enters A,B,C,D,E,F,G,H,I,J,K , I want to give a message like "Only 10 values can be accepted". I just want to fetch a report based on multiple values of ID's that they pass. So, I don't want a dropdown list of the parameter, where the user can "tick" his selections. What I want is, I want him to enter those ID's in that parameter box, separated by a comma. So if he clicks on "View Report", he can get the report related to those ID's. Also, I just want him to enter 10 values (not more than that). ID's is a "CHAR field". How can this be done in SSRS? How will my where clause look like?

Hi There Please put this custom code into your report which will convert your string to array Public Shared Function ConvertStringtoArray(sourceString as String) As string() Dim arrayOfStrings() As String = sourceString.Split(",") return arrayOfStrings End Function Public Shared Function ConvertStringtoArray(sourceString as String) As string() Dim arrayOfStrings() As String = sourceString.Split(",") return arrayOfStrings End Function And now please go to your dataset properties and go the parameter as shown in the screenshot and put the expression like this =code.ConvertStringtoArray(Parameters!Multi.Value) =code.ConvertStringtoArray(Parameters!Multi.Value) And in your dataset your query will be like this SELECT TestCase, Step1, PassStatus FROM TestCase where TestCase in(@Multi) SELECT TestCase, Step1, PassStatus FROM TestCase where TestCase in(@Multi) You are done J If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

The report is built out of a stored proc. So, what I did is, I created my stored proc and in the where clause of my stored proc I added, where ID In (Select value from dbo.ufn_split(@Id,',')). Now, I added that report code in my report, and I went in to my dataset properties and assigned this to my parameter expression: =code.ConvertStringtoArray(Parameters!Id.Value) Do i need to add another dataset and have my parameter take available values or something? Because currently, I get an error,

Hi There Thanks for your posting. Can you please try with out stored procedure. If it is working in the datset then n please try to put Split(Join(Parameters!Your_Parameter_Name.Value, ,), ,) and see if it is working Many thanks Syed Qazafi Anjum

Doesn't work. You know what Syed, I just used an Inline SQL and entered 5 Id's separated by a comma and it gives me the data. I am trying in stored procedure now.

Hi There You might be doing something wrong because I just tested it on the Stored procedure as well and it is working fine for me How did you assign this value Many thanks Syed Qazafi Anjum =code.ConvertStringtoArray(Parameters!Id.Value).DId you went to stored procedure dataset and go to parameter and put values @id and in expression you have put =code.ConvertStringtoArray(Parameters!Id.Value).

For stored procedure's where clause, if I just use where ID In (Select value from dbo.ufn_split(@Id,',')) and just use that for my report body without using any custom code and creating another dataset, then my report works fine.

Any more suggestions? Ideas?

Hi SqlCraze, Thanks for your posting. From your description, you want the report returns no data and prompts a message when the user enters more that ten parameter values which are comma-delimited. If I have anything misunderstood, please feel free to correct me. To work around this issue, we can set the visibility of the data region based on the parameter count number so that no data will be displayed when the user enters more than ten parameter values. Meanwhile, we can add a texbox above the data region to display the warning message. Let's assume the target parameter is "ID", then, please follow the steps below: 1. Right-click on one region (table/matrix/list/chart, etc), set the visibility of each data region based on the expression below: =IIF(Parameters!ID.Count>10, True, Flase) 2. Add a textbox to the top of report body, type in the warning message you want to display and then set the visibility of the textbox based on the following expression: =IIF(Parameters!ID.Count>10, False, True) Additionally, if you have any problems with passing multi-value parameters to the stored procedure, please refer to the following thread: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/0ead7ceb-3fdd-4625-aa82-1d4195f984b1/#f630f267-ae32-420b-a9ee-20fb79276f5d Regards, Mike YinMike Yin TechNet Community Support

Hi SqlCraze, Thanks for your posting. From your description, you want the report returns no data and prompts a message when the user enters more that ten parameter values which are comma-delimited. If I have anything misunderstood, please feel free to correct me. To work around this issue, we can set the visibility of the data region based on the parameter count number so that no data will be displayed when the user enters more than ten parameter values. Meanwhile, we can add a texbox above the data region to display the warning message. Let's assume the target parameter is "ID", then, please follow the steps below: 1. Right-click on one region (table/matrix/list/chart, etc), set the visibility of each data region based on the expression below: =IIF(Parameters!ID.Count>10, True, Flase) 2. Add a textbox to the top of report body, type in the warning message you want to display and then set the visibility of the textbox based on the following expression: =IIF(Parameters!ID.Count>10, False, True) Additionally, if you have any problems with passing multi-value parameters to the stored procedure, please refer to the following thread: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/0ead7ceb-3fdd-4625-aa82-1d4195f984b1/#f630f267-ae32-420b-a9ee-20fb79276f5d Regards, Mike YinMike Yin TechNet Community Support