Reporting for Duty

SQL Server 2000 Reporting Services is one of the most exciting additions to SQL Server-related technologies in the past 2 years. Although Reporting Services began as a new feature of SQL Server 2005, its capabilities and potential generated so much interest in the community that Microsoft made it available with SQL Server 2000 as a stand-alone release.

Reporting Services exposes a Web service interface that makes it easy to integrate report management, report viewing, and report rendering into your custom applications. I show you how to programmatically retrieve and populate report parameters in code and how to generate and save reports in different export formats. Learn how to use these concepts, and you'll soon be including report generation in your own applications.

Which API to Use

Reporting Services is a rich and powerful reporting platform that provides multiple ways to view, export, and deliver reports.You can use the Report Manager application that's included with Reporting Services, or you can use one of the two available APIs: URL Access or the Reporting Services Web service (also referred to as the Simple Object Access Protocol—SOAP—API).These APIs give you easy, flexible ways to include reports in your custom applications.

Using URL Access, you can request a report over HTTP by specifying the report URL in a Web browser or in a browser control. For example, the URL http://server/ Reports/Pages/Report.aspx?ItemPath=%2f SampleReports%2fCompany+Sales accesses a report called Company Sales located in the SampleReports subfolder.

The SOAP API provides much more than report navigation and viewing. This API provides full programmability over Reporting Services, letting you program all aspects of report management, including administration, deployment, and report subscription control.

Each API has its proper place and its own advantages and disadvantages. Use URL Access when you need to provide only report navigation and report viewing in Web applications. Report rendering is faster through URL Access, and unlike the SOAP API, it renders the report toolbar in HTML reports.

Use the SOAP API when you need to provide additional management capabilities and when URL Access is not desirable for viewing reports. For example, avoid using URL Access when you don't want to expose your Report Server over the Internet; instead, expose a facade that generates reports by calling a back-end Report Server and then passes the reports to calling applications. Use the SOAP API when you want to automate report generation, create a tool for easily exporting reports into multiple output formats, or automatically archive reports.

Now let's get down to the basics of how to program Reporting Services using the SOAP API. I show you how to render reports, manipulate report parameters, and save generated reports in various export formats.

To make things easy, I provide a demo application called Report Generator, including the source code in C#, which you can download. To run the demo application, you must first install the Reporting Services samples and the AdventureWorks database. The sidebar "Installing the Sample Reports and Setting Up the Demo Application" (page 40) provides details about installing the samples and configuring the application to use the Employee Sales Summary report, one of the sample reports Microsoft provides with Reporting Services.

Figure 1 shows the Report Generator window that results when the demo application retrieves report parameters from a Report Server Web service and presents them on the screen using selected list boxes. You can select multiple parameters and multiple export formats. When you click Render, the application loops through your selections, calls the Report Server rendering engine with each combination of selected parameters and export formats, and saves the rendered reports in the specified folder.

Using the SOAP API

Because the SOAP API is a Web service, you can use any programming environment that can call a Web service. Most likely, you'll choose to use Visual Studio .NET, so that's the programming environment I discuss here. You can use the SOAP API in any type of Visual Studio project—a Windows,Web, or console application.

First, in the Visual Studio project, create a Web reference to your Report Server Web service. In Solution Explorer, right-click References and select Add Web Reference. You can search for Web services on your computer, or you can specify the URL of a remote Web service. In the Report Server Web service, refer to the ReportServer.asmx page so that your Web reference has the format http://YourReportServerName/ReportServer/ ReportService.asmx.

To create a more descriptive name for the Web service object, you can change the default value in the Web Reference Name text box. When you click Add Reference, Visual Studio generates a proxy file for accessing the Web service. This proxy file contains an interface for all methods defined in the referenced Web service. The proxy file also contains a Web service property called Url that contains the HTTP address of the Web service. When Visual Studio generates the proxy file, it sets the value to the URL of the referenced Web service. You can change the Url value at runtime and point it to a specific Report Server. It's good practice to read the value of Url from the App.Config file when the application loads instead of hard-coding the value, as the following example shows:

rs.Url = ConfigurationSettings  .AppSettings["RsUrl"]; 

Using this approach, you don't need to recompile your application when you deploy it or when you need to point it to a different Report Server. After you add a reference to a Report Server, you can simply call the SOAP API to use whatever functionality you need in your application. Listing 1 shows a sample call to the Web service to generate a list of all reports available on that Report Server. Notice that before you can use any functionality, your code must establish credentials on the server. The code at callout A in Listing 1 uses the DefaultCredentials object, which means that the application runs in the security context of the user running the application. Alternatively, you can use the NetworkCredential class to run under the security context of a specific user:

rs.Credentials = new  NetworkCredential(UserName,   Password, Domain); 

If you choose the network credential approach, ensure greater security for your application by encrypting the username and password before using them. To learn more about the methods and properties exposed by the SOAP API, I encourage you to read the Reporting Services Programming section of the Reporting Services Books Online (RS BOL) at http://msdn.microsoft.com/library /default.asp?url=/library/en-us/rsprog/htm/ rsp_prog_intro_1pia.asp.

Report Rendering

Now that you've seen the basics of using the SOAP API, let's move on to report rendering.The Reporting Services class in the SOAP API exposes the Render() method, which takes a multitude of parameters and returns a rendered report in the form of a byte array. I cover the most important parameters and mention a few things that you should be aware of when using the Render() method. Discussing all the parameters and details of this method would be lengthy and beyond the scope of this article, so I suggest that you read about it in RS BOL, too. The documentation for the ReportingService.Render method appears in the Reporting Services Programming section of RS BOL, at http://msdn.microsoft.com/library/default .asp?url=/library/en-us/rsprog/htm/rsp_ref _soapapi_service_lz_6x0z.asp.

The Render() method requires only two parameters: Report and Format. The Report parameter contains the full path to the report, starting at the root. The Format parameter contains a string that maps to a rendering extension that generates the specified export format. You'll usually choose one of these formats: Acrobat (PDF), Microsoft Excel (EXCEL), Web Archive (MHTML), HTML with Office Web Components (HTMLOWC), or Image (IMAGE).

You can also render reports in XML and comma-separated value (CSV) formats, but because the output for these formats is only text, you should use these formats only in special situations, such as when you use the raw data from these formats in an application or when you load the data into a database. Reporting Services also supports HTML 3.2 (HTML3.2) and HTML 4.0 (HTML4.0) output, but these formats often contain references to external resources such as images and therefore can't be saved into a single file. Listing 2 uses the Render() method to render a report in PDF format.

When the report is in a byte array, use the following code to save it in a file:

FileStream stream =   File.OpenWrite   (@"C:My Report.pdf"); stream.Write(results, 0,   results.Length); stream.Close(); 

This sample code doesn't show how to populate the reportParameters array. The ParameterValue argument is optional if the report doesn't use any parameters or if all the parameters are defined with default values. But if your report contains parameter values that differ from default values or you want to generate a report with a specific set of parameters, you must create an array of ParameterValue objects and populate it with the appropriate values.

Let's assume that the report referenced in Listing 2, My Report.pdf, requires a parameter called Year. Listing 3 shows a modified declaration of the ParameterValue object. First you create an array of the same size as the number of parameters in the report, then you populate the Name and Value attributes.

SQL Server 2000 Reporting Services doesn't support multivalue parameters, so you can specify only one value for each parameter if you use that version of Reporting Services. However, SQL Server 2005 Reporting Services does support multivalue parameters. Say that you want to render the same report with SQL Server 2005 and include the year 2004 in the report. In that case, you'd create two parameter values instead of one, use the same parameter name, and populate each Value property with a distinct value, as Listing 4 shows.

Your application's requirements determine how you should handle report parameters in code. For example, you might have situations in which you want your application to display parameters so that the user can select one or more parameters for rendering the report. Or your application might be scheduled to run without any user interaction and render a report based on static predefined parameters or on dynamic parameters read from an external resource. There are many ways to pass a set of parameters—configuration files, XML files, command-line parameters, or even database tables. The method you choose depends on your preference and on the application architecture. In some reports, the report parameters are naturally static and you can hard-code parameter values, such as Month or Quarter, into the application. Or you might have parameters whose values change from time to time (e.g., Year). Reporting Services lets you define dynamic parameters such as those based on database queries.

If you have report parameters that will change on each report, you can use the SOAP API and call the GetReportParameters() method to retrieve parameters defined in the report. Listing 5 contains some of the code from the demo application and shows this approach.

The code retrieves parameters for the Employee Sales Summary report using the GetReportParameters() method and populates three list boxes with lists of parameter value descriptions. The GetReportParameters method returns an array of ReportParameter objects. Each object contains an array of ValidValue objects in the ValidValues property. The ValidValue object has two properties: Label and Value. Label contains the descriptive name (e.g., January, February), and Value contains the internal value that the report uses (e.g., 1,2). The internal value often contains numbers, codes, or database IDs.

Be careful when you use the Label property in code. If you define a report parameter with the same value for both the Value and Label fields, the GetReportParameters() method populates the Value property but not the Label property.You then might get an error if you try to use the label in the code, because its value is null. Listing 5 shows how you can safely handle this concern in code. The Year parameter in the report is defined with the same value for both Label and Value (e.g., 2002, 2003). Because you'd get an error if you tried to use Label, the code that populates theYear list box checks the Label property. When that property isn't null, the code uses its value; otherwise it uses the Value property's value, as the code at callout B in Listing 5 shows.

When you use the GetReportParameters() method to retrieve valid parameter values and one or more parameters are based on queries, make sure you set the Boolean value of the ForRendering parameter to true. Otherwise, the method won't run the query that retrieves valid values for the query-based parameter and the internal value of ValidValues will be null, which can lead to errors in your application if your code tries to access those values. Because the Employee parameter in Listing 5 is based on a query, the code sets the value of ForRendering to true before calling GetReportParameters(), as you can see at callout A.

If your report uses hierarchical parameters, you must add code to retrieve all combinations of valid parameter values. Hierarchical parameters, where the value of the parent parameter determines which values are valid for the child parameter, are a feature of Reporting Services. For example, suppose you have a report where the first parameter holds a list of regions within the United States and the second parameter holds a list of states within each region. You can define a hierarchical relationship between these two parameters so that when you view the report and change the value in the region list, Report Manager automatically refreshes the screen and shows only the states that belong to the selected region.

When you call the GetReportParameters() method and the report contains hierarchical parameters, the SOAP API fully populates valid values in the parent parameter. For the child parameter, however, the API populates only values that are valid for that parent parameter value. In this particular report, the ReportParameters object returned from the server would list all regions but only the states that belong to the first region. Listing 6 shows how you can iterate through all possible combinations of parent-child parameters by using multiple calls to GetReportParameters().The code at callout A first calls this method to retrieve all parameters and sets the name of the parent parameter in the paramValues object. Then it iterates through the values of the parent parameter (Region), populates the paramValues property with the region code, and calls the GetReportParameters() method again. Because the paramValue variable is now populated with the name and value combination of the parent parameter, the SOAP API returns a list of child parameters related to the parent value passed into the method.

Using Report Generation

I've introduced you to the SOAP API interface and shown you the basics of rendering reports programmatically, including how to retrieve, process, and display report parameters. Using the concepts I've discussed and the demo application, you can see how easy it is to incorporate report generation into your applications for report delivery, report archiving, unattended report generation, and other types of reporting.