Pass dataset to subreport with SQL Server Reporting Services

Question

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?

Answer 1

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.