Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
John C. Hancock
This article discusses:
|This article uses the following technologies:
SQL Server, ASP.NET, Visual Basic .NET
Code download available at:SQLServerReportingServices.exe(222 KB)
An Overview of Reporting Services
Designing Your First Report
Deploying and Testing
Adding Reports to a Web App
Embedding Reports into Your Web App
Securing Your Reports
Using the Reporting Services Web Service
Subscribing to a Report
Flexible reporting capabilities are a requirement for most business applications and their integration into Web apps makes them more versatile than ever. With the recent release of SQL Server™ 2000 Reporting Services, you can easily add reporting from diverse data sources. In this article I will introduce report authoring using Visual Studio® and Reporting Services and demonstrate how to integrate reports into your Web applications.
Reporting Services is a server-based reporting platform that is built on the .NET Framework and integrated with SQL Server 2000, so you can integrate rich reporting features into your apps using an extensive Web services-based API. Although the report server uses SQL Server as the repository for reports, any data source with an OLE DB, ODBC, or ADO.NET provider can be used to supply data to the reports, making Reporting Services a great choice for reporting in diverse enterprise environments.
Reporting Services is licensed as part of SQL Server 2000, so if you already have a licensed copy of SQL Server, you can run Reporting Services on the same server for no additional license fee. If you decide to run Reporting Services on a separate machine that is not yet licensed for SQL Server, such as a Web server, you will need to purchase an additional SQL Server license.
To get started with Reporting Services, you can download the 120-day trial software at SQL Server 2000 Reporting Services, which also includes directions on how to obtain the installation media for the product. Make sure to check the option to include the sample reports when you install the product since I will be integrating these reports into a Web application in this article.
An Overview of Reporting Services
Reporting Services reports are designed using a new Visual Studio .NET 2003 report designer, which is available when you install Reporting Services on the same machine as Visual Studio. The designer outputs a description of the report layout and data access in the new XML-based Report Definition Language (RDL), and the RDL files are published to a report server. Report designers from third parties are also available if you want to provide authoring capabilities to your users.
Administrators can manage the published reports using the Web-based Report Manager, and perform tasks such as securing reports for certain groups of users or altering the data source connection strings for deployed reports. Users can also use Report Manager to browse and view reports; however, many companies will prefer to integrate report viewing directly into their existing internal Web sites or applications, using either URL requests or the Reporting Services Web service.
Figure 1** Reporting Services Architecture **
Reports can be parameterized so that users can pick from a list of selections when viewing the report; they can also be exported to a wide range of formats including Microsoft® Excel, PDF, and XML. While live reports provide up-to-the-minute data, reports can also be cached for a period of time to improve performance and to reduce the load on data sources. For business intelligence applications, reports can access Analysis Services OLAP cubes, and Reporting Services can even import existing Microsoft Access reports, although not all Access features are supported due to differences between the two technologies. See Figure 1 for a high-level look at the Reporting Services architecture.
Designing Your First Report
Although I will focus on integrating reports into applications rather than report authoring, the best way to familiarize yourself with Reporting Services is to dive right in and create a new report project. To do this, open Visual Studio and create a new project using the Report Project Wizard, located in the new Business Intelligence Projects section. Specify a name for your project, click OK, and the report wizard dialog will be displayed. During the step when you select the data source, click the Edit button to specify a connection to AdventureWorks2000 on your database server, click OK, and then check the option to make it a shared data source.
Click Next to move to the Design the Query step, specify a simple SQL statement such as "SELECT * FROM Product", and click Next. Reporting Services supports standard Tabular reports which show a fixed set of columns, as well as Matrix reports which use a crosstab-style view with dynamic columns, so choose Tabular for this report and click Next.
Drag some columns, such as ProductID and Name, to the Details section and click Next. Choose the default Bold style, click Next, then specify the URL of the virtual directory of your report server—for example, https://MyServer/ReportServer. Now, click Next. On the final step, you can specify a name for your first report such as Products Report; then click Finish.
The wizard will create the project, along with an AdventureWorks2000 data source that can be shared by multiple reports, and a report on the Products data which is opened in the designer (see Figure 2). The Layout tab, which allows you to modify the report design, is displayed by default.
Figure 2** Visual Studio Report Designer **
The Data tab is used to specify the queries for your report, including setting up parameters so that the user can be prompted to supply values when the report is executed. Click the Preview tab to test how your report will appear to a user.
You can now add additional reports to the project or modify the Products report by adding additional columns, grouping the data and adding subtotals, or even adding additional tables which display data from other queries. Unlike many report designers, Reporting Services uses an out-of-band design which makes it easy to create reports that include data from a variety of different sources all brought together in one place.
The report project that you have just created consists of a set of files which currently reside on your development machine. In order to publish these reports to your users, you will need to deploy the project to the report server.
Deploying and Testing
Before deploying the report project, you will need to specify the server to which you are deploying your project. If you initially created your report project using the wizard, then you would have already specified a target server.
To check this setting or specify a new server, select Project | Properties to display the Project Properties dialog. Setting this allows you to specify both a folder name for your project and the URL of the Reporting Services Web service. The Build | Deploy Solution menu selection will build your report project and then deploy it to the server.
Now that the report project has been deployed, you can take it for a test drive. Every report that has been deployed to a server has a unique path that can be used to display the report in a browser. For example, to display the first report you created, open a browser and navigate to https://MyServer/ReportServer?/Report+Project1/Products+Report. This will render the report in HTML and display a toolbar at the top of the page to allow you to page through the report, change the zoom, or even view the report in a variety of other formats, such as a PDF.
You can also browse a list of all the report projects that have been deployed to the server by navigating to the following URL on your server: https://MyServer/ReportServer. The report server provides a minimal user interface that allows you to see what reports have been deployed and view the reports.
The Report Manager tool, which is friendlier and more feature-rich, can be accessed by simply navigating to the following URL on your local server: https://MyServer/Reports.
Adding Reports to a Web App
Now that you know how to design, deploy, and test reports, let's integrate these reports into an application. Many Web applications include a customized home page for every user, often referred to as a dashboard. This page usually presents summarized information that is tailored to the user and serves as a jumping off point to access the other functionality in the application.
I will be building a dashboard to demonstrate report integration, shown in Figure 3, using the sample reports that ship with Reporting Services. If you installed the sample reports during setup, you can open the report project (installed to \Reporting Services\Samples\Reports) and deploy it to your test server. You can test the sample reports using the Report Manager tool by browsing to the SampleReports folder.
Figure 3** Dashboard Web App **
The simplest way to provide users with access to reports from your application is to use a hyperlink. As you have seen, every report has a unique path that can be used to display it in a browser. For a dashboard application, the report should be opened in a separate window, so I would specify a target for the hyperlink. That's very simple so far, but what about reports that have parameters, such as the Employee Sales Summary report? To really integrate a report into an application, you often need to specify some values for the parameters so that the user is not always prompted.
Reporting Services makes this task very easy: values for report parameters can be specified as part of the URL. For example, the Employee Sales Summary report has ReportYear, ReportMonth, and EmpID parameters, so the hyperlink to display November sales for employee 24 would be:
<a href="https://MyServer/ReportServer?/SampleReports/Employee Sales Summary&ReportMonth=11&EmpID=24" target="_blank">November Sales Summary </a>
Parameters that don't have a value specified in the URL will use the default value that is set in the report designer, so the sample sales summary report just shown uses a ReportYear value of 2003. Reporting Services also has a set of built-in parameters that can be used to control how the report is displayed. These are distinguished from your own report parameters by prefixing them with "rs:".
One of the most useful is the rs:Format parameter, which is used to specify the format in which to render the report. This allows you to include reports that don't necessarily get displayed in HTML, but use formats such as PDF or even XML. The other commonly used parameter is rs:Command, which indicates the action to apply to the path that you have specified. For example, rs:Command=Render will render a report, while rs:Command=ListChildren will list all of the items in a folder.
If you don't specify a command, Reporting Services will look at the path you have specified and work out the appropriate action to take, such as rendering a report. Figure 4 shows a list of rs: parameters.
Figure 4 Report Server rs: Parameters
|rs:Command||The action to apply to the path, such as render a report or list all of the items in a folder. Defaults to Render for reports.||Render, ListChildren, GetResourceContents, GetDataSourceContents|
|rs:Format||The format in which to render the report, such as HTML4.0 or PDF. Defaults to the appropriate HTML supported by the browser.||HTML3.2, HTML4.0, HTMLOWC, MHTML, IMAGE, EXCEL, CSV, PDF, XML (can also include custom rendering extensions if available)|
|rs:Snapshot||Renders a report snapshot, which uses report data captured at a specific point in time and stored in the reporting server database.||Timestamp of snapshot—for example, 2004-03-30T15:45:02|
|rs:SessionID||Identifies an established active session between the browser and the report server. This is used when the report server has been configured to not use session cookies.||Existing session id|
|rs:ClearSession||Forces the client browser to clear its cached copy of the report and retrieve a new copy.||True|
Another set of built-in parameters controls the behavior of the various output formats and uses the rc: prefix. Each report format has its own specific set of parameters. For example, to render a report in comma-separated values (CSV) format with no header row, you should use the rc:NoHeader parameter:
<a href="https://MyServer/ReportServer?/SampleReports/Company Sales &rs:Command=Render&rs:Format=CSV&rc:NoHeader=true">Company Sales </a>
The HTML format has the widest range of rc: parameters, which gives you a lot of flexibility when integrating reports into a Web application. If your application passes report parameter selections to the report, you may want to avoid prompting the user by setting rc:Parameters to false, or you can even turn off the entire toolbar by setting rc:Toolbar to false. See Figure 5 for a list of commonly used HTML rc: parameters.
Figure 5 Commonly Used rc: Parameters for HTML Format
|rc:Toolbar||Shows or hides the toolbar, including the parameters area. Defaults to true.||true, false|
|rc:Parameters||Shows or hides the parameters area of the toolbar. Defaults to true.||true, false|
|rc:Zoom||Zoom factor to zoom in or out on a report. Defaults to 100. (Note that this setting only works with Internet Explorer 5.0 and above)||Page width, whole page, or an integer percentage such as 75.|
|rc:Section||The page number to render. The default value is 1.||Integer|
|rc:LinkTarget||The target name for hyperlinks in the report.||The name of a window or another valid target name such as _blank, _self, _parent, or _top.|
If your Web application uses frames to display reports, let's say with a list of reports in a left pane and the report content in the right pane, you will need to use the rc:LinkTarget parameter to specify the name of your content frame. Otherwise, when the user clicks any embedded links in the report, the browser will redisplay the report using the whole window rather than preserving your frames for the navigation pane and the content pane.
You can use the URL parameters I've described to add the Quick Launch area, which contains a list of hyperlinks to allow the user to open common reports (see Figure 3). Each hyperlink has its target set to _blank to open the report in a separate browser window and uses parameters such as rs:Format=EXCEL to control how the report is displayed in the browser.
Embedding Reports into Your Web App
Rather than popping up a new browser window whenever a user views a report, you may want to actually embed the reports into your own Web pages. The simplest way to do this is to use an IFRAME on your Web page and set the SRC attribute using the same URL-based technique I described. If you use this technique, remember to set the rc:LinkTarget parameter to the name of your IFRAME to avoid popping out of the frame when a user clicks a link in the report, as with any frame.
If you have previously designed server controls for ASP.NET, you may be thinking that the IFRAME technique is not particularly elegant since it requires Web developers to understand the URL parameter syntax for Reporting Services in order to build the appropriate SRC string. Wouldn't it be a better idea to build a server control that encapsulated all the parameters and allowed developers to simply set properties such as ReportPath and Zoom?
Reporting Services conveniently ships with a sample called ReportViewer (installed to the \Reporting Services\Samples\Applications\ReportViewer directory) which does exactly that. The control wraps the IFRAME and URL access parameter logic and provides a simpler method of embedding reports into your application. To use the control, open and build the ReportViewer solution, then switch over to your Web application and add the control to the toolbox by browsing to ReportViewer.dll in the bin directory of the ReportViewer solution.
You can use the control to add embedded reports to the dashboard application. Add the control to the Web page, set the height and width properties, and add the following code to display a report in your page:
Private Sub Page_Load(ByVal sender As System.Object, ByVal _ e As System.EventArgs) Handles MyBase.Load If Not IsPostBack Then ReportViewer1.ServerUrl = "https://MyServer/ReportServer" ReportViewer1.ReportPath = "/SampleReports/Sales Order" & _ "Detail&SalesOrderNumber=so8153" ReportViewer1.Toolbar = ReportViewer.multiState.False ReportViewer1.Zoom = "75" End If End Sub
Securing Your Reports
Reporting Services security uses a familiar role-based model. Users and groups can be assigned to roles such as System Administrator or Browser, and all members of a particular role will be allowed to perform operations that are defined for that role.
Report Manager is used to perform security-related tasks such as assigning users to roles or modifying the permissions for an item such as a report. By default, the Administrators windows group is a member of both System Administrator and Content Manager for all folders. In order to extend report access to other users, the user account or group must be added to a role such as Browser. This is usually set up at the folder level, but also can be overridden for individual reports.
In addition to securing access to reports, administrators must also decide how to connect reports to source databases. Data sources can be configured to use integrated security so that database connections are made using the current user's account. Alternatively, a specific Windows® or SQL Server user name and password can be defined and stored securely in the report server database. This approach is required for reports that will be run without user interactivity, such as those involved in subscriptions.
Using the Reporting Services Web Service
The URL access approach that I have been using so far provides a quick and easy way to view reports and specify parameters, but it doesn't provide access to all of the report management functionality that is available. Reporting Services also comes with a full-featured API, which gives you access to the advanced functions through a Web service. For example, the Web service includes methods to manage data source information for your reports or to enumerate all of the items in a folder.
I will be using the Web service to add a dynamic list of reports to the dashboard application. To get started, you will need to add a Web service reference to your application using Add Web Reference. Specify the path to the Web service, like so:
Once you have added the reference, you can create an instance of the ReportingService proxy class and invoke a method on the service, like I've done here:
Private Sub LoadSampleReports() Dim rs As New ReportingService rs.Credentials = System.Net.CredentialCache.DefaultCredentials Dim item As CatalogItem For Each item In rs.ListChildren("/SampleReports", False) If item.Type = ItemTypeEnum.Report Then DropDownList1.Items.Add(New ListItem(item.Name, item.Path)) End If Next End Sub
If you are deploying your application in an intranet environment, you will want to enable impersonation by adding <identity impersonate="true"/> to your application's web.config file. You will also want to pass the client's login credentials to the service, as shown in the previous example.
The ListChildren method returns an array of CatalogItem objects representing the items that the user has permission to view, including reports, folders, and data sources. The Path property gives the unique path to the item, such as /SampleReports/Sales Order Detail, and the Type property allows you to distinguish between reports and other types of items.
Now that I've loaded the report names into a dropdown list, I have to decide how to display the reports within the dashboard application. I decided to extend the ReportViewer code that I wrote earlier and change the ReportPath property whenever the user selects a report and clicks the Go button. You could also use an IFRAME and set the SRC property to the appropriate URL (and prefix the report name with "https://MyServer/ReportServer?").
In addition to report management functions, the other main feature that the Web service provides is report rendering. Most applications (including the Report Manager application) use the Web service features to list reports or alter settings, and use the URL approach I've described to display reports. However, if you want full control over how the report content is handled in your application, you can use the Render method of the ReportingService class. This method returns a byte array which you can then save to an output file, such as an image file or an Excel spreadsheet, depending on the requested format. If you want to render a report in HTML, things are a bit more complicated because you will need to use the RenderStream method to separately render any images or other resources.
Although this article has focused on Web applications, you can use the same techniques to add reporting to Windows-based applications. The easiest approach is still to use URL access and either launch the user's browser from your application or include the Microsoft WebBrowser ActiveX® control and use the Navigate method to load the appropriate URL. The Render method of the Web service is a good alternative when you want more control, for operations such as displaying the results in a picture box or saving the file directly to disk.
Information workers generally have to deal with two types of reports: the type they seek out when they need to gather information and the type that shows up on their desk or in their e-mail every week. So far I've concentrated on the process by which users fetch their own reports, but Reporting Services also includes a powerful feature called subscriptions that allows you to "push" reports to users.
Subscribing to a Report
The Reporting Services subscription feature lets users receive reports on a scheduled basis. Reports are generally e-mailed to the user, but Reporting Services also lets you deliver reports to a file share or even write your own delivery extension. This allows you to easily accomplish dissemination tasks like e-mailing a weekly sales total report to every sales rep, or setting up the monthly financial report to run on the last day of every month and write it out as a PDF to a company file share.
Before setting up a subscription, you need to think about how the report will connect to the database to retrieve its data. Since the subscribed report will not be executed directly by a user, you cannot use integrated security for the data source, but must specify a user name and password to be used when the report is run. If you try to set up a subscription for a report that uses integrated security, Reporting Services will return an error.
To change the data source for the sample reports, open Report Manager | Sample Reports, and click the AdventureWorks data source. This is shared by all the sample reports, so any changes made to this data source will affect all of the reports. Select the "Credentials stored securely in the report server" option, specify a valid domain user name and password of an account that has access to the AdventureWorks database, check the "Use as Windows credentials when connecting to the data source option," and click the Apply button. Note that this change means that the specified account will always be used to connect to the database, regardless of the user who actually runs the report.
So now you're ready to dive in and take a look at the Web service API that provides access to subscriptions. This part of the Web service is a little more challenging to use than the List or Render methods, mainly due to the number of options that are available. The method that I will be using is CreateSubscription, which also belongs to the ReportingService class. The parameters for this method are described in detail online (see ReportingService Class), but essentially you will need to specify which report you are subscribing to, the delivery schedule (such as every Monday morning or the last Friday of the month), the e-mail options including e-mail address, and any report parameter values.
I have included a utility class called EmailSubscriber in the sample code for this article, which abstracts some of the complexity. The dashboard application uses this class to allow the user to specify their e-mail address and to have the Employee Sales Summary report e-mailed to them every Monday. In a production application, you could tailor the report to each user, for example, by retrieving their employee ID based on their Windows user account and then setting the report parameters appropriately when you create the subscription.
If you don't want to wait until next Monday to test your subscription, it helps to know a little more about how Reporting Services handles subscription scheduling. When you create a subscription, Reporting Services creates a SQL Server Agent job which is executed according to the required schedule. You can see this job by opening Enterprise Manager and expanding the Management, SQL Server Agent, and Jobs folders. The subscription job will have a category of "Report Server" and is named with a GUID which is used by Reporting Services to keep track of the job. Right-click the job and choose Start Job, and if you have set everything up correctly your report will be e-mailed to you.
Two other Reporting Services features that you can use to add great reporting capabilities to your application include Data Driven Subscriptions, which allows you to set up a mailing list of users and e-mail specific parameterized reports to them, and Snapshots, which are views of the report at scheduled points in time that can also provide historical views of reports. If you need even more power, Reporting Services is built with a modular architecture, which allows you to add powerful new extensions using your favorite .NET-targeted language.
John C. Hancock is a senior consultant for Microsoft, specializing in business intelligence and .NET development. He is currently based in Toronto, and can be contacted at www.johnchancock.net.