Reporting Services_PIOLEDB_Tutorial

使用报告服务(Reporting Services) 创建即席报告应用程序(Ad Hoc Report Application)摘要：您是 .NET 编程或报告服务(Reporting Services) 的初学者吗？下面介绍如何使用报告服务API 创建 .NET 应用程序以创建和发布报告。

本页内容先决条件使用 2003 创建解决方案将Error_Page 窗体添加到项目中将RDLGenerator 窗体添加到项目中将ManagingReports 窗体添加到项目中小结先决条件此项目要求将Microsoft IIS 6.0、Microsoft Visual 2003、Microsoft SQL Server 2000 和SQL Server 2000 报告服务安装在开发人员机器上。

SQL Server 2000 和报告服务的版本应该是一样的。

例如，如果使用的是SQL Server 2000 Developer Edition，那么也需要使用报告服务开发人员版本。

用于报告的SQL Server 数据源不必在本地，也不要求是同一版本。

用户需要能够访问远程数据源从而加以使用。

报告服务数据库（ReportServer 和ReportServer TempDB）必须在本地。

所有SQL Server（不论是作为数据源引用的还是由报告服务使用的）都必须启用Windows 身份验证或混合模式身份验证。

返回页首使用 2003 创建解决方案1.打开Visual Studio .NET 2003，并在Start Page 上单击New Project。

2.选择Visual Basic Projects, Web Application。

3.将项目命名为CreateReport，并单击OK。

图14.右键单击References，并从快捷菜单上选择Add Reference。

5.在Add Reference 模式窗口上单击Browse。

SQL Server Reporting Servi 发布日期：09/03/2004|更新日期：09/03/2004John C.Hancock本文讨论：Reporting设计和部署报表使用Reporting Services的Web服务保证报表的安全本文使用下列技术：SQL Server、、Visual 代码下载：SQLServerReportingServices.exe(222KB)本页内容Reporting Services概述设计第一个报表部署和测试将报表添加到Web应用程序将报表嵌入到Web应用程序中保证报表的安全使用Reporting Services Web服务订阅报表接下来该怎么做?灵活的报表功能是大多数业务应用程序的一个要求，这些报表功能在集成到Web应用程序中之后用途更加广泛。

利用SQL Server 2000 Reporting Services的最新版本，您可以轻松地具有来自各种数据源的报表生成功能。

在本文中，我将介绍使用Visual Studio和Reporting Services来编写报表，并演示如何将报表集成到Web应用程序中。

Reporting Services是基于服务器的报表生成平台，该平台构建在.NET Framework上并与SQL Server 2000集成在一起，因此您可以使用一个扩展的基于Web服务的API将丰富的报表生成功能集成到应用程序中。

尽管报表服务器使用SQL Server作为报表的储存库，但利用OLE DB、ODBC或提供程序的任何数据源都可以用来为报表提供数据，这就使得Reporting Services成为在各种企业环境中生成报表的极佳工具。

Reporting Services是作为SQL Server 2000的一部分而授权的，因此如果您有已授权的SQL Server的副本，则可以在同一服务器上运行Reporting Services而无需支付额外的许可证费用。

Create Reports Utilizing SQL Server Reporting Services and PI OLEDBLearning Lab TutorialIntroduction (3)PI OLEDB (3)SQL Server 2005 Reporting Services (SSRS) (3)Installed Software on Tutorial PC (3)Basic Report Creation Tutorial (4)Step 1 – Opening the Tutorial Project (4)Step 2 – Report Definition File (4)Step 3 – Connection to PI Server via PI OLEDB (5)Step 4 – SQL Query (6)Step 5 – Layout and Preview (6)Step 6 – Deploying Reports to IIS (7)Reporting Models/Scenarios (8)Portal Integration (8)Embedded Reporting (8)Internet Reporting (9)Conclusion (9)IntroductionThe goal of this document is to learn how to create a simple report using the PI OLEDB provider in the MS SQL Server 2005 Reporting Services environment (SSRS).PI OLEDBOLEDB provider for OSIsoft‟s PI Server is the middle tier component that implements SQL for the PI Server.Other applications, capable of working with OLE DB data sources, can thus query the underlying PI data and provide it to end users in the …standardized form‟. One of these applications is the Microsoft‟s Reporting Services, which is part of the SQL Server 2005:SQL Server 2005 Reporting Services (SSRS)SQL Server 2005 Reporting Services is a server-based reporting platform that can be used to create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources. The reports can be viewed and managed over a World Wide Web-based connection. Reporting Services includes the following core components:∙Complete set of tools that one can use to create, manage, and view reports∙Report Server (NT Service) component that hosts and processes reports in a variety of formats.These (output) formats include HTML, PDF, TIFF, Excel, CSV, and more.One of the sources of data the Reporting Services can work with are OLEDB providers. This document outlines a short tutorial describing how to create a simple report by means of the aforementioned components (SSRS and PI OLEDB). The goal is also to show the …simplicity‟ of this approach, because no programming is involved to createa report. In addition, the available report deployment models/schemas are shortly discussed at the end of thisdocument.Reporting Services detailed help can be found in MS SQL Server Books Online available at/sql/default.mspxInstalled Software on Tutorial PCWindows XP ProfessionalMicrosoft MDAC 2.7 SP1PI SDK 1.3.3.304PI OLEDB 3.1.0.0PI Server 3.4.370.54MS SQL Server 2005 with Reporting Services (can be on a separate PC)The following paragraphs describe how to create the SSRS report in the Microsoft Visual Studio 2005:Basic Report Creation TutorialThis tutorial will guide you through report project creation, set up connection information, define a query, adda table, and preview the report:Step 1 – Opening the Tutorial Project1.Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click Business IntelligenceDevelopment Studio.2.On the File menu, point to New, and then click Project.3.In the Project Types list, click Business Intelligence Projects.4.In the Templates list, click Report Server Project.5.In Name, type Tutorial1.6.Click OK to create the project.The Tutorial1 solution is displayed in Solution Explorer.Step 2 – Report Definition File1.In Solution Explorer, right-click Reports, point to Add, and click New Item.2.In Add New Item, click Report.3.In Name, type Visited URLs.rdl and then click Add. A view opens that contains Data, Layout, and Previewtabs. This is the Report Designer component of Business Intelligence Development Studio. The report opens in Data view.Figure 1. Report Definition File in VS 2005Step 3 – Connection to PI Server via PI OLEDB1.In the Data tab, expand the contents of the Dataset drop-down list at the top of the page, and select NewDataset. The Data Source dialog box appears.2.In Name, type PI OLEDB.3.In Type, select OLE DB.4.Press the Edit button and fill in the PI OLEDB connection properties. See the PI OLEDB manual for details.5.In the Credentials tab explicitly specify login account (otherwise you may get errors when running the report).Figure 2. OLE DB Connection StringAfter the data source is defined, Report Designer creates a dataset (we named it PI OLEDB) and displays the generic query designer, which you can use to design the query. For this tutorial, we will create a query thatretrieves the times someone has visited the page.Step 4 – SQL Query1.Type the following or a similar query into the SQL pane of the generic query designer. The SQL pane is thetop pane in the design tool. In our example the tag 'pioledb001_string' contains IP addresses that representlogin times of a certain client PC. You may choose any other available tag and related query:SELECT time FROM picomp2 WHEREtime >'*-7d' AND tag LIKE 'pioledb001_string' ANDCAST(value AS string) = '192.168.100.5' GROUP BY time2.To view the results of the query, click the Run (!) button on the query designer toolbar.Step 5 – Layout and Preview1.Click the Layout tab and go through the dialogs that allow for specifying the format of your report. See SQLServer Books OnLine for more details.2.Save the report project. On the File menu, click Save All.3.Click the Preview tab. Report Designer runs the report and displays it in Preview view.Figure 3. Final Report in a Web BrowserStep 6 – Deploying Reports to IISThe final report has to be deployed onto the given location so that the IIS knows how to address it. A standard deployment model consists of a single report server instance that uses a local or remote SQL Server database engine to host the report server database. The following diagram shows the standard deployment model where the report server database is located on a remote server. The report database and the report server components can also be installed locally, so that the involved server components are on the same computer.Figure 4. Reporting Services StructureReporting Models/ScenariosUsers who work with business data often require the ability to create and refine reports on an ad-hoc basis.Reporting Services includes the Report Builder component to achieve this (more info about this area can be again located in SQL Server 2005 Books OnLine). There are a couple of other possibilities how users can access reports.At this place we will shortly summarize the various approaches:Portal IntegrationReports can be integrated into the web portal solution - MS SharePoint Portal Server. They can be …hosted‟in a portal page via a SharePoint web part:Figure 5. MS SharePoint Portal Server Reporting Web PartMoreover, in the portal environment it is possible to create a report that mimics a dashboard-style Web application by embedding multiple reports, charts, and data-driven images into a single free-form report layout.Embedded ReportingIn general, there are three options how to implement Reporting Services into the custom application: URL Access– direct and most efficient way to integrate the reports into the application.The report can be addressed directly via url; e.g.: http://myserver/reportserver/myreport.Report Server Web Service –provides additional capabilities of managing a content of the Report Server througha set of Web methods.ReportViewer controls for Visual Studio 2005 - In addition to URL access and the Report Server Web Service, Microsoft Visual Studio 2005 contains a set of freely distributable report viewer controls that make it easy to embed Reporting Services functionality into custom applications.Figure 6. Reporting Services Integration ScenariosInternet ReportingOne can make reports available outside the intranet by deploying a report server on an Internet-facing Web server. Such an Internet report deployment usually requires that a custom security extension to support forms-based authentication is in place.ConclusionAs outlined above, business users can use Report Manager, SharePoint Web parts, or a Web browser to view reports on demand, or subscribe to reports that are delivered through e-mail or to a file share. The number of possible scenarios is immense. In general we can say that whenever users need to incorporate tabular or chart data into a line-of-business application, it …pays off‟ to think about a reporting tool that keeps reports on a central server in a maintainable form. The SSRS environment is …one of the answers‟ to this problem. It is a very efficient tool that enables report creation without programming. To gether with PI OLEDB, the only “knowledge” that is needed is thus to know how to formulate the SQL query against the PI Server.。

第13章 Reporting ServicesMicrosoft SQL Server 2005 Reporting Services是一种基于服务器的解决方案，用于生成从多种关系数据源和多维数据源提取内容的企业报表，发布能以各种格式查看的报表，以及集中管理安全性和订阅。

用户创建的报表可以通过基于Web的连接进行查看，也可以作为Microsoft Windows应用程序的一部分或共享门户进行查看。

本章主要给读者讲解利用SQL Server 2005自身的功能创建报表的方法，希望读者通过本章的学习，理解Reporting Services体系架构，并熟练掌握SQL Server 2005中报表工具的使用方法，以及创建报表的方法。

13.1 Reporting Services体系架构SQL Server 2005的Reporting Services并不仅仅是一种报表设计工具。

也是一个完整的报表平台，可以创建报表、保存报表定义、提供安全的报表访问、以多种不同的输出方式呈现报表、制订报表交付的进度表、支持部署报表、并具备编程能力和扩展特性。

Reporting Services提供了运行在IIS之下的中间层报务器。

如果在执行安装程序的系统上没有IIS，则SQL Server 2005的安装对话框中不会提供Reporting services的安装选项。

虽然Reporting Services可以安装在与SQL Server数据库引擎相同的服务器系统上，但为了提供可伸缩性，通常最好Reporting Services安装到一个单独的服务器上。

Reporting Services包括一些应用程序。

它是一种基于服务器的子系统，支持在企业环境中创建、管理和部署报表。

其体系架构如图13-1所示。

•2•图13-1 Reporting Services体系架构下面对报表的各个组件进行详细阐述。

13.1.1 报表服务器报表服务器是Reporting Services的主要组件。