Advanced Reporting Techniques

This section discusses a few of the more complex aspects of report creation and management, including how to set up click-through reports and how to include subreports in your reports. These topics are included, as they're programmed entirely differently than the techniques discussed in our Reporting Services book.

Managing Click-Through Reports

One approach that you're going to want to try is creating a "click-through" report. Imagine, if you will, a report that displays limited information about a productbut enough for most uses. However, to fetch additional information, the user is prompted to click on a specified field (as shown in Figure 14.42). This launches a "click-through" report (as shown in Figure 14.43). The magic here is that the ReportViewer is automatically reprogrammed to point to the targeted drill-through report and pass zero to many parameters to the report. That is, a new ReportViewer or Windows Form is not createdthe existing form and control are reused. This means that your user will have to use the "back" button to return to the host report. Thankfully, this transition is handled automaticallyas long as you use the "back" button. Sure, if you want to pass parameters to the drill-through report, you're going to need to write some code to fetch the parameters and run a custom query. I'll show how that's done later in this section.

Figure 14.42. A report hosting a drill-through report.

Figure 14.43. The drill-through report.

Note that this "drill-through" approach is different from a SubReport (that I discuss later in this chapter). In contrast, a SubReport is embedded in a Table report item cell or co-located on the report surface. In either case, you'll need to provide a fresh DataTable and Data Source to connect the report items with the data that populates them.

When accessing a report programmed for drill-through operations, once you click the pre-programmed Table report item cell, the ReportViewer launches the specified (possibly parameter-driven) report to show more detailed information about the selected cell, as shown in Figure 14.43. Behind the scenes, you might have to trap the ReportViewer Drillthrough event, where you execute the (parameter) query used to populate the drill-through report. I'll show you how next.

Okay, this seems cool, but getting to this point is a challenge, given the sorry state of the documentation. Let's walk through the process of getting to this statestep by step.

1.	Start a new project. I created prjClickThroughReport to illustrate the final product.
2.	I leveraged one of the previous reports that returns product information and created a new StockReport to launch as a drill-through report. Add these to your project along with appropriate Data Sources. I used two strongly typed TableAdapter classes to return the initial rowset as well as the parameter-driven drill-through report data. The StockReport query is customized to accept a ProductIDWanted parameter that focuses the returned rowset on the specific rows in the Inventory table for that product.
3.	Since this report is populated from a parameter-driven query, I need to specify a report parameter to pass along to the custom Fill method. Open the drill-through StockReport and choose "Report Parameters". Fill in the dialog with the Parameter name and default value, as shown in Figure 14.44. No, the prompt is not required in this case, as you won't be prompting the user for the valuebut you could if that seems more appropriate. Note that you set a default value, but this also is not used unless creating a user interface to capture the value from your human operator. By default, the parameter value is filled in automatically from the cell I'm about to program on the host Table report item value. Figure 14.44. Defining the drill-through StockReport report parameter.
4.	Next, open the host report, select the detail row "Product Number" column (as shown in Figure 14.42), right-click, and choose "Report Properties". I'm about to set up the linkage between the host report and the targeted drill-through report, and define the parameter to pass to the drill-through report.
5.	Choose the "Navigation" tab and select "Jump to report:". Choose the targeted report from the drop-down list, as shown in Figure 14.45. This instructs the ReportViewer to reconfigure the LocalReport class to address the specified report when the cell is clicked at runtime. Figure 14.45. Programming a Report Table item cell to redirect to a drill-through report.
6.	Since the drill-through report requires an input parameter (to focus the displayed results), click on the "Parameters..." dialog. This opens another dialog where you can specify how the report parameter required by the drill-through report is generated.
7.	Open the drill-through report (StockReport) in the Report Designer, right-click, and choose "Report Properties". This opens a dialog, as shown in Figure 14.46, that permits you to map each report parameter (in this case, ProductIDWanted) defined in the targeted drill-through report with a value. This value can be a constant or an expression, or can be drawn from an existing Fields value (as shown). In this case, I bound the report parameter to the current value in the selected row's Table item cell containing the ProductID. At runtime, when a Product ID cell is clicked, this value is passed to the drill-through reportas long as you write to code to do so. Click OK to save these settings and OK to close the TextBox properties dialog. Figure 14.46. Binding the drill-through report property to the parameter value. No, I'm not really donenot nearly. While the automatic linkages would work if I were not executing a special parameter query, I need to step into the process of launching the drill-through report and manage the parameter query used to populate the StockReport.
8.	Get into the code editor and set up the ReportViewer Drillthrough event handler. In this example (as shown in Figure 14.47), I first set up a variable to handle the report parameters (the ReportParameterInfoCollection). This structure contains a wealth of information about the report parameters that can be very useful when building UI to capture the parameters from your report user. Figure 14.47. Managing the Drillthrough event. Next, I pick off the LocalReport instance passed to the event handler in the DrillThroughEventArgs (line 19). This addresses the targeted drill-through report. I test to see if this drill-through report is the one I expect, and if this is the first time the event is called, I instantiate a new TableAdapter and DataTable to fetch and hold the rowset.
9.	The next block of code (shown in Figure 14.48) illustrates how to pick off a named parameter from the ReportParameterInfoCollection. I use the LocalReport GetParameters method to fetch the parameters (line 28). Note that this method also validates the parameter values, so if they don't comply with the datatype specified in the report parameter definition, the method throws an exception. Figure 14.48. Retrieving the report parameter and executing the custom Fill method. If for some reason no value is passed, I use a default parameter value of 0; otherwise, I set the inbound parameter value to the named parameter value. Note that the ReportViewer knows how to manage value arraysthat is, a parameter value can be the result of the user selecting several items. The UI setup does not support this, so I simply pick off the first (and only) value (line 33).
10.	The next block of code is responsible for executing the custom Fill method (FillByProductID) and populating the DataTable passed to the ReportDataSource. Note that the ReportDataSource name must match the DataSet Name element in the RDLC definition. Ordinarily, this is set when you drag columns from the Visual Studio Data Source to the report elements, but if you import the RDL from another source, it might be different.

Remember that when you use this approach, the user has to press the "back" button to return to the host reportunless you provide your own navigation buttons. Any Table item cell or TextBox item can be programmed as a "button" or hot-spot by setting the navigation properties.

Sure, if you want to support additional cells that drill through to other drill-through reports, you'll have to create logic in the Drillthrough event handler that checks to see which drill-through report is being requested. In the final version of the prjClickThroughReport application, I implemented this functionality to illustrate this, as shown in Figure 14.50. Once this is configured, the targeted drill-through report can act as a host and launch additional drill-through report(s). Remember, the ReportViewer control is maintaining a "stack" of reports so that your user can navigate back to the original report.

Figure 14.49. Executing the custom Fill method and setting the Data Source.

Figure 14.50. Branching based on the drill-through report name requested.

Using the SubReport Report Item

Another approach you can implement to show additional detail leverages the SubReport item. This technique is a bit easier to implement when compared to the drill-through reports I just discussed, but it includes a number of other nuances that I need to discuss.

The example application is similar to those I've already shown you, but in this case, I create a subreport that displays the available photographs of selected products. The subreport can be positioned in a number of places, including on the report surface or in a Table item cell. I chose the latter for this example, as it best fits with the report's interactive "finder-detail" design. That is, the host report simply lists products (that have photos) but does not show the pictures for each row. Even though the pictures are not initially shown for each item (as shown in Figure 14.51), the ReportViewer invoked the SubReport for each row returned in the Products query. This is pretty expensiveone round-trip query for every row in the Products table.

Figure 14.51. The host report with the SubReport item hidden.

In this case, I set the Visible property of the SubReport using a TextBox cell in the Table item. When the user clicks on the "+" sign, the ReportViewer toggles the SubReport Visible property. This is programmed via the SubReport item properties page, as shown in Figure 14.52. Sure, you can write an expression to hide the SubReport (or most items).

Figure 14.52. Programming the visibility of the SubReport item.

Once the user clicks the "toggle" item in the Table item cell, the SubReport item is made visible (as shown in Figure 14.53).

Figure 14.53. Toggling the Visible property of the SubReport.

No, this is probably not the best way to implement a subreport. A more efficient approach would be to construct the report so that the SubReport was not rendered along with each row fetched. In this case, the SubReport would be placed outside of the Table cells. The problem with this approach is that, unlike the drill-through report, you can't launch it from a cell in the Table report item. For the example (shown in Figure 14.54), I created a report that generates a small chart that reflects the overall sales of this product line. In this case, the code to generate its rowset is called only oncewhen the report is first rendered.

Figure 14.54. Rendering a SubReport item outside of the Table.

As you can see, I used the Chart report item in the last SubReport example. I devote an entire chapter to the Chart report item in our Reporting Services book. There, you will find details on how to configure the Chart report item to generate a wide variety of charts and graphs.

Note that Reporting Services 2005 now supports "control extensibility"that is, third-party vendors can (and have) adapted their custom report items to Reporting Services 2005. For example, the Dundas Chart report item has been so adapted. Unfortunately, these third-party report items do not work with the ReportViewer control. That said, it really isn't that much of a limitation. Because you have control over data extraction, you can use custom-built controls to return binary image streams and consume them within the image control. Conceptually, this is not much different from how the custom report items work.

What's in the RDLC?

I've often had to dig into the report definition file to debug one problem or another or resurrect a dead or crippled report. The Report Definition Language (RDL) variation generated by Visual Studio for use with the ReportViewer control is not that different. It has a different extension (RDLC) but shares a great deal of functionality with the BI-tools generated RDL files. The "C" stands for "client" as these reports are designed for execution on the clientnot the server by Reporting Services. Remember that while the RDL and RDLC report file contain information about the SELECT, the Data Source, and even the ConnectionString, the client-side ReportViewer control does not use all of these elements in the same way.

The <DataSets> Block

The core of the RDLC file is the DataSets block as shown in Figure 14.55 Note the presence of entire query (the CommandText attribute), references to TA Fill and GetData methods, SELECT, and DataSourceName and ConnectionString in the <DataSets> block. Many of these sections in the RDLC are required for it to compile (a process that's repeated each time against each RDLC file once you start the application). The <CommandText> element (this contains the SELECT statement) and the <rd:DataSetInfo> sections do not need to be filled in, as they are not run by the ReportViewer. These methods are up to your code to runnot the ReportViewer.

Figure 14.55. The RDLC report definition dumped to XML.

Note that the DataSet Name element must be present and must point to the DataTable (or equivalent)it's referenced in the LocalReport instance. Fields are mapped to report Fields but are otherwise not referenced. The values in this block are set when columns are dragged from the Data Source window to the report designer report items. These settings would be used if the report was to be rendered by Reporting Services, but since they're generated from a strongly typed TableAdapter, they can't be run on SQL Server Reporting Services as is.

Converting RDL to RDLC

Okay, so you got your boss to sign on to Reporting Services and you've invested a couple of months (or a year) creating reports using the BI tools or Visual Studio 2003's Reporting Services add-in. Now, you want to leverage that work with the ReportViewer control. Of course, the simplest thing to do is launch the server-side report using the ServerReport mode of the ReportViewer control. If you need to execute the report on the client for any reason, you'll need to convert the RDL to RDLC format. Unlike some other XML fiascos (as when you tried to use ADOc Recordsets persisted as XML in ADO.NET), the transition to RDLC is really pretty easy because the schema for both files is identical. While the ReportViewer control ignores the <Query> element, the other elements and attributes are processedalbeit in somewhat different ways.

Basically, to convert an existing RDL report to RDLC, you're going to need the RDL XML file. This can be extracted from SQL Server by using the Reporting Services Report Manager or by bribing the developer that owns the file. Ah, only Reporting Services 2005 RDL files can be imported and converted to RDLC format. If you're working with Reporting Services 2000, you'll need to use the BI tools to open each RDL report and let the tools convert the RDL to the new format. Of course, this is a one-way conversionthere is no tool to convert them back to Reporting Services 2000 format.

Once you have the RDL (2005-format) file, you need to follow these steps:

1.	Rename the .RDL file to .RDLC.
2.	In Visual Studio 2005, open the solution or project that's intended to host the report.
3.	Create the DataSet that returns the data using the same SELECT query defined in the reportit must return the same columns with the same names as the RDL SELECT. You can create the DataSet by any means you chooseusing the wizards or simply by rolling your own ADO.NET 2.0 code. This SELECT is contained in the RDL file in the <DataSets> element. Yes, there may be more than SELECT, so while this might be easy for a simple report, it can be a lot of work for a report that requires a number of queries. However, the RDL report can also contain queries used to populate pick lists, and while you don't need these to convert the report, your ReportViewer program code might need to implement these pick list queries along with the UI elements that are supported automatically in the report.
4.	On the project menu, click Add Existing Item, navigate to the RDLC file and click "Add". Yes, you'll have to change the "Files of type:" entry on the explorer to see the RDLC file you're converting.
5.	In the project, open the Form containing the ReportViewer control that will host the converted RDLC report.
6.	In the ReportViewer Tasks smart tags panel, select Choose Reports and select the .RDLC report you've just imported into the project.
7.	While still in the ReportViewer Tasks smart tags panel, select Choose Data Sources and choose the DataSet you want to use to populate the report.
8.	Save all of the files and test the report.

For more information and details about how to convert RDLC reports to RDL format, see http://msdn2.microsoft.com/en-us/library/ms252109.aspx.