| Executive Summary: An experienced coder guides you through the steps needed to generate a local report using the Visual Studio ReportViewer control. See how you can create click-through reports without using SQL Server Reporting Services (SSRS). |
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 the Learning Path at www.sqlmag.com, InstantDoc ID 99192.
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 at www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004.)
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 (www.sqlmag.com, InstantDoc ID 99192).
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 the Learning Path at www.sqlmag.com, InstantDoc ID 99192. 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.
Continue on Page 2
Prev. page
[1]
2
next page 
