Pass dataset to subreport with SQL Server Reporting Services

report-designerreporting-services

I'm using SQL Server Reporting Services and the report designer that comes with Visual Studio. I've got a really big report. It's actually so large that Visual Studio hangs (sometimes for hours at a time) or just crashes when I make changes.

There is preciously little I can do to solve the problem, so I've decided to just move the bottom half of the report into a sub-report. So, I started with one enormous, unresponsive report and ended with two small, manageable reports — surprisingly, this actually works.

One problem: my subreport uses the same data as my main report. Right now, it populates its dataset by re-querying the database. The extra round-trip to the database causes the report to take twice as long to generate; up from 45 minutes to 1 1/2 hours to generate.

I'd like to avoid hitting the database again, and instead use the same dataset in both reports.

How can I share or pass a dataset between a report and subreport?

Best Answer

I think this can help you: http://www.gotreportviewer.com/subreports/index.html

Supplying data for the subreport - the SubreportProcessing event To supply data for the subreport you have to handle the SubreportProcessing event. Note that this event is on the LocalReport object. You can add an event handler like this:
private void MainForm_Load(object sender, EventArgs e)
{
    this.reportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(MySubreportEventHandler);
}
Below is an example for the event handler. In this example LoadSalesData is defined to return a DataTable.
void MySubreportEventHandler(object sender, SubreportProcessingEventArgs e)
{
    e.DataSources.Add(new ReportDataSource("Sales", LoadSalesData()));
}
If your report has multiple subreports you can look at the ReportPath property of SubreportProcessingEventArgs and supply data for the corresponding subreport. You may also want to examine the values of Parameters property of SubreportProcessingEventArgs and only return the subset of data that corresponds to the subreport parameters, as mentioned here.

Related Solutions

Reporting services: Get the PDF of a generated report

Several methods are provided at MSDN. I have used URL access often for quick simple PDF buttons in an ASP .NET app.

Here's a quick hack bit of code doing this. It could be cleaned up to use integrated authentication, and there are many ways you could store the report name. (I cut and pasted this from some old code I had that would store a report to a database and then later could return that from the db.

// First read in the report into memory.

string strReportUser = "RSUserName";
string strReportUserPW = "MySecretPassword";
string strReportUserDomain = "DomainName";

string sTargetURL = "http://SqlServer/ReportServer?" +
   "/MyReportFolder/Report1&rs:Command=Render&rs:format=PDF&ReportParam=" +
   ParamValue;

HttpWebRequest req =
      (HttpWebRequest)WebRequest.Create( sTargetURL );
req.PreAuthenticate = true;
req.Credentials = new System.Net.NetworkCredential(
    strReportUser,
    strReportUserPW,
    strReportUserDomain );

HttpWebResponse HttpWResp = (HttpWebResponse)req.GetResponse();

Stream fStream = HttpWResp.GetResponseStream();




//Now turn around and send this as the response..
byte[] fileBytes = ReadFully( fStream );
// Could save to a database or file here as well.

Response.Clear();
Response.ContentType = "application/pdf";
Response.AddHeader(
    "content-disposition",
    "attachment; filename=\"Report For " +
        ParamValue + ".pdf\"" );
Response.BinaryWrite( fileBytes );
Response.Flush();
HttpWResp.Close();
Response.End();

ReadFully is

public static byte[] ReadFully( Stream input )
{
   using ( MemoryStream ms = new MemoryStream() )
   {
      input.CopyTo( ms );
      return ms.ToArray();
   }
}

Best Answer

Related Solutions

Reporting services: Get the PDF of a generated report

Related Topic