Generate a Clickthrough Report using Visual Studio ReportViewer

Based on the number of questions I’ve seen about the Visual Studio ReportViewer control, it appears that many administrators don’t know that this control can perform sophisticated operations on its own. You might have seen examples of SQL Server Reporting Services (SSRS) clickthrough reports, but did you know that this same functionality is available when generating a local report with the ReportViewer?

A local report doesn’t require an SSRS server because the application provides the tools to distribute the report. So, even without benefit of SSRS, you can give the user the ability to execute parameter-driven reports of all kinds—even drillthrough reports. ReportViewer also lets developers manage input parameters and integrate reports with other UI elements on the form or ASP page. While it might seem challenging to implement a report with the ReportViewer control because you’re responsible for much of the leg work, in the end you’ll find this an essential tool when creating forms over data applications. This control gives developers virtually infinite flexibility in how the Report Definition Language (RDL) is presented. For a comprehensive explanation of how to create and manage reports with ReportViewer, see ReportViewer Samples for Microsoft Visual Studio 2008.

Although you can use either Visual Studio (VS) 2008 or VS 2005 to set up a clickthrough report without using SSRS, after SQL Server 2008 ships, VS 2008 will include SSRS hooks, which will make VS 2008 more useful for this project. (Note that in VS 2008 ReportViewer has been renamed Microsoft ReportViewer.)

Creating the Project and Datasource

To create a project and datasource, you first need to build an example application. Start by creating a new Windows Forms project in VS. I created a strongly typed datasource that points to the SQL Server AdventureWorks example database. I chose not to select any table, view, or stored procedure sources for the data set because I want to illustrate how to use the TableAdapter Designer to create a new TableAdapter from scratch. The following steps take you through the code generation process for the first TableAdapter.

1. In the new Windows Forms project, click the Data menu and select Add New Data Source.

2. For the Data Source Type, select Database.

3. Confirm the Data Connection or point to the AdventureWorks sample database. (AdventureWorks is available as either an optional installation choice or a download.)

4. When prompted for selected Database Objects, click Finish without selecting anything—no Tables, Views, Stored Procedures, or Functions.

5. Confirm this (lack of) selection. Doing so creates an empty TableAdapter .XSD DataSet.

6. Right-click the AdventureWorksDataSet.xsd file in the Solution Explorer, and select Open.

7. Click Toolbox to open the DataSet toolbox window in the VS UI. Now you’re ready to auto-generate the first of two TableAdapter classes to be used as report row sources.

8. Click and drag a TableAdapter to the XSD designer pane. Doing so relaunches the Table-Adapter Configuration Wizard but exposes a different set of options. Then click Next.

9. Select Use SQL statements, and click Next. You’ll see a dialog box in which you can enter a SQL query to return a rowset to populate the base report (in this case).

10. Type a SQL statement that returns columns required by the report and calls for a single input parameter, as shown in Web Listing 1.
Note that the SQL statement could be a stored procedure, but entering it yourself makes it easier to understand the process.

11. Click Advanced Options and clear Generate Insert, Update, and Delete statements to disable the generation of the code for these statements. You’re creating a report, not a data management front end.

12. Click OK and then Next.

13. In the Choose Methods to Generate dialog box, clear the check box to disable code generation for the Return a DataTable and GenerateDBDirectMethods. (You need only a Fill at this point.)

14. Click Next, and Finish.

Now that VS has completed the code generation for the first TableAdapter, repeat the process to create a second TableAdapter to return rows for the drillthrough query. The code generation process for the second TableAdapter is similar to the process for the first Table-Adapter, with a few changes. Repeat steps 8-14, but this time, use the Web Listing 2 SQL statement in step 11 to return the rowset. This parameter query uses the ProductID passed from the report processor as the input parameter.

After you’ve created the new TableAdapter, right-click the diagram and rename it StockByProduct. Now you have the two related row sources that you can use to generate a clickthrough report.

Creating the RDLC Reports

This example uses Report Definition Language Client-Side (RDLC) reports; one to show products by style and another to display details on a selected product. The first report displays pertinent information from the Products table based on the Product TableAdapter. The second report displays information from selected rows based on the ProductInventory TableAdapter. Although I won’t go into great detail about how to build a report, here is a brief summary of how to do so: Add a new Report item to the Project, and then add a Table Report item. Drag appropriate fields from the Data Source’s DataSet to the Table item. Note that these operations are recorded in the RDLC and that the DataSet name is an integral part of this process. (Note that when you’re working with report DataSets, you must use the DataSet name to help the report processor know where to apply the data you’re returning.) To complete the report, set some colors and a few formatting properties. For more information about building reports, see Take Control of Your Reports with ReportViewer. Figure 1 shows how the report appears in the designer when completed. Note that if you were to use existing report definition files, you’d need to reconnect them to the correct Data Source elements. The second report (as shown in Figure 1) includes data from columns from the StockByProduct TableAdapter rowset.

Linking to the Clickthrough Report

Now you’re ready to link the two reports together. The idea is to display the Stock on Hand report (shown in Figure 1) when the user clicks a Product Number item in the Stock report (shown in Figure 2) by passing the currently selected Product number to the Product-Inventory FillByProductNumber query as a parameter. Linking the two reports is accomplished by editing the RDLC report definition files using the Report Designer built into VS. The following steps take you through the process of linking two reports together:

1. In the Stock report, right-click the Product Number cell to open the Textbox Properties pane. (Note that because the Textbox report cell is a property, it can be set with an expression. This means you can trigger any RDLC report you want to—as long as the report processor can find it at runtime.)

2. Click the Navigation tab, and set Jump to report to the name of the drillthrough RDLC file, as shown in Figure 3.

3. Now, set up the parameter to be passed to the linked report. Click the Parameters button, which is shown in Figure 3.

4. In the Parameters dialog box, shown in Figure 4, enter ProductIDWanted as the Parameter Name and choose the expression for ProductID from the drop-down list. Click OK twice to return to the RDLC designer.

Setting Up the ReportViewer Control

To have VS generate most of the UI code, you can leverage the TableAdapter and VS’s ability to set up the UI elements to capture the input parameter, Style-Wanted, for the initial query. StyleWanted should be sufficient because you won’t need any UI elements to browse through the rowset or make changes. The following steps take you through setting up the ReportViewer Control:

1. Open the Form1 design-mode window.

2. Drag the Product data set from the Data Sources window to the form design surface. Doing so adds the ProductTableAdapter to the form’s generated code. It also adds a label and textbox to capture the parameter, and a Fill button to launch the Fill method defined for the TableAdapter. In addition, it supplies a DataGridView control, and Table-AdapterManager, BindingSource, and BindingNavigator classes that you don’t need.

3. Delete these unneeded classes; they’re used to provide update and scrolling mechanisms that you don’t need. All you want is the tool strip to manage the parameter and expose the Fill button. The result should look like Figure 5.

4. Now drag the ReportViewer control to the form. Immediately, the control prompts for the name of the RDLC report file or the path to an SSRS server-hosted report. It will look like Figure 6. Note that the control has been renamed Microsoft ReportViewer in VS 2008, and the control has been moved to the Reporting section of the toolbox.

When you add the ReportViewer control to the form or rebind the Data Source, VS drops a line or two of code into the Form_Load event handler because it doesn’t know where else to put it. However, you shouldn’t leave the code there. Instead, move the Fill method and RefreshReport method calls to the ToolStrip Button_Click event handler so they’re executed after you’ve captured the desired Style parameter from the user. Incidentally, the generated code won’t work because it ignores the fact that the Fill method requires an input parameter. See Web Listing 3 for the code.

When you enter a style value (such as “M”) in the ToolStrip text box and click the ToolStrip Fill button, this code executes the parameterized Fill method, binds the rowset to the Report DataSet, and shows the report hosted in the ReportViewer control.

Configuring the Clickthrough Report

At this point, the initial Products By Profit report should work. Now you need to have ReportViewer call the routine that invokes the StockReport FillBy-ProductID method and bind to the generated Data-Table. The following steps show you how to configure the clickthrough report:

1. First, make sure your form’s Class includes an Imports statement to help the compiler resolve the Microsoft.Reporting.WinForms namespace. The Imports statement permits VS to see the ReportViewer DrillthroughEventArgs: Imports Microsoft.Reporting.WinForms

2. Next, set up an input parameter for the Stock-Report using the VS Report Designer. As a reminder, click the StockReport in the designer and select Report, Report Parameters.

3. Add a new parameter. Figure 7 shows that the ProductIDWanted parameter is defined.

4. Mark the parameter as hidden because it’s provided only in code.

Coding the Drillthrough Event Handler

The next step is to respond to the Drillthrough event that’s fired when a user clicks the report cell (text box) that’s programmed to navigate to the Stock Report. This routine (shown in Web Listing 4) ensures that it’s the Stock Report that’s been requested.

Now you need to execute the parameter-driven Fill method and point the StockReport Data Source to the DataTable that’s returned. The code in Web Listing 5 handles this task.

The comments in this routine make it fairly self-explanatory. The base ProductProfitByStyle report should now run (after a style parameter is provided). When the user clicks the Product Number text box cell, the ClickThrough expression is programmed to run the stock query and display a report with the new data. To get back to the first report, the user needs to click the Back button (green arrow) on the ReportViewer.

Infinite Flexibility with ReportViewer

Congratulations. You now know how to set up a clickthrough report without using SSRS. In SSRS, there’s no need for extra code to generate the data sources because the code is written at design time and re-executed at runtime. However, using the VS ReportViewer control without SSRS gives you infinite flexibility because you can call whatever methods you desire to return the rowset consumed by the clickthrough report.