Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Passing list of integers to SSRS report from C#

Posted on 2014-01-28
10
Medium Priority
?
3,784 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

782 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