server|services| programs generate user-friendly reports from applications using SQL Server Reporting Services release date: 09/03/2004 | Renew Date: 09/03/2004
John C. Hancock
Http://www.microsoft.com/china/msdn/library/data/sqlserver/SQLServerReportServ.mspx
This article discusses: ·
Reporting
•
Designing and Deploying Reports
•
WEB Service Using Reporting Services
•
Keep the report safe
The following techniques are used in this article:
SQL Server, asp.net, Visual Basic. NET
Code Download:
SQLServerReportingServices.exe (222KB)
Page content Reporting Services Overview Design the first report deployment and test add a report to a Web application embed a report in a Web application Secure report use Reporting Services Web Service Subscription report What do you do next?
Flexible reporting capabilities are a requirement for most business applications, and these reporting features are more versatile after integration into WEB applications. With the latest version of SQL server®2000 Reporting Services, you can easily have report generation capabilities from a variety of data sources. In this article, I'll describe using Visual studio® and Reporting Services to write reports and show how to integrate reports into WEB applications.
Reporting Services is a server-based report generation platform built on the. NET Framework and integrated with SQL Server 2000, so you can use an extended web-based service API to integrate rich report generation capabilities into In the program. Although the report server uses SQL Server as the repository for reports, any data source that utilizes OLE DB, ODBC, or ado.net providers can be used to provide data for the report, making Reporting Services the very best at generating reports in various enterprise environments With.
Reporting Services is licensed as part of SQL Server 2000, so if you have a copy of an authorized SQL Server, you can run Reporting services on the same server without paying additional license fees. If you decide to run Reporting Services on a stand-alone computer that is not authorized by SQL Server (for example, a WEB server), you will need to purchase additional SQL Server licenses.
To start using Reporting services, you can download the 120-day trial software located at SQL Server Reporting Services, which also includes instructions on how to obtain the installation media for the product. When you install the product, make sure that the options that include the sample report are selected because I want to integrate these reports into the WEB application in this article.
Reporting Services Overview
The Reporting Services report design uses the new Visual Studio. NET 2003 Report Designer, which is available after installing Reporting Services on the same computer where Visual Studio is installed. The designer prints a description of the report layout and data access based on the new XML Report Definition language (RDL), and the RDL file is published to the report server. If you want to provide authoring functionality to your users, you can also use a Third-party Report Designer.
Administrators can use web-based Report Manager to manage published reports, and can perform tasks such as securing certain user group reports or changing the data source connection string for a deployed report. Users can also use Report Manager to browse and view reports, but many companies tend to integrate report viewing directly into their existing internal Web sites or applications using URL requests or Reporting Services Web service.
You can parameterize reports so that users can select from a list of choices when they view the report, and they can also be exported to multiple formats, such as Microsoft®excel, PDF, and XML. Although live reports provide the most current data, you can cache reports for a period of time to improve performance and reduce load on the data source. For business intelligence applications, reports can access the analysis Services OLAP cubes, and Reporting services can even import existing Microsoft Access reports, although there are differences between the two technologies, It does not support all Access features. For more information about the Reporting Services architecture.
Back to top design First Report
While my focus is on integrating reports into applications rather than authoring reports, the best way to familiarize yourself with Reporting Services is to start creating a new report project immediately. To do this, open Visual Studio and create a new project using the Wizard project, which is located in the Business Intelligence Projects section. Specify a name for your project, click OK, and then Display the Report Wizard dialog box. In the step where you select the data source, click the Edit button to specify the AdventureWorks2000 that is connected to your database server, click OK, and then select the option to make it a shared data source.
Click Next to go to the design Query step, specify a simple SQL statement (for example, select * from Product), and then click Next. Reporting Services supports a standard tabular (tabular) report that displays a set of fixed columns, as well as a matrix report with a crosstab style view with dynamic columns, so select tabular for the report, and then click Next.
Drag some columns (for example, ProductID and Name) to the Details section and click Next. Select the default Bold style, click Next, and then specify the URL of your report server's virtual directory, for example, Http://MyServer/ReportServer. Now, click Next. In the final step, you can specify a name for your first report, for example, the products reports, and then click Finish.
The wizard will create the project and also create a AdventureWorks2000 data source that can be shared by multiple reports, and a report that opens in the designer about the products data . By default, displays a Layout tab that enables you to modify the report design.
The Data tab specifies the query for the report, including setting parameters to prompt the user for a value when the report is executed. Click the Preview tab to test how the report is displayed to the user.
You can now add additional reports to the project, or modify the products report by adding additional columns, grouping data, adding totals, or even adding additional tables that can display data from other queries. Unlike many report designers, Reporting Services uses Out-of-band design, which makes it easy to create reports that centralize data from a variety of sources in the same location.
The report project you just created consists of a set of files that are currently residing on your development computer. In order to publish these reports to users, you need to deploy the project to a report server.
Back to the top of the page deployment and test
Before you deploy a report project, you need to specify which server to deploy the project to. If you originally created a report project using the wizard, you may have specified a 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. On the menu, select Build | Deploy Solution will build the report project and then deploy it to the server.
After you deploy the report project, you are ready to test. Each report that has been deployed to the server has a unique path that can be used to display the report in a browser. For example, to display the first report that you create, open the browser and navigate to Http://MyServer/ReportServer?/Report+Project1/Products+Report. This renders the report in HTML format and displays a toolbar at the top of the page so that you can flip the report, change the scaling, or even view the report in a variety of other formats, such as PDFs.
You can also browse through the list of all report items that have been deployed to the server by navigating to the following URL on the server: Http://MyServer/ReportServer. The report server provides the smallest user interface that allows you to view the types of reports that you have deployed and view reports.
Report Manager is a more friendly, richer tool that can be accessed by simply navigating to the following URL on the local server: Http://MyServer/Reports.
Return to the top of the page 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 a custom home page for each user, often called a dashboard. This page usually provides summary information tailored to the user and is used as a jump point to access other features of the application.
I will use the sample reports that are included with Reporting Services to build a dashboard to demonstrate report integration. If you installed the sample report during Setup, you can open the report project (installed to \reporting Services amples\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.
The easiest way to provide users with permission to access reports from an application is to use hyperlinks. As you can see, each report has a unique path for displaying the report in the browser. For a dashboard application, the report should open in a separate window, so I will specify a target for the hyperlink. This is pretty straightforward so far, but what about the report with the parameter report (for example, the Employee Sales Summary report)? To really integrate a report into your application, you typically need to specify some values for the parameters so that users are not prompted frequently.
Reporting Services makes this task very simple: the value of a report parameter can be specified as part of a URL. For example, the Employee Sales Summary report has reportyear, Reportmonth, and EmpID parameters, so the hyperlinks that show employees numbered 24 in November 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 values set in Report Designer, so the Reportyear value used for the sample sales summary report just shown is 2003. Reporting Services also has a set of built-in parameters to control how the report is displayed. These parameters distinguish your own report parameters by prefixing the prefix "RS:".
One of the most useful parameters is the Rs:format parameter, which specifies the format in which the report is rendered. This allows you to include reports that you do not need to display in HTML, and you can use formats such as PDFs or even XML. Other common parameters are Rs:command, which indicates the action to apply to the path you specify. For example, Rs:command=render will render a report, and Rs:command=listchildren will list all the items in the folder.
If you do not specify a command, Reporting Services looks at the path you specified and calculates the appropriate action to take, such as rendering the report.
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 render a report in a comma-separated value (CSV) format without a header row, 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 and gives you plenty 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 close the entire toolbar by setting Rc:toolbar to False. For more information about the list of commonly used HTML rc:parameters.
If your WEB application uses frames to display reports (for example, a list of reports in the left pane and a report content in the right pane), you will need to use the Rc:linktarget parameter to specify the name of the content frame. Otherwise, when the user clicks on any embedded link in the report, the browser uses the entire window to display the report again, rather than keeping the frame of the Navigation Pane and the content pane.
You can use the URL parameters that I introduced to add the Quick Launch area, which contains a list of hyperlinks that enable users to open frequently used reports . Each hyperlink sets its target to _blank to open the report in a standalone browser window and uses parameters such as Rs:format=excel to control how the report is displayed in the browser.
Return to the top of the page embed a report in a WEB application
If the new browser window is no longer used when the user views the report, you might want to actually embed the report in your own Web page. The easiest way to do this is to use an IFRAME on a Web page and set the SRC attribute using the same technique that I've introduced based on URLs. If you use this technique, remember to set the Rc:linktarget parameter to the name of the IFRAME to avoid ejecting any frame outside the frame when the user clicks the link for the report.
If you have previously designed a asp.net server control, you may think that the IFRAME technique is not perfect because it requires WEB developers to understand the URL parameter syntax of Reporting Services to build the appropriate SRC string. Is it not a better idea to build a server control that encapsulates all the parameters and allows developers to simply set parameters such as Reportpath and Zoom?
Reporting Services is conveniently attached with an example named ReportViewer (installed to the \reporting Services amples\applications\reportviewer directory), which can implement this idea. The control encapsulates the IFRAME and URL access parameter logic, and provides a simpler way to embed reports in your application. To use the control, open and build the ReportViewer solution, and then switch 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 this 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 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 = "A" End IfEnd Sub
Return to the top of the page guarantee 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, and all members of a particular role will be allowed to perform the actions defined for that role.
Report Manager is used to perform security-related tasks, such as assigning user roles or modifying the permissions of an item (for example, a report). By default, the Administrators Windows group is also a member of the System Administrator and Content Manager for all folders. To extend the report access to other users, the user account or group must be added to a role such as Browser. This is usually set at the folder level, and can also be overridden for individual reports.
In addition to securing access to reports, administrators must decide how to connect reports to the source database. You can configure your data source to use integrated security so that you can use the current user account for database connections. In addition, can you define Windows? or SQL Server user name and password, and store it securely in the report server database. This approach requires the report to run in the absence of user interaction (for example, the interaction involved in a subscription).
Back to the top of the page using the Reporting Services Web service
The URL access method I've been using so far provides a quick and easy way to view reports and specify parameters, but it does not provide access to all of the available report management features. Reporting Services also comes with a rich-functionality API to provide you with access to advanced functionality through WEB services. For example, the Web service includes methods for managing data source information for a report or for enumerating all items in a folder.
I will use the WEB service to add a dynamic list of reports to the dashboard application. To do this, you will need to add a Web service reference to your application using the Add Web Reference. Specify the path to the Web service, as follows:
Http://MyServer/ReportServer/ReportService.asmx
After adding a reference, you can create an instance of the Reportingservice proxy class and invoke a method on the service, 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 want to enable impersonation by adding the Web.config file to the application. You also want to pass the client logon credentials to the service, as shown in the previous example.
The Listchildren method returns an array of Catalogitem objects that represent the items that the user has permission to view, including reports, folders, and data sources. The Path property provides a unique path to the item, for example, the/samplereports/sales Order Detail,type property allows you to distinguish between reports and other types of items.
Now that I've loaded the report name into the Drop-down list, I have to decide how to display the report within the dashboard application. I decided to expand the ReportViewer code I wrote earlier and change the Reportpath property as soon as the user selects a report and clicks the Go button. You can also use the IFRAME and set SRC to the appropriate URL (and prefix the report name with "Http://MyServer/ReportServer?"). ）。
In addition to report management functionality, the other major features provided by WEB services are report rendering. Most applications, including Report Manager applications, use the WEB Services feature to list reports or change settings, and use the URL method I introduced to display the report. However, if you want to fully control how the report content is handled in your application, you can use the Render method of the Reportingservice class. The method returns a byte array that can be saved to the output file at a later time, depending on the format of the request, the output file can be a graphics file or an EXCEL spreadsheet. It may be more complicated if you want to render the report in HTML format, because you will need to use the Renderstream method to render images or other resources separately.
Although the discussion in this article focuses on Web applications, you can also use the same technology to add report generation functionality to windows-based applications. The easiest way is to use URL access, or start the user's browser from the application, or include Microsoft WebBrowser ActiveX? Control and use the Navigate method to load the appropriate URL. The Render method of a Web service is a good alternative when you want to better control certain actions, such as displaying results in picture boxes or saving files directly to disk.
Information workers typically have to deal with two types of reports: the types they want to get when they need to collect information, and the types that appear on their desktops or in their emails every week. At this point, I've focused on the process by which users get their own reports, but Reporting Services also includes a very powerful feature called subscriptions, which allows you to "push" the report to the customer.
Back to top page subscription report
The Reporting Services subscription feature lets users receive reports as scheduled. Reports are usually sent to users by e-mail, but Reporting Services also lets you generate reports to a file share and even write your own commit extensions. This allows you to easily complete distribution tasks, such as sending weekly sales statistics reports to each sales rep, or setting up monthly financial statements to run on the last day of each month and write them out as a PDF to a company file share.
Before you set up a subscription, you need to consider how the report will connect to the database to retrieve its data. Therefore, the subscribed reports will not be executed directly by the user, and you cannot use integrated security for the data source, but you must specify the username and password to use when the report is run. If you attempt 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 the report Manager | Sample Reports, and then click AdventureWorks Data Source. The data source is shared by all the sample reports, so any changes made to it will affect all reports. Select the Credentials stored securely in the server option to specify a valid domain username and password for an account that has access to the AdventureWorks database, and select the use as Windows Crede Ntials when connecting to the data source option, and then click the Apply button. Note that this change means that the specified account will always be used to connect to the database, regardless of which user actually runs the report.
Therefore, you are ready to delve into and view the WEB service APIs that provide access to subscriptions. This part of using WEB services is more challenging than using the List or Render method, mainly because of the number of options available. The method I'm going to use is createsubscription and it belongs to the Reportingservice class. For a detailed description of the parameters of the method, see the Reportingservice class, but most fundamentally you need to specify the report to subscribe to, the build plan (for example, every Monday morning or the last Friday of the month), e-mail options (including e-mail addresses), and all report parameter values.
I have included a utility class named Emailsubscriber in the sample code for this article, which abstracts some of the complex content. The dashboard application uses this class to allow users to specify their e-mail addresses and to have the Employee Sales Summary report sent to them weekly by e-mail. In a product application, you can adjust the reports for each user, for example, when you create a subscription, you can retrieve their employee IDs based on the employee's Windows user account, and then set the report parameters appropriately.
If you don't want to wait until next Monday to test your subscription, it will help you learn more about how Reporting Services handles subscription plans. When you create a subscription, Reporting Services creates a SQL Server agent job that executes according to the required schedule. You can view this job by opening Enterprise Manager, expanding the Management, SQL Server Agent, and Jobs folder. The subscription job will have a "list Server" category and be named after the GUID that Reporting Services uses to track the job. Right-click the job and select Start job, and your report will send e-mail to you if you have correctly set up all aspects of your report.
Return to the top of the page what should I do next?
Two other Reporting Services features that you can use to add important report generation functionality to your application include Data driven subscriptions and snapshots. The former allows you to set up a user's mailing list and send them to a specific parameterized report in e-mail, which is the report view at the planning point and provides a historical view of the report. Reporting Services is built with a modular architecture that enables you to add powerful new extensions with your favorite,. NET-Targeted language If you need more powerful features.
John C. Hancock is a senior advisor at Microsoft dedicated to business intelligence and. NET development. He is now residing in Toronto, to contact him, please visit http://www.johnchancock.net.
An excerpt from the August 2004 issue of MSDN Magazine.
You can buy at the local newsstand or best subscribe.
Go to original English page
