Getting Started with SSRS

up

This section is mainly about installation, deployment, and configuration issues for SSRS. installation of SSRs

Reporting services, as a component of SQL Server, is naturally installed along with SQL Server. With many versions of SQL Server, only the enterprise and development versions have full support for SSRS, the standard version provides most support, and the Express Edition with advanced features only supports some of the most basic features (excluding designers). Therefore, in the enterprise deployment should be selected enterprise version, as a developer should choose the development version.

It is possible to select the related components of Reporting Services during SQL Server installation, or to change the installation of one SQL Server to add SSRS.

Note that the above is a server-side installation. SQL Server Development Edition components are divided into server and workstation two parts. After installing the server-side related components, you also need to install workstation components on the machine that is being developed (either on the server or on a different workstation). The business Intelligence Development Studio must be installed, which is an extension of Visual Studio 2005 that will integrate BI's development template directly into VS, if the machine has already been installed VS2005. If it is not installed, it will automatically install a VS2005 shell for you (no C #, VWD and other components). SSRS on server-side configuration

There are two approaches to server-side configuration SSRs, one is to log on to the server through SQL Server Management Studio, and the other is to access the server's manage page, such as Http://IP/Reports, via the web. There is a slight difference in functionality between the two methods, which allows you to view related documents. Underwriting

This section discusses some of the basic functions of SSRS, namely, the establishment, publication, and reference of reports. Create a report Design Environment

The report design environment is the business Intelligence Development Studio, hereinafter referred to as BI. If the project is under VS2005, it is convenient because you can add a bi project in a solution like normal project. Actually, we did the same thing. Basic steps for report creation

Before you start using BI to develop SSRS, it is highly recommended that you manually complete all the related tutorials in the random book online.

In general, the design of a report can be summed up in the following steps: Create a report project set up a data source (source,rds file), which is the bridge between the report and the database; Create a DataSet, which is the data source of a report, that gets data from a dataset and does not directly access the database; Make page layout (layout) preview results (preview) and make further modifications based on the results until the release of the required report is fully met

After the report is designed, it is published to the Reporting Services service for invocation. We can call this release deploy.

After practical application, we found that there are three ways to publish the report in BI

Our design report is in BI and can be used to deploy the entire report project to the server at once. The general steps are as follows: Menu execution project->properties, change configuration to production, edit the parameters of production mode, and fill in the corresponding property values on the right. Generally targetdatasourcefolder content data sources unchanged, if the data source is updated, then the above overwritedatasources must be set to true; Set TargetReportFolder. This value is a virtual directory in the table server where all RDL files for the project will be stored in this directory; Set TargetServerURL. This is the URL address where Reporting Services resides, such as a local deployment that can be http://localhost/ReportServer. Note that the path that follows is the default installation path, you can see it when you open the default site in IIS, it is a virtual directory; After completing, in the compilation environment to switch the compilation mode for deploy, then start debugging, then BI will automatically to the The server deploys this series of reports.

After that, it will show http://localhost/ReportServer this page, which shows all the Reportfolder on the report server, and the report is saved in these folder according to the Deploy settings. After entering the folder, click on the report to view, the system has generated for us a ReportViewer aspx page. through the web under the Manager

Both of the following methods are used to manage the report server, and publishing reports is only part of their function.

The general steps for using the manager are as follows: Open the page of the manager, generally http://ServerUrl/Reports; go to the Data Sources folder, upload the RDS file for the source; Back to the root folder, Set up a reportfolder, the name is the value in the TargetReportFolder; Go to the folder, upload the RDL file one at a time, it will automatically give the report name, generally accept the default value.

This is OK, and you can then view the content under the ReportServer page. through SQL Server Management Studio

In the configuration of SQL Server, this tool is undoubtedly the most powerful. When you log on to SSMs, select server type reporting Services, and then specify the name of the server and how to log on. After the success of the login, in the home directory is what we see in the manager of the content, followed by similar operations, do not waste text. referencing reports with ReportViewer controls

The ultimate goal of creating and publishing reports is to refer to them in a program, where we choose the simplest method-using the ReportViewer control. the ReportViewer in the WinForm environment

The ReportViewer control under WinForm, located under the Microsoft.Reporting.WinForms namespace, appears by default in the toolbar in VS2005, and it can be manipulated directly by dragging it into the form.

In general, there are several parameters that must be set for all reports: Processingmode: This property is used to set the source of the ReportViewer data locally or remotely, where we set to remote; Serverreport.reportserverurl: That's the targetserverurl that we saw earlier, the URL address of the report server. Note that this address contains "ReportServer", such as Http://ServerUrl/ReportServer; Serverreport.reportpath: Is the combination of Reportfolder and reportname, such as "/TEST/REPORT1.RDL", pay attention to pay attention, the beginning of the "/" must not be omitted.

For practical applications, using code to control ReportViewer is more common than design-time settings, and here's a short example that outlines a process:

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 procedure, you set Reportpath first (ReportServerUrl is specified in this example and should actually be set by App.config's set string). Then create a list of report parameters (the params name is the report parameter set when you design the report, referenced in the SQL statement by @params), and then call the ServerReport Setparameters method and pass the parameters to the report. Next, set the report's Showparameterprompts property to False, that is, not ReportViewer the header display parameter input prompt. Finally, execute the Refreshreport () method to refresh the report page. ASP. NET environment under the ReportViewer

Microsoft's unity work is certainly very good, the web under the ReportViewer in use and WinForm under exactly the same, The only difference is that the control is under Microsoft.Reporting.WebForms, and classes such as ReportParameter are also changed to this namespace. In code control reports, you do not need to make changes to migrate. the ReportViewer under Visual WebGui

In the project, we use VWG as the framework of the program. The Gizmox development team also designed the appropriate version for ReportViewer, where the control is located under the Gizmox.WebGUI.Reporting namespace, but be aware that its properties such as ReportParameter, Processingmode and so on are still located under the Microsoft.Reporting.WebForms, this point does not make a mistake. Access reports directly through ReportServer

Remember the Http://ServerUrl/ReportServer mentioned earlier? SSRS has prepared a way for us to view the report by URL access, for example, to see the Report1 report in the Localhost/reportserver server under test, which can be entered directly into the browser http:// Localhost/reportserver? Test/report1,ssrs automatically invokes a system-built page to display it. After this URL with the parameter, we can control the report by appending the URL parameter. For example, the above example, in asp.net can use Response.Write () to the page to write the following code to pop-up window to display the report:

"<script language=\" javascript\ "> window.open (' Http://localhost/ReportServer? Test/report1&params= "+ Textquerystring.text +" &rc:parameters=false&rs:command=render "; </script> "

Where the URL parameter is constructed, refer to the MSDN related documentation. Turn

All of this is a regular application, but in fact a report from the request to the final deployment, most of them will not be able to do that example, the middle will encounter a variety of problems. In this section, I enumerate the problems encountered in the development process and the solutions in the form of questions and answers, and keep updating them. Deployment and Debugging Q: I use the ReportViewer to load the report under the ASP.net, why the {user "NT authority\network SERVICE" has not been granted enough permissions to perform this operation. (rsaccessdenied)} of errors.

A: This is because the access rights for the ReportServer virtual directory under IIS are not set correctly. There are three ways to solve the problem: Access Http://localhost/Reports on the server side, go to the Manager, and then click New Role Assignment under the Properties tab to fill in the "NT authority\" in "Group or user name" Network SERVICE "(without both sides of the quotes), tick below browser, OK. This is the right to assign the account to browse the report, and I strongly recommend this method; In IIS, modify the properties of the ReportServer virtual directory under the default site, and in the Directory Security tab, click Authentication and Access Control, open anonymous access, anonymous access account to the Administrator account, the local login is set to the administrator, domain account login is set to have administrator rights domain account. This allows access to the ReportServer connection to browse the report with administrator privileges. This is also the most widely circulated online method, but there are serious security risks, the development of debugging when there is no problem, the real deployment is not recommended; specifically for Reporting Services to create an anonymous account, such as Iusr_reportview, and then in the Manager to assign the browser role, and so on, and so on later steps. This is the most troublesome, the steps I am too lazy to write all here, the same online spread very wide, but I think only really idle people will use ... Q: I developed VWG in Visual Studio, ran in debug mode, and then reported the session expired error when ReportViewer exported the PDF.

A: Actually, I don't know why. The solution is not to debug the way, directly in the browser to visit the site, OK. As for the reason, call the high man to explain ~ ~ ~ ~ ~ Report Data related Q: I'm not just trying to pass the traditional SQL parameters to the report, like @id, @Count, but rather the whole where clause and the entire SQL statement can be controlled in the form of parameters, OK.

A: Sure, that's all right, that SSRS is too 圡. The report statement needs to be used here. Use Excel bar, cell value if "=" start, then you can follow the expression, the report statement is the same. One of the simplest examples, like I now want to pass an entire WHERE clause with a parameter, is to do this: Report->report parameters,add a new parameter in the menu, assuming name is wherestring, type string, and nothing else. Modify the dataset that accepts parameters in the data page, assuming that the contents of the original SQL statement are "SELECT * from table1 where age between" and id>40 "modified to =" SELECT * FROM t Able1 where "+ parameters! Wherestring.value Good, switch to the preview, see the results.

Note that there is a space behind the where, especially when constructing SQL statements, be sure to pay special attention to the use of spaces. Q: I did not fill in that parameters hint box, directly click the view, the wrong ...

a:-_-| | Of course, there will be errors in the SQL statement where there is no condition, the syntax is not correct ah ... In this case, you have to make a judgment in the expression statement to see if the arguments passed in are null. The example above can be read as follows:

= "SELECT * FROM table1" + IIF (parameters! Wherestring.value<> "", "where" + parameters! Wherestring.value, "")

The IIF here is a form of statement that belongs to the program flow statement. For more extensive usage, refer to the documentation ~ Q: It's amazing, so you can tell me what to do if I want to add "page X/y page" to a page.

A: This still uses the report statement. Enter the layout view, right-click in any text box and select expression, the one with the "FX" icon in front of you, to see the expression Editor. In the bottom left box is listed in the report statement category, see Globals? Click on the right there will be some constant on the report itself, that PageNumber and totalpages is here to use. The parameters and IIf used in front are also found here. Flexible application of these elements can greatly enrich the expressive performance of the report.

Oh, forget it, pagenumber and totalpages can only be used at the header or footer, so you need to use them in Layout view, menu Report->page header (or Page Footer), open header or footer, and then refer to it inside. Report Layout related Q: Why is my report printed horizontally across the page after printing it? I clearly did not cross the line in the design page.

A:CFT ... Your eyes have deceived you. The page boundary of the design interface is actually not related to your paper page, and SSRS does not provide a full page centered function, so you have to fine-tune your page layout a little bit.

First I recommend that you change the left and right margin to 0 in the menu report->report properties->layout tab. Then go back to the design interface and determine the size of the left edge by manually specifying the control's locate property. For example, now the paper is A4 size (21cm*29.7cm), The total width of my body table is set to 17.75cm, the remaining 3.25cm is the sum of the left and right margins, it should be allocated 1.625cm, but given the table in the table line is also a width (I set is 1pt), so the left margin set to 1.6cm, that is, the control placed in the distance from the design page to the left 1.6cm position , the other point is to put the left side of the control of the locate property in the value of the left-hand element set to 1.6cm (many of the attribute ah ... I didn't mean to.. ）。

When previewing, it is recommended that you export to PDF view with ReportViewer Export, or you can print to PDF, XPS, and if you tyrants a little more, it is best to print directly to the paper. This fine-tuning is a painful process. Hop

After this period of development experience, personal feeling SSRS still very good, with the seamless integration of SQL Server, layout flexibility and the powerful features of the report language, coupled with the affinity of Microsoft products, brings a refreshing experience. However, SSRS execution efficiency is not high, especially the SQL 2005 version of the SSRS in the dataset can only use SQL statements to query, the implementation of complex business logic is not very convenient. And in dynamic column display and dynamic page settings still need to improve.