Managing Server Reports

I'm going to take a bit of a side trip at this point in the tour to show you how to use the ReportViewer to execute SQL Server Reporting Services 2005 reports. This is an important feature that might be overlooked by some developers or architects. In the simplest implementation, invoking an existing server report is merely a matter of knowing what to fill in when prompted by the Reporting Services Task dialog from the ReportViewer control. Let's walk through the basic steps.

1.	Starta new Windows Forms project and drop a ReportViewer control on the form.
2.	Click the ReportViewer Tasks dialog button and select "<Server Report>" from the Choose Report dropdown.
3.	In the Report Server "Url" TextBox, enter the name of the target SQL Server 2005 Reporting Services server that's hosting the deployed RDL report. No, by default, you might not be able to get "localhost" to work as the machine nameeven if the Reporting Services 2005 server is hosted on the local system. This means you'll have to fill in the actual machine name hosting Reporting Services. If your server is configured for encrypted traffic, you'll want to use https://; otherwise, http:// can be used as the protocol. Be sure to end your reportserver name with the instance name. By default, the instance name is separated from the reportserver service name with a "$". In this case, I use https://betav9/reportserver$ss2k5 to address the virtual folder named "ss2k5" hosted on the targeted system's IISin this case, Betav9. Peter is totally opposed to naming these virtual folders with the "$" character to indicate that this folder somehow corresponds with a SQL Server instance name. These can be set as such (with the "$") only during initial setup. This means that if something goes wrong and you have to re-create your IIS folders, you're pooched. I recommend that you get with your IIS administrator and DBA and come up with other, more appropriate IIS virtual folder names.
4.	In the Report Path TextBox, enter the name of the report to render. Note that this name begins with a forward slash (/) and includes the full path to the deployed report. Figure 14.25 shows how the ReportViewer tasks dialog is completed for a server report. Figure 14.25. Accessing a server report on Reporting Services 2005.

Rendering the Server Report

When your application that references a server-based report is executed, the ReportViewer control merely addresses the report as it would be referenced from Internet Explorer (IE). It's rendered within the ReportViewer control along with all of the parameter selectors and other options afforded an IE execution. Actually, you don't have to render the report for the ReportViewer control to access the server-side report. When you reference the ServerReport object, the ReportViewer control must fetch the RDL report definition from the server so that it can expose the underlying properties. Yes, this takes a round-trip to the server to fetch, and it does so by executing SOAP protocol commands over the network.

Figure 14.26 is shown by the ReportViewer when executing the server report previously specified.

Figure 14.26. Using the ReportViewer to render a server-hosted RDL Reporting Services 2005 report.

Note that when rendering any Reporting Services report, all of the output re-rendering options are available (as the drop-down list shown in Figure 14.26 illustrates). In addition, your application did not have to do anything to build pick lists or manage parameters in any way. Since the data queries execute entirely on the remote server, the ReportViewer control only needs to be invoked with the ReportViewer RefreshReport methodyou won't have to worry about building TableAdapter or DataTable objects on the client.

Managing Server Report Parameters

Okay, so suppose you want to handle some of this work yourself in your application. For example, you might want to capture one or more of the parameters yourself instead of giving the user the option of setting them on their own. Before you launch off and begin implementation of this, remember that the Report Manager application supported by Reporting Services can also manage report parameters, as shown in Figure 14.27. Yes, this is discussed in great detail in our Reporting Services book.

Figure 14.27. Managing report parameters using the Reporting Services Report Manager.

While this is cool, you still think that there are a few more things you want to do with the report parameters and perhaps other aspects of the report rendering. Well, the ServerReport instance is your pathway to the select report parameters and other options. Let's see how you can manage the ServerReport instance configured by the ReportViewer control.

Exploring ServerReport Parameters

To explore the parameters used to drive this server report, I coded a routine to dump the contents of the ServerReport parameters collection as returned by the GetParameters method. It turns out that a GetParameters method is also exposed on the LocalReport. The code for the routine to explore the parameters is shown in Figure 14.28. Note that the GetParameters method returns a collectionthe ReportParameterInfoCollection, which contains ReportParameterInfo objects. Each of these objects describes the individual parameters. For example, you'll find the parameter name, the valid values, and the currently set value(s). Yes, a parameter can have more than one value, as Reporting Services 2005 and the ReportViewer support multi-valued parameters. I'll discuss this aspect of parameters later in this chapter. Sure, there are a lot more properties to explore here, and I'll leave that adventure to you and your spare time.

Figure 14.28. Dumping the contents of the server report parameter definitions.

The server report I showed you earlier contains the following parameter settings (as shown in the ListBox on the left of the form in Figure 14.29). This dump tells us a lot about the parameters required for this report but also gives us a number of hints as to how to manage them in code. Remember that Reporting Services generates the UI elements used to prompt and capture values for the report parameters.

Figure 14.29. Exposing the server report parameter configurations.

If you want to manage these parameters yourself, you might want to disable the report parameters UI section. This is easily done by setting the ReportViewer PromptAreaCollapsed property to True. However, if you don't disable the "Show Prompt Area" button, your user can simply click on the button to re-expose the parameter prompt area. This means you'll need to set the ShowPromptAreaButton property to False. No, these properties don't make much sense when you're working with a local (client-side) report unless you want to use them to show or hide your own home-grown prompt areas.

Next, if you intend to manage parameters yourself, you need to add UI elements or code to set the report parameters. Given the report I'm using in the example, this could mean that your code would need to implement (at most) 12 UI elements (6 labels and 6 TextBox/ComboBox controls) to prompt for and capture the parameter values used to execute the report. This can be very tedious and expensive if you consider that you might have hundreds of reports to manage with the ReportViewer control. To deal with this issue, you might want to generate the UI elements in much the same was as Reporting Services doesgenerate the UI elements automatically from the ReportParameterInfoCollection. I've done just that in the example written for this section (prjServerReports). Figure 14.30 illustrates a simple implementation of this approach. In this case, I walked the ReportParameterInfoCollection and picked off the parameter name and value(s). These were placed into Label and TextBox controls generated on-the-fly.

Figure 14.30. Generating Parameter prompts and TextBox controls to capture new values.

Sure, you might want to selectively choose which parameters to display, but this code should get you started. Part of the code used to generate these UI elements is shown in Figure 14.31. It's generic enough to permit you to generate UI elements from any Reporting Services report loaded into the ReportViewer. See the example on the DVD for the rest of this routine.

Figure 14.31. Building the parameter TextBox UI elements on-the-fly.

Yes, you could (and probably should) enhance this code to validate the values entered into the ComboBox controls to conform to the permissible values stored in the ReportParameterInfo objects. By using ComboBox controls, I have taken the first step toward that goalonly permissible values are listed in the control.

Resetting Parameter Values

Once new values are set, you'll need to reset the ReportParameterInfoCollection with the values. No, you don't need to change all of the defined parameters, but you will need to set them all. That is, even though you aren't changing a specific parameter, you'll still need code to "touch" each parameter. That's because the SetParameters method accepts a fully populated array of parametersall of them, not just the ones you've changed.

The routine shown in Figure 14.32 walks the array of controls I generated to display the parameters to the user and rebuilds a collection of ReportParameter objects that contain the Name and Value of each parameter.

Figure 14.32. Building an array of ReportParameter objects to pass to SetParameters.

Interesting ServerReport Parameters

As I wrote this chapter, I came across any number of ServerReport properties (some of which are underdocumented) that you might find interesting or perhaps essential as you manage parameters or other aspects of the ServerReport. These include:

• DisplayName: Sets the "display name" of the reportwhatever that is. This is pretty much undocumented.

• HistoryID: This property is used to specify that you want to render a specific report historical snapshot. Each of these reports is identified with a HistoryID.

• IsDrillthroughReport: Indicates that this report is a drill-through report. See the discussion of drill-through reports later in this chapter.

• ReportServerUrl: This property points to the Reporting Services server and SQL Server instance.

• ReportPath: This property reflects the name of the report you entered when filling out the ReportViewer Tasks dialog.

• ReportServerCredentials: Used to determine the credentials to use to access the Reporting Services. This property is used to manage SQL Server rights access to the report server.

• Timeout: This determines how long the ReportViewer waits to render your report. The default is 600,000ms (10,000 minutes, 6.94 days, or the time it takes you to figure your taxes if you're filing a 1040A).

Interesting ServerReport Methods and Functions

I also came across a number of (also underdocumented) ServerReport methods. While it's beyond the scope of this book to get into these in any detail, I hope to supplement the book with a few EBook chapters that dig into the inner cavities of the ServerReport class. I also found several methods and functions that were simply undocumented. However, I expect that you'll find these documented methods as intriguing as I did. Note that all of these points are discussed in depth in our Reporting Services book.

• LoadReportDefinition: This method reads a data stream and installs it as a report on a specified Reporting Services server.

• Refresh: Basically repeats the RefreshReport method of the ReportViewer controlcauses the report to be rendered with new data.

• Render/RenderStream/ListRenderingExtensions: Permits you to send the report to a targeted rendering extension.

• SetDataSourceCredentials: When the Reporting Services Data Source is configured to require credentials, this method is used to pass those credentials.

• SetParameters: This reloads the report parameters as demonstrated and discussed earlier in this chapter.

As for the ServerReport functions:

• GetDataSources: Returns a collection, ReportDataSourceInfoCollection, that describes the Data Sources used to populate the report. Ah, in my tests, this method didn't return anything.

• GetDefaultPageSettings: The page settings are returned in a ReportPageSettings structure that exposes the Margins and PaperSize defined for the report.

• GetParameters: Again, I just discussed this function earlier in this chapter. It's used to fetch the list of parameters from the RDL report definition.

• GetServerVersion: Yup, this is interesting, but since the ReportViewer works only against SQL Server 2005, it won't be of much interest until another version ships. The current server version reports 2005.090.1399.00. I guess that's specific enough.

• GetTotalPages: This returns (you guessed it) the total number of logical pages in the report.

As you explore the rest of this chapter, consider that this approach can also be used when building generic parameter-management routines with local reports.