SQL Server Reporting Services (SSRS)

This section describes how to install, deploy, and configure SSRS.

SSRS Installation

As a component of SQL Server, Reporting Services must be installed along with SQL Server. Currently, only Enterprise and Development versions have full support for SSRS in many versions of SQL Server, while Standard version provides most support, the Express edition with advanced features only supports some of the most basic functions (excluding the designer ). Therefore, Enterprise Edition should be selected for Enterprise deployment, and developers should choose Development edition.

It is feasible to select the relevant components of Reporting Services during SQL Server installation, or change the installation of an SQL Server to add SSRS.

Note that the above is the Server installation. The components of SQL Server 2005 Development Edition are divided into two parts: Server and workstation. After installing the relevant components on the server end, you also need to install the workstation components on the developed machine (either on the server or on another workstation. The Business Intelligence Development Studio must be installed. This is an extension of Visual Studio 2005. If VS2005 has been installed on the machine, it will directly integrate the BI Development Template into, if it has not been installed, it will automatically install a VS2005 shell for you (without components such as C # And VWD ).

SSRS configuration on the server

There are two ways to configure SSRS on the Server. One is to log on to the Server through SQL Server Management Studio, and the other is to access the Server's Report Manage page through web, such as http: // IP/Reports. The functions of the two methods are slightly different. For details about the operation process, refer to the relevant documentation.

Cheng

This section discusses some basic functions of SSRS, namely the creation, release, and reference of reports.

Create report

Design Environment

The report design environment is the Business Intelligence Development Studio, which is short for BI. If the Project is carried out in VS2005, It is very convenient, because you can add BI projects in a Solution just like adding a common Project. In fact, we did the same.

Basic Steps for creating a report

Before using BI to develop SSRS, we strongly recommend that you manually complete all the relevant tutorials in the Book Online that are attached to it.

In general, the design of a report can be summarized into the following steps:
Create a Report Project
Create Data sources (rds files), a bridge between reports and databases;
Create a dataset, that is, the report data source. The report obtains data from the dataset and does not directly access the database;
Layout)
Preview the result (preview) and make further modifications based on the results until the requirements are fully met.

Report Publishing

After designing a report, you must publish it to the Reporting Services Service for calling. We can call this release deploy.

After practical application, it is found that there are three methods to publish a report:

Compile and publish in BI

We designed reports in BI, which can be used to deploy the entire report project to the server at one time. The procedure is as follows:
Execute Project-> Properties in the menu, and change Configuration to Production, that is, edit the parameters in the Production mode;
Fill in the corresponding attribute values on the right. In general, the content of targetperformancefolder Data Sources remains unchanged. If the Data source is updated, you must set OverwriteDataSources to True;
Set TargetReportFolder. This value is a virtual directory in the Report Server. All rdl files of the project will be stored in this directory;
Set TargetServerUrl. Here is the URL of Reporting Services. For example, http: // localhost/ReportServer can be used for local deployment. Note that the following path is the default installation path. After you open the default site in IIS, you can see that it is a virtual directory;
After all are filled in, switch the compiling mode to Deploy in the compiling environment and Start Debugging. Then, BI will automatically Deploy this series of reports to the Report Server.

The http: // localhost/ReportServer page is displayed, which displays all ReportFolder on the Report Server, and the report is set according to deploy, save them in the folder. After entering Folder, click report to view details. The system has generated An aspx page with ReportViewer for us.

Report Manager on the Web

The following two methods are used to manage the Report Server. publishing a report is only part of their functions.

The procedure for using Report Manager is as follows:
Open the Report Manager page, which is generally http: // ServerUrl/Reports;
Go to the Data Sources folder and upload the rds file of the Data source;
Return to the root folder and create a ReportFolder named TargetReportFolder;
Enter the folder and upload the rdl files one by one. The report name is automatically named and the default value is generally accepted.

In this way, you can view the content on the ReportServer page.

SQL Server Management Studio

In SQL Server configuration, this tool is undoubtedly the most powerful. When logging on to SSMS, select "Server Type" as "Reporting Services" and specify the Server name and logon method. After successful login, the content we see in the Report Manager is in the Home directory. The subsequent operations are similar, so no text is wasted.

Use the ReportViewer control to reference a report

The ultimate goal of creating and publishing reports is to reference them in the program. Here we choose the simplest method-use the ReportViewer control.

ReportViewer in WinForm Environment

The ReportViewer control under WinForm is located in the Microsoft. Reporting. WinForms namespace. It appears in the ToolBar by default in VS2005 and can be operated directly by dragging it into the form.

Generally, the following parameters must be set for all reports:
ProcessingMode: This attribute is used to set whether the ReportViewer data source is local or Remote. Here we set it to Remote;
ServerReport. ReportServerUrl: The TargetServerUrl we saw earlier, that is, the URL address of the Report Server. Note that this address contains "ReportServer", such as http: // ServerUrl/ReportServer;
ServerReport. ReportPath: A combination of ReportFolder and ReportName, for example, "/Test/Report1.rdl". Note that the first "/" must not be omitted!

For practical applications, it is more common to use code to control ReportViewer than to set attributes during design. The following is a brief example, which summarizes the process as follows:

This. reportViewer1.ServerReport. reportPath = "/Test/Report1"; List <ReportParameter> parameters = new List <ReportParameter> (); parameters. add (new ReportParameter ("params", textQueryString. text); this. reportViewer1.ServerReport. setParameters (parameters); this. reportViewer1.ShowParameterPrompts = false; this. reportViewer1.RefreshReport ();

In the above process, set ReportPath first (ReportServerUrl has been specified in this example, and should actually be set through the setting string of App. config ). Create a report parameter list (this params name is the report parameter set during report design, which is referenced by @ params in an SQL statement) and call the SetParameters method of ServerReport, pass parameters to the report. Then, set the ShowParameterPrompts attribute of the report to false, that is, the parameter input prompt is not displayed in the ReportViewer header. Finally, execute the RefreshReport () method to refresh the report page.

ReportViewer in ASP. NET Environment

Microsoft's uniformity work is undoubtedly excellent. The ReportViewer in the Web is used exactly the same as that in WinForm. The only difference is that the control is located in Microsoft. reporting. webForms, and classes such as ReportParameter are changed to this namespace. Code control reports can be transplanted without any changes.

ReportViewer in Visual WebGUI

In the project, we use VWG as the framework of the program. The Gizmox development team also designed the corresponding version for ReportViewer. The control is located in Gizmox. webGUI. in the Reporting namespace, but note that its attributes such as ReportParameter and ProcessingMode are still located in Microsoft. reporting. in WebForms, do not make a mistake.

Access reports directly through ReportServer

Do you still remember the http: // ServerUrl/ReportServer mentioned above? SSRS has prepared a method for viewing reports, that is, access through URL. For example, to view the Report1 report under Test on the localhost/ReportServer server, you can directly enter http: // localhost/ReportServer in the browser? Test/Report1, SSRS will automatically call a built-in system page to display it. After this URL with parameters, we can control the report by appending URL parameters. For example, in the above example, you can use Response. Write () in ASP. NET to Write the following code to the page to display the report in the pop-up window:

"<Script language =/" JavaScript/"> window. open ('HTTP: // localhost/ReportServer? Test/Report1 & params = "+ textQueryString. Text +" & rc: Parameters = false & rs: Command = Render '; </script>"

For the construction method of URL parameters, see the MSDN documentation.

Transfer

All of the above are conventional applications, but in fact, most of a report can be done without that example from requirement submission to final deployment, and various problems will be encountered in the middle. In this section, I list the problems and solutions encountered during the development process in different categories and update them continuously.

Deployment and debugging
Q: Why does the {user "nt authority/network service" fail to perform this operation when I use ReportViewer to load reports in ASP. NET. (RsAccessDenied)} error?
A: This is because the access permission to the ReportServer virtual directory under IIS is not set correctly. There are three solutions:
Access http: // localhost/Reports on the server, go to Report Manager, and click "create role assignment" on the "properties" tab ", in "group or user name", enter "nt authority/network service" (no quotation marks on both sides). Select Browser below to confirm. This is to grant this account the permission to browse reports. I strongly recommend this method;
In IIS, modify the properties of the ReportServer virtual Directory under the default site. On the Directory Security tab, click Edit in Authentication and access control to enable Anonymous access, set the anonymous access account to the Administrator account, set the local Logon account to Administrator, and set the domain account to the domain account with Administrator permissions. In this way, you can access the ReportServer connection to view the report as an administrator. This is also the most widely spread method on the Internet, but there are serious security risks, there is no problem during development and debugging, and it is not recommended for deployment;
Create an anonymous account for Reporting Services, such as IUSR_ReportView, assign the Browser role to Report Manager, and perform subsequent steps. This is the most troublesome thing. I am too lazy to write all the steps here. It is also widely spread on the Internet, but I think it will only be used by people who are idle and have nothing to do...

Q: I developed VWG in Visual Studio and run it in debug mode. When ReportViewer exports a PDF file, the Session Expired error is reported. What is the problem?
A: Actually, I don't know why... The solution is to access the site directly in the browser without using debug. As for the reason, we call for an advanced explanation ~~~

Report data
Q: I don't just want to pass traditional SQL parameters to reports, such as @ ID, @ Count, instead, I want to control the entire WHERE clause and any part of the SQL statement in the form of parameters?
A: Of course. This is not the case. SSRS is too much. []

/R/n this article from the CSDN blog, reprint please indicate the source: http://www.flatws.cn/article/program/sql-server/2010-08-25/9965.html