Reporting Services Handscript

Document directory

• Design Environment
• Basic Steps for creating a report
• Compile and publish in BI
• Report Manager on the Web
• SQL Server Management Studio
• ReportViewer in WinForm Environment
• ReportViewer in ASP. NET Environment
• ReportViewer in Visual WebGUI

SQL Server Reporting Services (SSRS) is a powerful report service. In the relevant documentation of MSDN and Google results, you can find its general description. When SQL Server 2008 is still brewing and 2000 is outdated, we naturally discuss its version 2005.

Unfortunately, it seems that the application of this function is still not extensive, so it is so difficult to find a very comprehensive and skillful tutorial. But the good news is that this is a product developed by Microsoft! Adhering to the excellent tradition of Microsoft products that are always easy to use and informative, the exploration of SSRS is quite worthy of the special book ~~~

Start

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 a 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:

1. Create a report project;
2. Create Data sources (rds files), a bridge between reports and databases;
3. Create a dataset, that is, the report data source. The report obtains data from the dataset and does not directly access the database;
4. Layout );
5. Preview the result (preview) and make further modifications based on the results until the requirements are fully met.

In my other log, there is a simple example from SQL Book Online, which can be used as a reference.

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:

1. Execute Project-> Properties in the menu, and change Configuration to Production, that is, edit the parameters in the Production mode;
2. 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;
3. Set TargetReportFolder. This value is a virtual directory in the Report Server. All rdl files of the project will be stored in this directory;
4. 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;
5. 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:

1. Open the Report Manager page, which is generally http: // ServerUrl/Reports;
2. Go to the Data Sources folder and upload the rds file of the Data source;
3. Return to the root folder and create a ReportFolder named TargetReportFolder;
4. 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 busy .. A report statement is required here. I have used Excel. If the cell value starts with "=", it can be followed by expressions and report statements. The simplest example is as follows:

  1. Choose Report> Report Parameters from the menu, and Add a new parameter. Assume that the Name is WhereString of the type, which is a String of other types.
  2. Modify the DataSet of parameters to be accepted on the Data page. Assume that the content of the original SQL statement is "select * from table1 where age between 10 and 15 and id> 40"
  3. Change to = "select * from table1 where" + Parameters! WhereString. Value
  4. Okay. Switch to Preview. Check the result.

  Note that there is a space behind the where clause, especially when constructing an SQL statement.

• Q: I did not fill in anything in the Parameters prompt box. I directly clicked View Report. An error occurred...

  A:-_-| of course, an error occurs. The where clause in the SQL statement has no conditions and the syntax is incorrect .. In this case, you have to make a judgment in the expression statement to check whether the input parameter is null. The above example can be changed:

  = "Select * from table1" + IIF (Parameters! WhereString. Value <> "", "where" + Parameters! WhereString. Value ,"")

  Here, IIF is a report statement, which belongs to the Program Flow statement. For more extensive usage, see the documentation ~~

• Q: What should I do if I want to add page X/Page Y to a page?

  A: You still need to use the report statement. In the Layout View, right-click any text box and select Expression, which is the one with the "fx" icon in front. The Expression editor is displayed. The list in the lower-left box lists the report statement categories. Do you see Globals? Click Here and there will be some constants about the report itself on the right. The PageNumber and TotalPages are used here. The Parameters and IIF Parameters used earlier can also be found here. Flexible application of these elements can greatly enrich the expressiveness of reports.

  Oh, forget to mention that both PageNumber and TotalPages can only be used at the header or footer. Therefore, before using them, you need to use them in the Layout View, choose Report> Page Header (or Page Footer), enable the Header or Footer, and reference it in it.

Report Layout problems

• Q: Why are my reports horizontally displayed on different pages? I clearly did not cross-border in the design page!

  A: cft... your eyes have cheated you. The page boundary in the design field has nothing to do with your paper page, and SSRS does not provide the entire page center function. Therefore, you must adjust your page layout a little bit.

  First, we recommend that you change the left and right Margin values to 0 on the Report-> Report Properties-> Layout tab. Return to the design page and manually specify the Locate attribute of the control to determine the size of the Left Border. For example, if the current paper size is A4 (21 cm * 29.7 cm), the total width of my body table is set to 17.75 cm, and the remaining 25cm is the sum of the left and right margins, it is reasonable to say that 1.625 cm should be allocated each, but considering the table line in the table also has a width (I set it to 1pt), the left margin is set to 1.6, that is to say, place the control on the left side of the design page at 6cm, the clear point is to set the value of the Left element in the Locate attribute of each control on the Left to 1.6 .. I didn't mean it ..).

  We recommend that you use the export function of ReportViewer to export the data to pdf for preview, and print the data to pdf and xps. If you are more proud, print the data directly to the paper to see the best. This fine-tuning is a painful process.

Integration

After this period of development experience, I personally feel that SSRS is still very useful. It is seamlessly integrated with SQL Server. layout is flexible and changeable, and the powerful functions of the Report Language, coupled with the affinity of Microsoft products, it brings a fresh user experience. However, SSRS execution efficiency is not high. In particular, SSRS of SQL 2005 can only be queried using SQL statements when creating DataSet. it is not convenient to implement complicated business logic. In addition, the dynamic Column Display and dynamic page settings need to be improved. We hope SQL Server 2008 SSRS can be upgraded to the next level ~~