Use SQL Server reporting services to generate user-friendly reports from applications

Use SQL Server reporting services Program User-friendly report generation released on: 09/03/2004 | Updated on: 09/03/2004

John C. Hancock

This article discusses:
• Reporting • Design and deploy reports • Use the Web Service of Reporting Services • Ensure report security	This article uses the following technologies: SQL Server, ASP. NET, Visual Basic. net CodeDownload: Sqlserverreportingservices.exe (222kb)

Content on this page

	Reporting Services Overview
	Design the first report
	Deployment and Testing
	Add a report to a web application
	Embed reports into Web Applications
	Ensure report security
	Use the Reporting Services Web Service
	Subscription report
	What should we do next?

Flexible report functions are a requirement of most business applications. These report functions are more widely used after being integrated into web applications. With the latest version of SQL Server 2000 reporting services, you can easily generate reports from various data sources. This article describes how to use Visual Studio and reporting services to write reports and how to integrate reports into web applications.

Reporting Services is a server-based report generation platform built on.. NET Framework is integrated with SQL Server 2000. Therefore, you can use an extended web service-based API to integrate rich report generation functions into applications. Although the report server uses SQL Server as the report repository, OLE DB, ODBC, or ADO is used. any data source of the net provider can be used to provide data for reports, which makes reporting services an excellent tool for generating reports in various enterprise environments.

Reporting Services is authorized as part of SQL Server 2000. Therefore, if you have an authorized copy of SQL Server, you can run reporting services on the same server without the additional license fee. If you decide to run reporting services on an independent computer that has not been authorized by SQL Server (for example, a Web server), you will need to purchase an additional SQL server license.

To start using reporting services, you can download the 2000-day trial version of SQL Server 120 reporting services, which also includes instructions on how to obtain the installation media for this product. When installing this product, make sure that you select the option that includes the sample report, because I want to integrate these reports into the web application in this article.

Reporting Services Overview

Reporting Services report design uses the New Visual Studio. NET 2003 report designer. After reporting services is installed on the same computer where Visual Studio is installed, the designer can be used. The designer outputs the report layout and data access Description Based on the XML report Definition Language (RDL), and the RDL file is published to the Report Server. If you want to provide users with the creation function, you can also use a third-party report designer.

The administrator can use the web-based report manager to manage published reports and execute some tasks. For example, to ensure the security of reports of some user groups or to change the data source connection string of the deployed reports. You can also use the report manager to browse and view reports. However, many companies prefer to use URL requests or reporting services web services, directly integrate the view of reports into their existing internal web sites or applications.

Figure1Reporting Services Architecture

You can parameterize reports so that you can select from the selection list when viewing reports. They can also be exported to multiple formats, such as Microsoft Excel, PDF, and XML. Although real-time reports provide the latest data, you can cache the reports for a period of time to improve performance and reduce the load on the data source. For business intelligence applications, reports can access Analysis Services OLAP multi-dimensional datasets, and reporting services can even import existing Microsoft Access reports. Despite the differences between the two technologies, it does not support all access functions. For more information about the reporting services architecture, seeFigure1.

Back to Top

Design the first report

Although my focus is on integrating reports into applications rather than creating reports, the best way to be familiar with reporting services is to create a new report project immediately. To do this, open Visual Studio and use Report Project Wizard to create a new project, which is located in the Business Intelligence Projects Section. Specify a name for your project and click "OK". The Report Wizard dialog box is displayed. In this step, click "edit" to specify the adventureworks2000 that is connected to your database server, and click "OK ", select the option to make it a shared data source.

Click "Next" to go to the "design the query" step, specify a simple SQL statement (for example, "select * from product"), and click "Next ". Reporting Services supports displaying a set of standard tabular reports with fixed columns, and also supports matrix reports with Dynamic cross-table style views, therefore, select tabular for the report and click "Next ".

Drag some columns (such as productid and name) to the details section and click "Next ". Select the default bold style, click "Next", and specify the URL of the virtual directory of your report server, for example, http ://Myserver/Reportserver. Click "Next ". In the last step, you can specify a name for your first report, for example, products report. Then click Finish ".

This wizard will create this project, and also create a report that can be shared by multiple reports to the adventureworks2000 data source, and a report about products data opened in the designer (seeFigure2). By default, the layout tab of the report design is displayed.

Figure2Visual Studio report designer

The data tab is used to specify a REPORT query, including setting parameters to prompt users to provide values when executing the report. Click the "preview" tab to test how the report is displayed to the user.

Now, you can add the attached report to this project, you can also modify the products report by adding additional columns, grouping data, adding a summary, or even adding an additional table that displays data from other queries. Unlike many report designers, Reporting Services uses an out-of-band design, which allows it to easily create reports that place datasets from various sources in the same location.

The report project you just created consists of a group of files that currently reside on your development computer. To publish these reports to users, deploy the project to the Report Server.

Back to Top

Deployment and Testing

Before deploying a report project, you must specify the server on which the project is deployed. If you have initially created a report project using this wizard, you may have specified the target server.

To check this setting or specify a new server, select "project | properties" to display the Project Properties dialog box. This setting allows you to specify the folder name of the project and the URL of the Reporting Services web service. Select "build | deploy solution" from the menu to build a report project and deploy it on the server.

After you deploy a report project, you can test the project. Each report deployed on the server has a unique path, which can be used to display reports in a browser. For example, to display the first report you created, open a browser and navigate to http ://Myserver/Reportserver? /Report + project1/products + report. This displays a report in HTML format and a toolbar at the top of the page, so that you can flip the report, change the zoom ratio, or even in a variety of other formats (such as PDF) view reports.

You can also navigate to the following URL on the server to view the list of all report items deployed on the server: http ://Myserver/Reportserver. The Report Server allows you to view the deployed report types and the minimal user interface for viewing reports.

The report manager is a more user-friendly and functional tool. You can access this tool by simply navigating to the following URL on the local server: http ://Myserver/Reports.

Back to Top

Add a report to a web application

Now that you know how to design, deploy, and test reports, let's integrate these reports into the application! Many Web applications include custom Homepages for each user, usually called dashboards ". This page usually provides customized summary information for users and serves as a jump point to access other features of the application.

I will build a dashboard using the sample report that comes with reporting services to demonstrate report integration,3. If you have installed the sample report during installation, you can open the Report Project (install it to \ Reporting Services \ samples \ reports) and deploy it to the test server. You can use the report manager tool to test the sample report by browsing to the samplereports folder.

Figure3Dashboard Web Applications

The easiest way to grant users the permission to access reports from applications is to use hyperlink. As you can see, each report has a unique path for displaying the report in a browser. For a dashboard application, the report should be opened in a separate window, so I will specify a target for this hyperlink. So far, this is very simple, but what is the situation for reports with parameter reports (such as the employee sales summary report? To truly integrate a report into an application, you usually need to specify some values for the parameter so that the user will not be prompted frequently.

Reporting Services makes this task very simple: the report parameter value can be specified as part of the URL. For example, the employee sales summary report has the reportyear, reportmonth, and empid parameters, so the hyperlink that shows the sales volume of employees numbered 24 in March can be:

 
<A href = "http: // myserver/reportserver? /Samplereports/employee sales summary & reportmonth = 11 & empid = 24 "target =" _ blank "> November sales summary </a>
 

Parameters that do not have the value specified in the URL will use the default value set in the report designer. Therefore, the reportyear value used in the example sales summary report just displayed is 2003. Reporting Services also has a set of built-in parameters used to control the report display mode. These parameters differentiate your report parameters by prefix "RS.

The most useful parameter is the RS: Format parameter, which is used to specify the report format. This allows you to include reports that do not need to be displayed in HTML, or use formats such as PDF or XML. Other common parameters are RS: Command, which indicates the operation to apply to the path you specified. For example, RS: command = render will present a report, and RS: command = listchildren will list all the items in the folder.

If you do not specify a command, reporting services will view the path you specified and calculate the appropriate action to take, for example, to present a report. Figure 4 shows the RS: parameters list.
Another set of built-in parameters controls the behavior of various output formats and uses RC: prefix. Each report format has its own specific set of parameters. For example, to present a report in the format of comma-separated values (CSV) without a title line, you should use the RC: noheader parameter:

 
<A href = "http: // myserver/reportserver? /Samplereports/company sales & RS: command = render & RS: format = CSV & rc: noheader = true "> company sales </a>
 

The HTML format has a lot of RC: parameters, which provides a lot of flexibility when integrating reports into web applications. If the application passes the report parameter selection to the report, you may need to set RC: parameters to false to avoid prompting users, or even by setting RC: set toolbar to false to disable the entire toolbar. For details about the list of commonly used html rc: parameters, see Figure 5.

If your web application uses a framework to display a report (for example, displaying a report list in the left pane and the report content in the right pane), you need to use RC: the linktarget parameter to specify the name of the content framework. Otherwise, when you click any embedded link in the report, the browser will use the entire window to re-display the report, rather than retaining the framework of the Navigation Pane and content pane.

You can add the "Quick Start" area by using the URL parameters I have introduced. This area contains a list of hyperlinks that allow users to open frequently-used reports (see figure 3 ). Each hyperlink sets its target as _ blank to open a report in an independent browser window, and uses parameters such as RS: format = EXCEL to control the display mode of the report in the browser.

Back to Top

Embed reports into Web Applications

If you do not use a new browser window when viewing a report, you may want to embed the report into your web page. The simplest way is to use IFRAME on the web page and set the src attribute using the same URL-based technology I have introduced. If you use this technology, remember to set the RC: linktarget parameter to the IFRAME name to avoid any framework pop-up outside the framework when you click the Report link.

If you have previously designed ASP. NET Server Control, you may think that IFRAME technology is not very perfect, because it requires Web developers to understand the URL parameter Syntax of reporting services to build an appropriate SRC string. Isn't it a better idea to build a server control that encapsulates all parameters and allows developers to easily set parameters such as reportpath and zoom?

Reporting Services is very convenient with the example named reportviewer (installed in the \ Reporting Services \ samples \ applications \ reportviewer directory), which can implement this idea. This control encapsulates the IFRAME and URL access parameter logic and provides a simpler method for embedding a report into an application. To use this control, open and build the reportviewer solution, and then browse to reportviewer in the bin directory of the reportviewer solution. DLL, switch to your web application, and add the control to the toolbox.
You can use this control to add embedded reports to a dashboard application. Add the control to the web page, set the height and width attributes, and add the following code to display a Report on the page:

 
Private sub page_load (byval sender as system. object, byval _ e as system. eventargs) handles mybase. load if not ispostback then reportviewer1.serverurl = "http: // myserver/reportserver" reportviewer1.reportpath = "/samplereports/sales order" & _ "detail & salesordernumber = so8153" reportviewer1.toolbar = reportviewer. multistate. false reportviewer1.zoom = "75" End ifend sub
 

Back to Top

Ensure report security

Reporting Services Security uses a familiar role-based model. Users and groups can be assigned to roles such as system administrator or browser. All members of a specific role can perform operations defined for this role.

The report manager is used to execute security-related tasks, such as assigning user roles or modifying permissions for items (such as reports. By default, the administrators Windows Group is a member of system administrator and Content Manager in all folders. To extend report access to other users, user accounts or groups must be added to roles such as browser. This is usually set at the folder level, and can also be rewritten for individual reports.

In addition to protecting access to reports, the Administrator must also decide how to connect the reports to the source database. You can configure the data source to use integrated security, so that you can use the current user account to connect to the database. In addition, windows? Or SQL Server user name and password, and securely store them in the Report Server database. This method requires that the report run without user interaction (for example, interaction involved in subscription.

Back to Top

Use the Reporting Services Web Service

So far, the URL access method I have been using provides a fast and convenient way to view reports and specify parameters, but it cannot provide access to all available report management functions. Reporting Services also comes with an API with rich functions to provide you with access to advanced functions through Web Services. For example, the Web service includes a method for managing the data source information of a report or a method for enumerating all items in a folder.

I will use web services to add the dynamic list of reports to the dashboard application. Therefore, you need to use add web reference to add a web service reference to the application. The path to the web service is as follows:

 
Http: // myserver/reportserver/reportservice. asmx
 

After adding a reference, you can create an instance of the reportingservice proxy class and call a method on the service, just as I have 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 nextend sub
 

If you are deploying an application in an Intranet environment, you must enable simulation by adding it to the Web. config file of the application. You also need to pass the Client Login creden。 to the Service, as shown in the previous example.

The listchildren method returns an array of catalogitem objects. This array indicates the items that you have the right to view, including reports, folders, and data sources. The path attribute provides a unique path to an item, for example,/samplereports/sales order detail. The type attribute allows you to distinguish between a report and other types of items.

Since I have loaded the report name to the drop-down list, I have to decide how to display the report in the dashboard application. I decided to expand the reportviewer code I wrote earlier, and changed the reportpath attribute as long as the user selected a report and clicked the "go" button. You can also use IFRAME and set SRC to an appropriate URL (with the prefix "http: //" added to the report name ://Myserver/Reportserver ?").

In addition to the report management function, other major functions provided by Web services are report presentation. Most applications (including report manager applications) use the web service function to list reports or change settings, and use the URL method I have introduced to display reports. However, if you want to fully control how the report content is processed in the application, you can use the render method of the reportingservice class. This method returns a byte array that can be saved to the output file later. The output file can be a graphical file or an Excel spreadsheet, depending on the request format. If you want to present a report in HTML format, it may be more complicated because you will need to use the renderstream method to present images or other resources separately.

Although this article focuses on Web applications, you can also use the same technology to add the report generation function to Windows-based applications. The simplest way is to use URL access, or start your browser from the application, or include Microsoft webbrowser ActiveX? Control and use the navigate method to load the appropriate URL. When you want to better control some operations (such as displaying results in an image box or directly saving files to a disk), the web service render method is a good alternative.

Information workers usually have to process two types of reports: the types they want to obtain when they need to collect information and the types that appear on their desktops or in their emails every week. So far, I have already elaborated on the process for users to obtain their own reports, but reporting services also includes a very powerful function called subscription, it enables you to "push" reports to customers.

Back to Top

Subscription report

The reporting services subscription function allows users to receive reports as planned. Reports are usually sent to users by email, but reporting services also allows you to generate a report to a file share or even write your own commit extension. This allows you to easily complete distribution tasks, such as sending weekly sales statistical reports to each sales representative by email, or setting monthly financial reports to run on the last day of each month, and write it as a PDF file to the company file sharing.

Before setting a subscription, you need to consider how the report connects to the database to retrieve its data. Therefore, subscribed reports are not directly executed by users. You cannot use integrated security for data sources. However, you must specify the user name and password to use when running reports. If you try to set subscription for reports with integrated security, reporting services returns an error.

To change the data source of the sample report, open "report manager | sample reports" and click adventureworks data source. This data source is shared by all sample reports, so any changes made to it will affect all reports. Select the "credentials stored securely in the Report Server" option to specify the valid domain user name and password for an account with access to the adventureworks database, select "use as Windows credentials when connecting to the data source option" and click "Apply. Note that this change means that the specified account will always be used to connect to the database, no matter which user actually runs the report.

Therefore, you are ready to study and view the Web Service APIs that provide subscription access. Using Web services is more challenging than using the list or render method, mainly because of the number of available options. The method I will use is createsub.pdf, which also belongs to the reportingservice class. For more information about the parameters of this method, see the reportingservice class. However, you must specify the report to be subscribed to and the generation plan (for example, monday morning or the last Friday of each month), email options (including email addresses), and all report parameter values.

I have included the utility class named emailsubscriber in the sample code in this article, which summarizes some complex content. The dashboard application uses this class to allow users to specify their email addresses and send the employee sales summary report to them by email every Monday. In the product application, you can adjust the reports of each user. For example, when you create a subscription, you can retrieve the employee ID based on the employee's Windows user account, set Report parameters as appropriate.

If you do not want to test your subscription until next Monday, it helps you learn more about how Reporting Services handles the subscription plan. When you create a subscription, reporting services creates an SQL Server proxy job that is executed as required. You can open Enterprise Manager and expand the management, SQL Server Agent, and jobs folders to view the job. This subscribe job will have a "Report Server" category and will be named after the guid that reporting services uses to track the job. Right-click the job and select start job. If you have correctly set all aspects of your report, your report will be sent to you by email.

Back to Top

What should we do next?

Two other reporting services functions that you can use to add important report generation functions to an application include data driven subscriptions and snapshots, the former allows you to set a user's mail list and send specific parameterized reports to them by email. The latter is the report view at the planning point, you can also provide a historical view of the report. Reporting Services is built using a modular architecture. If you need more powerful features, it allows you to use your preferred. net is the target language to add powerful new extensions.

John C. HancockIs a senior consultant of Microsoft, dedicated to business intelligence and. NET development. He now lives in Toronto and wants to contact him, visit http://www.johnchancock.net.

From msdn magazine, published in August 2004.
You can purchase at a local newsstand orSubscription.

Figure 4 Report Server RS: Parameters

Parameter	Description	Values
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, getperformancecontents
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 interval or 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

Figure 5 Commonly used RC: parameters for HTML Format

Parameter	Description	Values
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.