Solved

Passing list of integers to SSRS report from C#

Posted on 2014-01-28
10
3,089 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now