?
Solved

Passing list of integers to SSRS report from C#

Posted on 2014-01-28
10
Medium Priority
?
3,941 Views
Last Modified: 2014-01-29
I have a report With a parameter that takes mulitple values of int.
So that I can use it in a Query like:
Select * from Table
where ID in (@param)

I calling the report from my website.
How can I pass a list of integer from c#,
seems lik I can only pass a list of strings?
0
Comment
Question by:staleb
  • 5
  • 4
10 Comments
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 39816833
How  you are storing values in @param ?  

if you are storing as 2345  then pass this parameter to a SP .and there you convert it as table..

Declare @Param BigINt 
set @param=13235671

Select * from Table
where ID in (
select TOp(LEN(cast(@param as varchar(15)))) Substring(cast(@param as varchar(15)), Row_number() over(order by (select null)),1) 
from sys.objects )

Open in new window


Thanks,
saurabh
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39817010
I assume you're using the ReportViewer control to render the report?  If so, have a look at the following code:

// Set the processing mode for the ReportViewer to Remote
reportViewer.ProcessingMode = ProcessingMode.Remote;

ServerReport serverReport = reportViewer.ServerReport;

// Set the report server URL and report path
serverReport.ReportServerUrl =
	new Uri("http://<Server Name>/reportserver");
serverReport.ReportPath =
	"/AdventureWorks Sample Reports/Sales Order Detail";

// Create the sales order number multi-value report parameter
List<ReportParameter> parameters = new List<ReportParameter>();
ReportParameter salesOrderNumbers = new ReportParameter();
salesOrderNumbers.Name = "salesOrderNumbers";

// add array of values to report parameter
string[] values = new string[]{"SO43661", "S012345"};
salesOrderNumbers.Values.AddRange(values);
parameters.Add(salesOrderNumbers);

// Set the report parameters for the report
reportViewer.ServerReport.SetParameters(parameters);

Open in new window

The code above is a modified version of the code here: Using the WebForms ReportViewer Control [TechNet]

Let me know if something isn't clear here.  If you're using another method then please post your current code...

Saurv: when that query from the question is put directly into the report's dataset (not SP) then it doesn't need to get converted to "table".  SSRS knows how to handle it.
0
 

Author Comment

by:staleb
ID: 39817050
Hi

Valentino:  This is the code I tried. But since its sending in strings I get an error, that the report cant convert the nvarchar to int. SalesOrderNumbers need to be int values, since I'm using the parameter in the report as described earlier: "where ID in (@param)"
If I change the report parameter to string, the sql in the Query for the dataset wont work.

Or I'm I missing something here?

Maybe I just need to make the parameter in the report a string, and then use a function to create a temp table of int's and Select from the table in the where clause?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 39817148
The strings shouldn't be a problem, even with your report parameter being an integer.  With the code above it should work (I tested this).

This is also confirmed by the following documentation: ReportParameter Members.  As you can see, the Values property is a collection of String objects, there's no other option.

Are you sure the values passed into the parameter are valid integers?

Can you post your code just to be sure we're not overlooking another issue?
0
 

Author Comment

by:staleb
ID: 39817191
Here is my code:
 List<ReportParameter> rptParams = new List<ReportParameter>();
                   
                    ReportParameter pObservationID = new ReportParameter("ID_Observation", myObservationId.ToString());
                    ReportParameter pObservationFiles = new ReportParameter("ObservationFiles");
                                       string[] values = new string[] { _fileListRepresentation.ToString() };
                    pObservationFiles.Values.AddRange(values);
                    rptParams.Add(pObservationID);
                    rptParams.Add(pObservationFiles);

and _fileListRepresentation is:
 List<int> _fileListRepresentation = new List<int>();

As fare as I can see its the same code

You have the parameter in the report set to integer and allow multiple values, right?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 39817211
As far as I can see its the same code

More or less but not exactly... I think something must be going wrong when converting that List of ints to string array, more precisely:

    string[] values = new string[] { _fileListRepresentation.ToString() };

That code will put something like "System.Collections.Generic.List`1[System.Int32]" as first value in the string array, not very useful.

Replace that line with following code and I think you'll notice it works better:

    string[] values = _fileListRepresentation.Select(i => i.ToString()).ToArray();

BTW: looks like your report has got two parameters.  Is only the second one (ObservationFiles) multi-valued?
0
 

Author Comment

by:staleb
ID: 39817242
Hi
Changed the code, and I get this Message:
"Microsoft.Reporting.WebForms.ReportServerException: The value provided for the report parameter 'ObservationFiles' is not valid for its type. (rsReportParameterTypeMismatch)"

Yes I have two parameters, but one the one as multivalue.

The report runs fine if I run it directly from the reportserver
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39817322
Are you sure the ObservationTypes parameter is an integer?

I got that same error when I tried your code btw...  But it disappeared and the report worked with the following (as mentioned in previous comment):

    string[] values = _fileListRepresentation.Select(i => i.ToString()).ToArray();

Was this what you tried?
0
 

Author Comment

by:staleb
ID: 39817519
Hi
My mistake
Had the same code i another loop that I had'nt chagned.
It Works now.

Thank you!
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39817583
Phew ;)

Glad to hear you got it to work, good luck with the project!

VV
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
SingleRun is a tool that ensures that only one instance of an application is started, running it again brings the application to focus.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question