Take Control of Your Reports with ReportViewer, Part 2

As you discovered in "Take Control of Your Reports with ReportViewer, Part 1" (October 2006), Microsoft Visual Studio 2005's ReportViewer control is multitalented.You can use it as a convenient interface to a SQL Server 2005 Reporting Services (SSRS) report server. In addition, you can use it for rendering local reports, freeing you from the need for a report server.

When the ReportViewer control renders reports locally, it requires the host application to take responsibility for some of the activities necessary for report creation. The activities include creating the data set and prompting the user for report parameters. In "Take Control of Your Reports with ReportViewer, Part 1," I showed you how to create and display a local report using mainly drag-and-drop programming to create the data set. Now I want to show you how to write code that provides the report-parameter prompting and the data set, which provides endless possibilities in the types of reports you can create. For example, you can create an application that stores the local report definitions in external files so that you can add new reports or update existing reports without having to recompile and redistribute the application.

The New Sample Report

Let's look at how you can use the ReportViewer control to render a main report and a subreport that aren't embedded in the application's .exe or .dll file.The definitions for these two reports exist as Report Definition Language (.rdl) files in the folder from which the application is being run. These reports are created as report server reports in the Business Intelligence Development Studio, so they have the .rdl extension rather than the Report Definition Language Client (.rdlc) extension.

The main report, EmployeeTime.rdl, provides users with a multiselect parameter, which is used for choosing one or more job types to be reported on. A prompt and a list of the job types are hard-coded in the report parameter definition, as Figure 1 shows.

When a user selects one or more job types, the report displays the total number of hours worked by employees of each type.The user can drill down to see information about the individual employees in a job type, which weeks they worked, and the hours worked in a given week.The user can also expand the work history to reveal a subreport named EmployeeAssignments.rdl, which shows the work assignments for an employee.

How to Render the Local Report

The example for this article uses a Windows forms application to host the ReportViewer control. On the form, you need to add a ReportViewer control to display the report. You also need to add a label and checked list box (which are used to prompt the user to specify one or more job types) and a button (which lets the user render the report after selecting the job types). To make these items functional, you have to add the necessary code.

Adding the code that loads the label and checked list box. To make the label and checked list box work, you need to add the code in Listing 1 to the form's load event. (You can download fully commented versions of the code discussed in this article from the SQL Server Magazine Web site.) This code will extract information about the report parameter from the report definition and use it to load the label and checked list box. Because the information about the report parameter is already defined in the report definition, it makes sense to use that metadata rather than hard-coding it again in the application.

As callout A in Listing 1 shows, the code sets the ReportViewer control's Processing-Mode property to local processing and sets the local report's ReportPath property to the EmployeeTime.rdl file. This code assumes the .rdl file is in the same folder as the executable, so no folder path is needed. (Alternatively, you can load the content of the report's .rdl file into a stream or text reader and use the local report's LoadReportDefinition method to load that stream or text string.)

After the report's definition is specified in the local report's ReportPath property, you can extract information from that definition. In this case, you need to extract information about the prompt and the list of job types for the report parameter. To do so, you must examine the collection of parameter information embedded in the report definition.

The ReportViewer control doesn't do any database access. The parameter's Valid-Values collection contains the data because the data is hard-coded in the report definition. If the report definition had used a data set to populate the list of job types, those values wouldn't show up in the Report-Viewer control. Instead, the ValidValues collection would have been set to Nothing and you would have to duplicate the data set in the application.

As I noted previously, the code in Listing 1 makes assumptions about the report being rendered and the parameters it requires. However, with some creativity and a bit more code, it's possible to create a more generic application. For example, you can write code that points to any RDL file, then determines the number of required parameters and their data types. With this information, your application can create the right type of data-entry control for each report parameter.

Adding the click event code for the button. Listing 2 shows the Render Report button's click event code that you need to load into the form.As callout A in Listing 2 shows, the code first resets the ReportViewer control. This lets users change the job types and click the Render Report button multiple times to get different reports without having to exit the application. However, because you're resetting the ReportViewer control, you have to set the processing mode and the local report's ReportPath property again, as callout B in Listing 2 shows.

Next, the code takes the values selected in the checked list box and passes them to the local report. This might seem a bit strange considering that the ReportViewer control doesn't create the data set and that most report parameters are used solely in the WHERE clause of the data set query.

However, some report parameters are used in the report definition itself, so they're required by the ReportViewer control for report rendering. The ReportViewer control doesn't try to determine which report parameters are used in the data set query and which are used in the report definition. Instead, it requires that a value be supplied for every report parameter defined in the report.

As callout C in Listing 2 shows, the code provides the report parameter values by creating an array of ReportParameter objects. Each object has properties for the parameter name and a collection of values. The report parameter name is supplied in the New statement for the ReportParameter object. The code loops through selected values in the checked list box and adds them to the ReportParameter object's values collection.At the same time, the code also builds a comma-separated list of the selected values.

The code at callout D uses SqlConnection and SqlCommand objects to create a result set for the report. The comma-separated list just created is passed as a parameter to the SQL stored procedure that ultimately queries the database tables. The result set returned from the stored procedure is assigned to the collection of data sources used by the local report. For the report to use the data set properly, the name of the data set must match the name in the report definition. In this case, the name is Employee-TimeDS.

Finally, the code uses the RefreshReport method to cause the ReportViewer control to render the report. Although the report is ready for rendering, the subreport isn't. In order for the subreport to function properly, you need a bit more code.

How to Render the Subreport

There are two important parts to handling subreports with the ReportViewer control.The first part deals with the way the ReportViewer control finds the definition of any subreports referenced by the main report. Essentially, the ReportViewer control loads the subreport definitions using the same method used to load the main report. When the ReportViewer control reads the report definition from the file system based on a path specified in the ReportPath parameter, the control will read the subreport definitions from the file system as well. (When the report definition is loaded using the LoadReportDefinition method, you must use the LoadSubreportDefinition method to load the report definitions for any subreports.)

When the ReportPath parameter is used, the ReportViewer control looks for the subreports in the same folder in which it found the main report.The ReportViewer control uses the name of the subreport specified in main report definition as the name of the subreport definition file. It assumes a file extension of .rdlc. Even if you specify that the main report file has an .rdl extension, the ReportViewer control will still use an .rdlc extension for the subreport. So, to make the subreport code work properly, you must rename the subreport definition file from EmployeeAssignments.rdl to EmployeeAssignments.rdlc.

The second part of handling subreports deals with creating the data sets for the subreport. Each time a subreport is rendered, you must use the local report's Subreport-Processing event to supply code that creates the necessary data sets for that subreport. When a subreport is rendered, this event fires. It's up to you to handle this event properly, which brings us to the code between callout A and callout B in Listing 2.This code assigns a subroutine to serve as the event handler for the SubreportProcessing event. In this case, the subroutine is named GalacticSubreportProcessingEvent-Handler.

GalacticSubreportProcessingEventHandler first gets the value for the Employee-Number parameter passed from the main report to the subreport. The subroutine then uses the EmployeeNumber parameter's value to create a result set and adds that result set to the collection of data sources used by the subreport. As with the main report, the name of the data set must match the name in the subreport's definition.

With the necessary code in place, the application is now ready to render both the report and subreport. When a user selects entries in the checked list box and clicks the Render Report button, the user will get results similar to those in Figure 2.

Rendering Behind the Scenes

In addition to being added as a visual control on a form, the ReportViewer control can function as an object instantiated in code without a visual presence. When used in this manner, you can use the Report-Viewer control to render reports in any of the supported rendering formats, including spreadsheet (.xls), PDF, and XML. You can then use these rendered reports within the application or write them to a file. You can also render reports in Enhanced Meta-File (EMF) format and send them directly to a printer. You can even render the report as an image and use that image as the background to a control or form.

Listing 3 shows the code that makes our sample report a background image in a form. As callout A in Listing 3 shows, the code uses a subroutine to create an instance of the ReportViewer control, point the viewer at the report definition, and supply the required data set. Although this code is similar to that used in Listing 1 and Listing 2, the rest of the code in the subroutine differs from what you've seen so far. As callout B in Listing 3 shows, the subroutine defines an XML structure named deviceInfo. This structure provides information (e.g., page size, margin size) required by the various rendering formats. Next, the code prepares for the Render method, which will place the rendered report's pages in a list of streams (one stream for each page). The preparations include initializing the list of streams to an empty list and providing a callback function that will create a new stream for each new page.The callback function, which is named CreateStream, appears at the end of Listing 3.

With the necessary preparations made, the subroutine finally calls the Render method.The method's first parameter specifies the rendering format. As callout C in Listing 3 shows, the format in this example is Image. After the method is called, you can use the streams as desired. In this case, the first and only stream is the first page of the Employee List report, which is converted to a bitmap and assigned to the form's BackgroundImage property. Figure 3 shows the result.

Reports Anywhere and Everywhere

The ReportViewer control is an extremely flexible tool. It supplies you with a convenient way to display reports residing on a Reporting Services report server. It also provides the means for you to render reports in a completely self-contained environment, severing your ties to the report server. To borrow an old advertising slogan, with the ReportViewer control, Reporting Services reports can truly be everywhere you and your users want to be.