Solved

Passing list of integers to SSRS report from C#

Posted on 2014-01-28
10
3,425 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.

738 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