Drill through reports in ASP.NET

In a recent ASP.NET application (coded in C# - natch) I had the cause to create a report, which contained a hyper link which in turn displayed a second report based on data from the first report. In researching this, I discovered this is called a drill through report.

I found information at CodeProject.com regarding drill down reports. The difference between what Shirley did, and what I was doing is that I already had the dataset for the report created. I created a dataset with two data tables in it. I haven't tried it, but I'm guessing that I could have easily put as many tables as I needed if I were going to drill down farther than just one level.

I used the same dataset for both reports. But when I went to perform the drill down, I received the following error:

"A data source instance has not been supplied for the data source [report data source name]"

So I slap my forehead, call my self a dunce, and make sure that both tables are represented by data sources (or would that be data sourcii?). At any rate, I kept getting the same error, which is what led me to googling the error message to see what the problem is. Based on Shirley's article, I realized that I would be required to set the data source for the new report in the ReportViewer_Drillthrough event handler.

Here is my code:

protected void ReportViewer_Drillthrough(object sender, Microsoft.Reporting.WebForms.DrillthroughEventArgs e)
{
    Microsoft.Reporting.WebForms.LocalReport localReport = (Microsoft.Reporting.WebForms.LocalReport)e.Report;
    localReport.DataSources.Clear();
    this.ObjectDataSource3.SelectParameters[0].DefaultValue = e.Report.GetParameters()[0].Values[0].ToString().Trim();
    Microsoft.Reporting.WebForms.ReportDataSource rds = new Microsoft.Reporting.WebForms.ReportDataSource("ReportDataSet_TABLE",this.ObjectDataSource3.Select());
    localReport.DataSources.Add(rds);
}

A bit of breakdown for those who have a hard time following my crappy coding style:
First off, I set a LocalReport object ot the DrillthroughEventArgs.Report object. Then I clear the data sources because I'm going to add a new one, and I certainly don't want the report getting confused. I set the default value of the SelectParameters[0] (the only parameter for this report) to the only parameter that is passed. If there are more parameters in your report, then you would, of course, be required to set them all. For this instance, I had only one.

Then I had to create a new ReportDataSource object. At first I tried simply added the ObjectDataSource3 to my localReport.DataSources, but that didn't fly because I guess an ObjectDataSource is different from a ReportDataSource. Then I figured I would just cast the ObjectDataSource as a ReportDataSource. But that didn't fly either. You can't cast the former as the latter. So I was resigned to create a new one. I gave the new one the same name as the DataSource I defined in my report. Then I added the ReportDataSource to my localReport.DataSources collection, and voila!

Oh, and don't try to refresh the localReport. For some reason it seems to resort back to the parent report. I don't know why, and frankly don't really care, as long as I know how to handle it, it's all good.




Technorati Tags: ReportViewer, ASP.NET, DrillthroughEventArgs, Drill Down Reports, ASP Reports, SQL Reports