Use SQL Server Reporting Services to generate user-friendly reports from the application

xiaoxiao2021-03-05 20

This page

Reporting Services Overview Design First Report Deployment and Test Sets the report to the web application to embed the report to the Web application secure report safe to use the Reporting Services Web service subscription report Next what to do?

Flexible reporting features are a requirement for most business applications, which are more wide after integrating into a web application. With the latest version of SQL Server® 2000 Reporting Services, you can easily have report generation functions from various data sources. In this article, I will introduce to the use of Visual Studio® and Reporting Services to write reports and demonstrate how to integrate reports into the web application.

Reporting Services is based on the server-based report generation platform, which is built on .NET Framework and integrated with SQL Server 2000, so you can use an extension-based web-based API to integrate rich report generation power into the application . Although the report server uses SQL Server as a repository of the report, it can be used to provide data for the report using the OLE DB, ODBC, or ADO.NET provider, which makes Reporting Services become generated in a variety of enterprise environments. Extreme tools for reports.

Reporting Services is authorized as part of SQL Server 2000, so if you have an authorized SQL Server copy, you can run Reporting Services on the same server without paying additional license fees. If you decide to run the Reporting Services on a computer (for example, a web server) authorized by SQL Server (for example, web server), you will need to purchase additional SQL Server licenses.

To start using Reporting Services, you can download the 120-day trial software in SQL Server 2000 Reporting Services, which also includes instructions on how to get the installation media of the product. When installing the product, make sure that the options including the sample report are selected as I want to integrate these reports into the web application of this article.

Reporting Services Overview

Reporting Services Report Design Using New Visual Studio .NET 2003 Report Designer, the designer can be used after installing Reporting Services on the same computer installed with Visual Studio. The designer outputs a report layout and data access in a new XML report definition language (RDL), and the RDL file will be released to the report server. If you want to provide a creation function for users, you can also use third-party report designers.

Administrators can use the web-based report manager to manage published reports, and can perform some tasks, for example, to ensure safe or change the data source connection string of the deployed report for some user group reports. Users can also use the report manager to browse and view reports; but many companies tend to use the URL request or Reporting Services Web service to direct the report to the existing internal Web site or application.

Figure 1 Reporting Services Architecture

The report can be parameted so that users can choose from the selection list when viewing the report; they can also export into a variety of formats, such as Microsoft®Excel, PDF, and XML. Although the Timetop provides the latest data, it is also possible to cache a report to improve performance and reduce the load on the data source. For business intelligent applications, the report can access Analysis Services OLAP cubes, and Reporting Services can even import existing Microsoft Access reports, although there is a difference in these two technologies, it does not support all Access functions. See Figure 1 for details on the Reporting Services architecture. Back to top

Design first report

Although the focus I introduced is to integrate the report to the application rather than a creation report, the best way to familiarize with Reporting Services is to immediately create a new report item. To do this, open Visual Studio and create a new item with Report Project Wizard, which is part of the Business Intelligence Projects. Specify a name for your project, click "OK", and then the Report Wizard dialog box will be displayed. In this step of your choice of data sources, click the "Edit" button to specify the AdventureWorks2000 that is connected to your database server, click OK, and then select the option to share the shared data source.

Click "Next" to enter the "Design the Query" step, specify a simple SQL statement (for example, "select * from product") and then click "Next". Reporting Services supports a standard table format (Tabular) report that displays a set of fixed columns, also supports a matrix (MATRIX) report with a cross-table style view with a dynamic column, so select Tabular for the report, then click "Next" .

Drag some columns (eg, ProductID, and Name) to the Details section and click "Next". Select the default Bold style, click "Next", then specify the URL of the virtual directory of your report server, for example, http: // myserver / reportserver. Now click "Next". In the last step, you can specify a name for your first report, for example, Products Report; and then click Finish.

The wizard will create the project, and also create a report that can be shared by multiple reports, as well as a report on Products data in the designer (see Figure 2). By default, the display allows you to modify the Layout tab of the report.

Figure 2 Visual Studio Report Designer

The DATA tab is used to specify the query of the report, including setting parameters to prompt the user to provide a value when the report is executed. Click the "Preview" tab to test the way the report is displayed to the user.

Now you can add additional reports to the item, or add additional columns to add data to the data, add aggregate, or even add additional tables that can display data from other queries to modify the product. Unlike a lot of report designers, Reporting Services uses out-of-band design, which makes it easy to create a report that sets various source data in the same location.

The report items you have just created consists of a set of files currently residing on your development machine. In order to publish these reports to users, you need to deploy the project to the report server. Back to top

Deployment and testing

Before deploying report projects, you need to specify which server deployed to which server is deployed. If you initially use the wizard to create a report item, 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. Perform this setting allows you to specify the item's folder name and the URL of the Reporting Services Web service. Select "Build | Deploy Solution" on the menu to build a report item and deploy it to the server.

After you deploy the report, you can test it. Each report that has been deployed to the server has a unique path that can be used to display reports in the browser. For example, to display the first report you created, open your browser and navigate to http: // myserver / reportServer? / Report Project1 / Products Report. This will present a report in an HTML format and display a toolbar at the top of the page so that you can turn your report to change the scales, even in a variety of other formats (eg, PDF).

You can also browse a list of all report items deployed to the server by navigating the following URLs on the server: http: // myserver / reportserver. Report servers provide you can view the deployed report type and view the smallest user interface of the report.

Report Manager is a more friendly, more featured tool, which can access the tool by simply navigate to the following URLs on the local server: http: // myserver / reports.

Add reports to web applications

Since you know how to design, deploy and test reports, let us integrate these reports into the app! Many web applications include custom homepage for each user, is often referred to as "dashboard." This page typically provides a summary information for user quantiles and other functions of accessing applications as a jump point.

I will build a dashboard with the sample report included with the Reporting Services to demonstrate the report integration, as shown in Figure 3. If you have installed the sample report during the installation process, you can open the report item (install to / Reporting Services / Samples / Reports) and deploy it to the test server. You can test the sample report by browsing to the Samplereports folder using the Report Manager tool.

Figure 3 Dashboard Web Application

The easiest way to provide users with access to the report from the application is to use hyperlinks. As you have seen, each report has a unique, used to display the path to the report. For dashboard applications, the report should be opened in a separate window, so I will specify a goal for the hyperlink. So far, this is very simple, but what is the situation for reports with parameter reports (for example, the Employee Sales Summary report? To truly integrate the report into the application, you usually need to specify certain values for the parameter so that users will not be prompted.

Reporting Services makes this task very simple: the value of the report parameters 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 of employees who appear to be numbered in November can be:

November Sales Summary

The parameters that do not have the values specified in the URL will use the default values set in the report designer, so the ReportYear value used by the example sales summary report used is 2003. The Reporting Services also has a built-in parameter for controlling report display mode. These parameters distinguish your own report parameters by adding prefix "RS:".

One of the most useful parameters is the RS: Format parameter, which is used to specify the format of the rendering report. This allows you to include reports that need to be displayed in HTML, and can also use formats such as PDF even XML. Other common parameters are RS: Command, which indicates the operation of the path to be applied to the path you specify. For example, RS: command = render will present a report, and RS: command = listchildren will list all in the folder.

If you don't specify a command, Reporting Services will view the path you specified and calculate the appropriate operation to take, for example, rendering reports. Figure 4 shows the list of RS: parameters. Another group of built-in parameters controls behavior in various output formats and uses RC: Prefix. Each report format has its own specific set of parameters. For example, you should render a report in a comma-separated value (CSV) format without title, you should use the RC: NoHeader parameter:

Embed a report into a web application

If you want to use a pop-up new browser window when you view the report, you may want to actually embed the report into your own web page. The easiest way is to use iframe on the web page and use the URL-based identical technology I introduced to set the SRC property. If you use this technology, remember to set the RC: LinkTarGet parameter to the name of the iFrame to avoid an arbitrary framework outside the frame when the user clicks on the report. If you have previously designed ASP.NET server controls, you might think that iframe technology is not perfect because it requires Web developers to understand the URL parameter syntax of Reporting Services to build the appropriate SRC string. Building a package has all the parameters and makes developers to set the server controls such as ReportPath and ZOOM. Isn't a better idea?

Reporting Services is very convenient to include an example called ReportViewer (installed to the / reporting service / samples / application / applications / reportViewer directory), it can achieve this idea. The control encapsulates the IFRAME and URL access parameter logic and provides a simpler way to embed the report into the application. To use this control, open and build the ReportViewer solution, then switch to your web application by browsing to the ReportViewer.dll in the bin directory of the ReportViewer solution, and add the control to the toolbox. You can use this control to add an embedded report 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.

ReportViewer1.serverUR = "http:// myserver / reportserver"

ReportViewer1.ReportPath = "/ Samplereports / Sales Order" & _

"Detail & SalesOrdernumber = so8153"

ReportViewer1.toolbar = ReportViewer.Multistate.false

ReportViewer1.Zoom = "75"

END IF

End Sub

Guarantee the security of the report

Reporting Services security uses familiar roles based models. User and groups can be assigned to roles such as System Administrator or Browser, and all members of a particular role will allow the operation to be executed as the role.

Report Manager is used to perform information related to security, for example, allocating user roles or modifying items (eg, report) permissions. By default, the Administrators Windows group is 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 the role such as Browser. This is usually set in the folder level, and it can also be rewritten for a separate report.

In addition to protecting access to reports, administrators must also determine how to connect reports to the source database. You can configure the data source to use integrated security so that you can use the current user account for database connections. In addition, you can define a Windows or SQL Server username and password, and securely store it in the report server database. This method requires that the report is run in the case where no user interaction (eg, the interaction involved)). Back to top

Use the Reporting Services Web Services

So far, the URL access method I have been using provides a quick 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 features to provide you with access to advanced features through Web services. For example, the web service includes methods for managing data source information of the report or enumerates all items in a folder.

I will add the dynamic list of the report to the dashboard application. To do this, you will need to add a Web service reference to the application using the Add Web Reference. Specify the path to the web service, as shown below:

http://myserver/reportserver/reportservice.asmx

After adding a reference, you can create an instance of the ReportingService Agent class, and call a method on the service, just like 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

End Sub

If you are deploying an application in an intranet environment, you have to enable simulation by adding to the application's web.config file. You also want to pass the client login credential to the service, as shown in the example above.

ListChildren methods Returns the array of CatalogItem objects that represent users who have the right to view, including reports, folders, and data sources. The Path property provides the unique path to the item, for example, / samplereports / sales ORDER DETAIL, TYPE properties allows you to distinguish between reports and other types.

Since I have loaded the report name into the drop-down list, I have to decide how to display the report in the dashboard application. I decided to extend the ReportViewer code I have written early and change the ReportPath property as long as the user selects a report and click the "Go" button. You can also use iframe and set the src to the appropriate URL (and prefix "http: // myserver / reportserver?") For the name of the report.

In addition to report management functions, other main functions provided by the web service are reported rendering. Most applications (including report manager applications) use Web service functions to list reports or change settings, and use the URL method I have introduced to display the report. However, if you want to completely control the way the report content is handled in the application, you can use the Render method for the RenderingService class. This method returns a byte array that can be saved to the output file in the future; depending on the format of the request, the output file can be a graphic file or an Excel spreadsheet. If you want to rendering a report in an HTML format, it may be more complex because you will need to use the Renderstream method to present images or other resources separately. Although this paper focuses on web applications, you can also use the same technique to add report generating software to Windows-based applications. The easiest way is still accessible using URL, or starts a user's browser from an application, or includes the Microsoft WebBrowser ActiveX Control and use the NaviGate method to load the appropriate URL. When you want to better control certain operations (for example, display results in the picture box or save the file directly to disk), the Web service's render method is a good alternative method.

Information workers usually have to handle two types of reports: When they need to collect information, they want to get the type and the types of each week appear on their desktop or in their email. At this point, I have focused on the process of obtaining their own reports, but Reporting Services also includes a very powerful feature called subscription, which allows you to "push" to our customers.

Subscription report

The Reporting Services subscription feature allows users to receive reports as planned. The report usually sent to the user via email, but Reporting Services also allows you to generate the report to a file share even to write your submission extensions. This allows you to easily complete the distribution task, for example, send a weekly sales statistical report to each sales representative, or set a monthly financial statement to run in the last day of each month, and write it as a PDF Sharing from the company file.

Before setting your subscription, you need to consider how the report will connect to the database to retrieve its data. Therefore, the subscribed report will not be executed directly by the user, 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 have tried to subscribe to the report settings for the integrated security, Reporting Services will return an error.

To change the data source of the exemplary report, open "Report Manager | Sample Reports" and click the AdventureWorks source. This data source is shared by all sample reports, so any changes to it will affect all reports. Select the "Credentials Stored Securely In The Report Server" option, specify a valid domain username and password with an account that access to the AdventureWorks database, select "Use as Windows Credentials 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 is actually running the report.

Therefore, you are ready to study in-depth research and view web services APIs to subscribe access. This part of using Web services is more than a few challenges than using the List or Render method, the main reason is the number of available options. The method I will use is CreateSubscription, which is also a ReportingService class. For detailed description of the parameters of this method, see the ReportingService class, but the most basic thing is that you need to specify the report to subscribe, build a plan (for example, on Monday morning or monthly on the last Friday), email options (including Email address) and all report parameter values. I have included the utility class named Emailsubscriber in the sample code in this article, which has been summarized. Some complex content. Dashboard Application Use this class to allow users to specify their email address and make the Employee Sales Summary report to them every Monday through email. In the product application, you can adjust the report of each user, for example, when you create a subscription, you can retrieve their employee ID according to the employee's Windows user account, and then set the report parameters appropriately.

If you don't want to test your subscription next Monday, it helps you know more about how Reporting Services handles the subscription plan. When you create a subscription, Reporting Services creates a SQL Server Agent Job that executes as required. You can view this job by opening Enterprise Manager, expanding the Management, SQL Server Agent, and Jobs folders. The subscription job will have a "Report Server" category and named the GUID named by Reporting Services to track the job. Right-click on the job and select Start Job, if you have correctly set up all your reports, your report will send you an email.

What should I do next?

You can use two other Reporting Services features that generate functionality to your application include Data Driven Subscriptions and Snapshots, which allows you to set up users' mailing lists and send specific parametric reports to them in email. The latter is a report view of the scheduled point while also providing a historical view of the report. Reporting Services is built using modular architecture. If you need more powerful features, it allows you to use your favorite, with .NET language to add a powerful new extension.

转载请注明原文地址:https://www.9cbs.com/read-39246.html

9cbs

New Post(0)