Pass data to ssrs report (.rdl) from code behind in ASP.NET C#, ProcessingMode=Remote

Question

I can see that there are many questions already being asked regarding the same thing like:

Binding a datasource to a rdl in report server programmatically - SSRS

SSRS using Xml datasource [WCF], its possible, but is it advisable?

and many others...

But, what I really want is to have your valuable opinion before I start something and finally find myself at a dead-end !

We are developing reports in BIDS SSRS 2008 environment, VS 2010 for a Web application. with ReportViewer 10 having ProcessingMode="Remote"

Until now, everything was ok.. passing parametetes to report, integration with asp.net etc. I mean, getting the data through SQL queries (and sometimes filtering through paramters). I could use Stored Procedures and all is working fine.

But now, there are reports which need to be generated dynamically, based on the user selection of the data from front-end, (a GridView ), on an asp.net webpage, and then display that data on the report.

That is, if a user selects 10 rows on a GridView and clicks the print button, then only those 10 rows (along with some other report specific data) should be displayed in the report. That's what we have in the legacy system, which we are upgrading now.
and of course, this cannot be achieved merely through passing the parameters, because selection of data on the GridView depends all on the user, and is all dynamic. .. there I am in a fix now!

So, what can be the best approach for these kinds of situations?

P.S (Edit): there are solutions out there but they are more pertaining to .rdlc , and I am talking about .rdl

Irf · Accepted Answer · 2017-02-13T05:38:49.487

Finally we implemented it like the following . I thought may be it is of some use to others. So am posting my solution:
This can be done with the implementation of XML
First convert your data into XML, like following:

private void ConvertToXml(ref XmlDocument xm)
{
 const string header = @"<ENVELOPE>";
 var strenvelopes = "";
 GridItemCollection selectedRows;
 selectedRows = dgAddressList.SelectedItems;

if (selectedRows.Count > 0)
{
 foreach (GridItem item in dgAddressList.SelectedItems)
 {                 
  strenvelopes += @"<ADDRESS>" + 
  "<NAME>" + "<![CDATA[" + ((object[])(item.DataItem))[2].ToString() + "]]>" + "</NAME>" +
  "<CONTACT>" + "<![CDATA[" + ((object[])(item.DataItem))[1].ToString() + "]]>" + "</CONTACT>" +
  "<STREET>" + "<![CDATA[" + ((object[])(item.DataItem))[3].ToString() + "]]>" + "</STREET>" +
  "<SUBURBSTATE>" + "<![CDATA[" + ((object[])(item.DataItem))[4].ToString() + "]]>" + "</SUBURBSTATE>" +
  "</ADDRESS>";                  
  }
}

   const string footer = @"</ENVELOPE>";  

   var envelopes = header + strenvelopes + footer;
   xm.LoadXml(envelopes);
}

You need to replace ((object[])(item.DataItem))[].ToString() with your own values
Then generate your report like following :

private void GenerateReport()
{
       var xm = new XmlDocument();
       ConvertToXml(ref xm);
       var xml = xm.InnerXml.ToString();
       rptViewer = ucrvEnvelope.FindControl("rptViewer") as ReportViewer;
       if (rptViewer != null)
       {
           rptViewer.ProcessingMode = ProcessingMode.Remote;              
           rptViewer.ServerReport.ReportServerUrl = new Uri("http://localhost/MyReports");
           rptViewer.ServerReport.ReportPath = "/Reporting/rptEnvelope";
           rptViewer.PromptAreaCollapsed = true;
       }
        ReportParameter myParam = new ReportParameter("list", xml);
        rptViewer.ServerReport.SetParameters(new ReportParameter[] { myParam });
        rptViewer.ShowParameterPrompts = false;
        rptViewer.ShowBackButton = true;
        rptViewer.ServerReport.Refresh();
  }

You should add a Parameter "list" to your report. because we will be passing whole XML thing to this "list" through the following Dataset,

Now the Dataset that will handle this XML:
Normally, what we do is you write some stored procedure or write some query inside the dataset, since ours is the XML, it needs to be handled XML way,
With the Query Type: Text write the following query to handle the XML data

DECLARE @docHandle int DECLARE @xmlDocument varchar(max); DECLARE @listXML nvarchar(max)
SET @listXML = @list
SET @xmlDocument = @listXML EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
    SELECT * FROM OPENXML (@docHandle, N'/ENVELOPE/ADDRESS') WITH
    (
      NAME nvarchar(max) 'NAME',
      CONTACT nvarchar(max) 'CONTACT',
      STREET nvarchar(max) 'STREET',
      SUBURBSTATE nvarchar(max) 'SUBURBSTATE'
    )

The Fields NAME, CONTACT etc. should be the same with what you created the XML document in the first place, at the top ConvertToXML

So the bottom line is, you are not passing data to report through strored procedure from DB but you are actaully grabbing data from aspx page - Code behind and converting the same to XML and passing it to your report-server report via this Dataset
So whats in the code behind is passed to your report.. voila !

So dynamically i can't add DataSource to remote processing? All we have to done with Parameters only? e.g. I want to filter datasource then all the procedure parameters i have to pass with report parameter and run the procedure at server side...something like that? — Snehal, Nov 03 '16 at 13:34
@Snehal the solution posted above lets you pass data to the report from code behind, in XML way. Here you need to query data in Code behind, make its XML and pass to report, and thus no use of stored procedure.. its actually _dynamic_ . Make sure you make the **list** parameter in your report, as it is mentioned. : ) — Irf, Nov 04 '16 at 13:44
what's the dataset datasource? it looks like the query is invalid. — koo9, Aug 13 '21 at 19:57

score 0 · Answer 2 · answered Oct 25 '12 at 20:42

Most likely, you may wind up needing to run them in the context of the web application, and pull the data from the datasource into the report by hand.

I did this project a few years ago, and wouldn't mind getting it back up and running, and finally completed to support MVC/Web Forms/Win Forms/WPF/etc. as well as get it more robust.

This may get you on the right track.

Pass data to ssrs report (.rdl) from code behind in ASP.NET C#, ProcessingMode=Remote

2 Answers2

Linked