Leveraging SQL Server 2000 Reporting Services

Creating Reports Can Be Fun and Productive

XtremeData

LANGUAGES: C#

ASP.NET VERSIONS: ALL

 

Leveraging SQL Server 2000 Reporting Services

Creating Reports Can Be Fun and Productive

 

By Dan Wahlin

 

ASP.NET developers spend their time in many different ways as they build applications that satisfy clients design requirements. These development activities typically involve creating the data, business, and presentation tiers, as well as generating reports for the end user. I don t know too many developers who enjoy creating reports for an application even though there are several different tools available from third-party vendors to make the process easier. Fortunately, companies using SQL Server 2000 can now tap into the powerful SQL Server Reporting Services to make creating reports a more fun and productive process.

 

When using Reporting Services, clients can access their desired reports via the Web in a variety of formats, such as HTML, PDF, TIFF, and Excel. Reports can be secured using built-in administrative tools, can include charts, and can generate dynamic data based on end-user parameters. End users can even subscribe to reports and automatically receive them in their inbox. Reports can also be embedded in new or existing ASP.NET applications.

 

In this article, I ll provide a step-by-step approach to getting Reporting Services going, as well as demonstrate how to create a report that hits the Northwind database from within an ASP.NET Web Form.

 

Installing Reporting Services

The Microsoft Reporting Services homepage is located at http://www.microsoft.com/sql/reporting/ and contains details on how to get a trial version of the product. If you have an MSDN Universal subscription you can also download Reporting Services as an .iso image, which can be burned to CD. Once downloaded, the installation process is relatively simple especially if you ll be installing all the components on a single development machine. Reporting Services provides three main components including a report server, a report database, and client tools that plug directly into Visual Studio.NET. The report server requires IIS5 or higher; the report database requires SQL Server 2000 with service pack 3 applied. To use the client tools you ll need Visual Studio.NET 2003 or a product that includes the VS.NET shell, such as Visual Basic.NET 2003.

 

As you start the Reporting Services installation, you ll be asked about which components you d like to install. To keep the process simple for this article, I ll assume that you ll be installing all components on a single development machine, including the report server, report database, and client tools. Reporting Services service pack 1 was available at the time this article was written, and it s recommended that you install this service pack, as well.

 

After installing the server and database components (as well as the service pack) you can test whether or not the installation was successful by navigating to http://localhost/ReportServer in your browser.

 

Creating a Simple Report

Once Reporting Services is installed you can create reports directly in VS.NET 2003. To begin the process of creating a report, open VS.NET 2003 and create a new project. In the New Project dialog box select Business Intelligence Projects from Project Types and Report Project from Templates (see Figure 1). Name the report NorthwindReports and click the OK button.

 


Figure 1: After installing the Reporting Services client tools you ll see new Visual Studio.NET templates.

 

A new Reporting Services solution will be created in VS.NET that can be used to create data connections and reports. Before adding a report to the solution you first need to add a data connection. Do this by right-clicking Shared Data Sources and selecting Add New Data Source from the menu. In the next screen enter your database name and the credentials used to get to the server, and select the Northwind database from the list of databases. Test the connection by clicking the Test Connection button. Click the OK button when the connection is successful. After the data connection is created you ll see a new entry in the Solution Explorer named Northwind.rds. Because this data source is shared, it can be used by multiple reports, if needed.

 

Right-click on the Reports folder in Solution Explorer and select Add New Report from the menu. Click Next to go to the Select the Data Source screen. If the Northwind data source you created earlier isn t selected then select it in the Shared Data Source drop-down list. Click Next to go to the Design the Query screen. Type the following into the Query String textbox:

 

SELECT ContactName, CompanyName, Address, City,

 Region, Phone

FROM Customers

 

Although this query is quite simple, more advanced queries can be designed visually by clicking the Edit button. Alternatively, you can also list the name of a stored procedure used by the report.

 

After you ve entered the SELECT query, click the Next button and, on the screen that follows, click the Finish button. Finally, give the report a name of Customers and click the Finish button again. This creates a new entry in Solution Explorer named Customers.rdl (.rdl files contain report meta-data marked-up as XML). Figure 2 shows what the report s design surface should look like at this point. A live report can be viewed in Visual Studio.NET by clicking on the Preview tab at the top of the report designer.

 


Figure 2: Reporting Services provides a designer that is built directly into Visual Studio.NET.

 

Once a report is created it must be deployed to the report server for display to end users. This can be handled within Visual Studio.NET by right-clicking on the report (Customers.rdl in this case) and selecting Deploy from the menu. Doing this for the first time will likely result in an error because the target report server to deploy to hasn t been defined. To fix this issue, right-click on the NorthwindReports project in Solution Explorer and select Properties. In the Properties window enter a value of http://localhost/ReportServer for the TargetServerURL property (see Figure 3).When this value has been assigned, re-deploy the report using the steps mentioned earlier.

 


Figure 3: Before deploying reports to the report server you must assign a value to the TargetServerURL property.

 

To test the report, navigate to http://localhost/ReportServer in your browser and select the NorthwindReports link. Next, click on the Customers link to view the report. Notice that the report displays as HTML by default, but that the output type can be changed using the drop-down list shown in Figure 4.

 


Figure 4: Reports can be viewed in several different formats, ranging from HTML to PDF to TIFF.

 

Securing Reports

After a report is published you may want to restrict who has access to view it. This can be accomplished by using the Reporting Services Report Manager tool. Access the tool by navigating to http://localhost/Reports/ in the browser. To set security for reports contained within the NorthwindReports project, click the NorthwindReports link, select the Properties link located at the top of the page, and then click the Security link on the left side of the page. New roles can be added for users or groups that should have access to the reports by selecting New Role Assignment.

 

To give a specific NT group or user access to browse reports, type the group or user name into the textbox and then check the checkbox next to Browser (see Figure 5). This allows the user or group to view reports, but does not give them access to deploy reports or modify them in any way. If the user or group needs access to publish reports in addition to viewing them, you can check the Publisher checkbox, as well.

 


Figure 5: The Reporting Services Report Manager tool allows security to be set, as well as many other features relating to reports.

 

Incorporating Reports into ASP.NET Web Forms

Reporting Services ships with several examples that can be used to help get you started creating different types of reports. The application s sample folder includes an ASP.NET server control that can be used to easily embed reports into Web Forms. A compiled version of the control doesn t ship with the samples, but you can open a VB.NET or C# example by going to \Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer. Within the cs (or vb) folder you ll find a solution file containing the sample ASP.NET server control. This control embeds the desired report into an iframe for display within the Web Form.

 

After compiling the control (a compiled version is included with this article s accompanying sample code; see end of article for download details), you can create an ASP.NET project in Visual Studio.NET and add the control to the toolbox by right-clicking and selecting Add/Remove Items from the menu. Click the Browse button and navigate to the compiled ReportViewer.dll file you compiled and select it. Now drag the control onto the ASP.NET Web Form design surface.

 

Once the ReportViewer control is on the design surface, right-click the control and select Properties. Set the ServerURL property to http://localhost/ReportServer and the ReportPath property to /NorthwindReports/Customers. After setting these properties, the control s size can be changed by grabbing its corners in the designer. After sizing the control, save the page and view it in the browser. You should see something similar to the image shown in Figure 6.

 


Figure 6: The Reporting Services sample ASP.NET server control makes it easy to add existing reports into ASP.NET Web Forms for display to end users.

 

Conclusion

Although this article has only scratched the surface of the many features found in Reporting Services, it should get you off to a running start creating reports. Other Reporting Services features include dynamic chart generation (line charts, pie charts, donut charts, scatter charts, etc.), subreport integration, parameterized reports, linked reports, and more. The Reporting Services help file has several different tutorials to walk you through additional report-creation details. By using Reporting Services, you can whip up end-user reports in no time, and possibly even enjoying yourself along the way.

 

The sample code in this article is available for download.

 

Dan Wahlin (Microsoft MVP for ASP.NET and XML Web services) is the president of Wahlin Consulting LLC and founded the XML for ASP.NET Developers Web site (http://www.XMLforASP.NET), which focuses on using ADO.NET, XML, and Web services in Microsoft s .NET platform. In addition to providing .NET consulting services, Dan is also a speaker at several different technology conferences and is on the INETA Speaker s Bureau (http://www.ineta.org). Dan co-authored ASP.NET 1.1 Insider Solutions (SAMS, 2004), Professional Windows DNA (Wrox, 2000), ASP.NET: Tips, Tutorials and Code (SAMS, 2001), and authored XML for ASP.NET Developers (SAMS, 2001).

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish