Tips and Tricks: Microsoft SQL Server 2005 Reporting Services
Author:
August 2007
Situation
So, you have gotten your feet wet with Microsoft’s reporting services and are now ready to move on to more complex reports. Here are a few tips to help you avoid a several common pitfalls. All code snippets provided are in C#.
Tips & Tricks
For a basic, one datasource report, everything can be accomplished without having to type a line of code. The problem that you may run into is trying to dynamically load a report that contains subreports. For me, this is a common scenario, as I do not want to create a separate ASP.NET page for each report.
The first time you run a report with a subreport, you may get the following error:
A data source instance has not been supplied for the data source 'report_name'.
This is easy to fix. You must explicitly define all of the datasources that the report uses. This can be accomplished by adding the following line of code for each subreport:
- rptViewer.LocalReport.DataSources.Add(new ReportDataSource("qryName", dsYourDataSource));
When using ASP.NET, you can get the query name and datasource name by looking at the source view of the page that the report viewer is contained in.
So, now you try running your report and you see the following text where your subreport should be:
Error: Subreport could not be shown
Why is this happening you ask? Again, an easy fix. When the reporting engine processes the report, it generates an event letting you know when it is working on a subreport. If you add the following line of code to your report initialization routine:
- rptViewer.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(setSubProc);
This will add an event handler that fires off whenever a subreport is loading. This routine should look something like this:
- public void setSubConstructionDaily(object sender, SubreportProcessingEventArgs e) {
-
e.DataSources.Add(new ReportDataSource("qryName", dsYourDataSource)); - }
Yes, this is almost identical to what we did earlier. The only difference is that we are modifying the event arguments, rather than the report viewer. By the way, if you replace the variable ‘e’ with your report viewer, it will generate yet another error. This one occurs because the report viewer essentially becomes read-only while it generates your report.
One last thing to remember. Keep your subreports as simple as possible and keep the number of subreports to a minimum. The more complicated the report, the slower it will run. Depending on your data, you may be able to get away with more complicated reports and still maintain a reasonable execution time.
If you have any questions, please contact Gary Codeluppi at Ross Group Inc, 937-427-3069.
