Passing a Multi-value parameter to the ReportViewer control RSS

• • Reply
  

  Rob Stanley

  0 Points

  3 Posts

  Passing a Multi-value parameter to the ReportViewer control

  Oct 23, 2008 01:52 PM|LINK

  I am hooking up a site with an embeded ReportViewer control. I need to pass a Multi-value parameter (string data type) to the report. I have tried using the following methods but each time the report errors with a parameter value not set exception.

  List<ReportParameter> paramList = new List<ReportParameter>();

  List<string> paramValue = GetParamValue();

  // method 1 comma delimited string

  string s = String.Join(",", paramValue.ToArray());

  // method 2 carriage return new line delimited string

  string s = String.Join("/r/n", paramValue.ToArray()) + "/r/n";

  // method 3 values as a string array

  string[] s = paramValue.ToArray();

  paramList.Add(new ReportParameter("ParamName", s, false));

  Obviously the above code is not exactly what I am using but it does show the variations I have tried.

  Hope someone can help.

  Thanks,

  Rob.

• • Reply
  

  mark@markhem...

  Member

  19 Points

  80 Posts

  Re: Passing a Multi-value parameter to the ReportViewer control

  Oct 23, 2008 02:29 PM|LINK

  This is the VB code I am using...on the report side, the parameter names have to match EXACTLY to be picked up.

   HTH

  Mark  

   

  Dim myparam As ReportParameter

  Dim myparams As New List(Of ReportParameter)

  myparam = New ReportParameter("Letter", Server.HtmlDecode(Activity.Description))

  myparams.Add(myparam)

  myparam = New ReportParameter("FSize", Fsize)

  myparams.Add(myparam)

  ReportViewer1.LocalReport.SetParameters(myparams)

• • Reply
  

  Rob Stanley

  0 Points

  3 Posts

  Re: Passing a Multi-value parameter to the ReportViewer control

  Oct 24, 2008 08:00 AM|LINK

  Hi Mark,

  Thanks for the response. The parameter names do match exactly. I have also had a colleague confirm in case I'm going blind [:)]

  I am passing five other single value parameters of various data types without any issues. It is only the Multi-value parameter I am struggleing with.

  Thanks,

  Rob.

   

• • Reply
  

  Rob Stanley

  0 Points

  3 Posts

  Re: Passing a Multi-value parameter to the ReportViewer control

  Oct 24, 2008 12:53 PM|LINK

  OK, I think the intellisense documentation is a bit missleading. In order to add a Multi-value parameter you cannot use a string or string array. Instead you have to create an new instance of the ReportParameter class. If you check the values property it is of type StringCollection. in order to add the string array to the StringCollection you can use the StringCollection.AddRange(string[]) method. E.g.

  List<ReportParameter> paramList = new List<ReportParameter>();

  ReportParameter param = new ReportParameter("ParamName");

  // Create the string array of values to pass

  string[] values = new string[]{"x", "y", "z"};

  // Add a range of elements from an array to the end of the StringCollection.

  param.Values.AddRange(GetStringArray());

  // Add the parameter to the list of ReportParameters

  paramList.Add(param);

  // Set the reports parameters

  this.ReportViewer1.ServerReport.SetParameters(paramList);

   

  I hope this helps someone else out, or was I just being stupid [:)]

• • Reply
  

  vikramsinh

  Member

  209 Points

  80 Posts

  Re: Passing a Multi-value parameter to the ReportViewer control

  May 14, 2009 12:29 PM|LINK

  Its Work perfect for me

  Thansk a lot sir

   

  "Mark as answer" if this post is useful.
  Vikramsinh Patil.
• • Reply
  

  jrattinger

  Member

  2 Points

  1 Post

  Re: Passing a Multi-value parameter to the ReportViewer control

  Jun 06, 2009 07:01 PM|LINK

   Hi All,
  
  I’m hoping that someone here can help me. I tried to apply the answers above, but no luck. Also, I'm using VB, not C#. I’m a relative newbie to SSRS/MVReportViewer, & .net. I have a vb.net app that will set report parameters and send them to a “ReportViewer” widget that will in turn run a remote report that was created in VS2005 and uses SQLServer2005.
  
  I’ve been spinning my wheels for days on some of the following questions:
  
  - In my report query I have an IN clause (IN (@accountlist)) that is setup as a multi-value string parameter. When I set the report parameter “accountlist” to one value it works perfectly, more than one, it fails. I have tried many formats for the list – “1,2,3″ or “‘1′,’2′,’3′”, etc……….
  
  Here’s my VB code that sets up and runs the report:
  

   

  'Set the report Processing Mode
  ReportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote
  
  'Set the Report Viewer - Server URL & Report Path
  ReportViewer1.ServerReport.ReportServerUrl = New Uri("http://localhost/reportserver")
  ReportViewer1.ServerReport.ReportPath = "/ljm reports/" & LJM_Reporter.ReportName
  
  'Setup parameter collection
  Dim pInfo As Microsoft.Reporting.WinForms.ReportParameterInfoCollection
  Dim paramList As New Generic.List(Of Microsoft.Reporting.WinForms.ReportParameter)
  
  'Insert parameter list - note - LJM_Reporter.AccountList is a string
  paramList.Add(New Microsoft.Reporting.WinForms.ReportParameter("accountlist", LJM_Reporter.AccountList, False))
  
  'Here are some examples of formats that have not worked
  'paramList.Add(New Microsoft.Reporting.WinForms.ReportParameter("accountlist", "'0000R9Y199', '00001366W7'", False))
  'paramList.Add(New Microsoft.Reporting.WinForms.ReportParameter("accountlist", '0000R9Y199','00001366W7' , False))
  
  ReportViewer1.ServerReport.SetParameters(paramList)
  
  'Run the Report
  Me.ReportViewer1.RefreshReport()

   

    I have come across a few postings on help sites that mentioned that a spliter (tokenize values that are in my parameter string and delimited by a comma) function (UDF) is needed for stored procedures. I am not using a stored procedure but thought this might be part of my problem anyway. My report query was created in VS2005 in the data tab of my report project. Do I need a spiter for this report query? Interestingly enough, when I run the report by itself in preview mode and input multiple values from the report parameter area on top for account_list it works.
  

  
  - I’m also running into problems with the UDF function spliter. I created the function in SQL Server and it works fine. I put it into my select statement as such – “IN (SELECT item FROM dbo.fnSplit(@account_list, [,]) AS fnSplit_1)”. When I run the report I now get several errors. I get “must declare the scalar variable “@accountlist” and I also get one for another parameter for some strange reason. Since this is not a SP, I’m not sure how to resolve.
  
  I have other questions as well, but these are the one’s that are driving me crazy at the moment. I can provide more detail if needed or my rambling is unclear.
  
  Any advice would be greatly appreciated!
  
  Regards,
  John
  
  

• • Reply
  

  jurcisinjaku...

  Member

  35 Points

  12 Posts

  Re: Passing a Multi-value parameter to the ReportViewer control

  Jun 13, 2011 03:22 PM|LINK

  Hi Rob

  Could you share that bit of code with me where you're passing single values.

  Thanks, Jakub