Solved

Passing list of integers to SSRS report from C#

Posted on 2014-01-28
10
3,297 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

830 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