Create Reports Utilizing SQL Server Reporting Services and PI OLEDB. Tutorial

Full text

(1)

Create Reports

Utilizing SQL Server Reporting Services and PI OLEDB

(2)

Introduction ... 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

(3)

Page: 3

Introduction

The 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 OLEDB

OLEDB 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 create a report. In addition, the available report deployment models/schemas are shortly discussed at the end of this document.

Reporting Services detailed help can be found in MS SQL Server Books Online available at http://www.microsoft.com/sql/default.mspx

Installed Software on Tutorial PC Windows XP Professional

Microsoft MDAC 2.7 SP1 PI SDK 1.3.3.304

PI OLEDB 3.1.0.0 PI Server 3.4.370.54

MS SQL Server 2005 with Reporting Services (can be on a separate PC)

(4)

Basic Report Creation Tutorial

This tutorial will guide you through report project creation, set up connection information, define a query, add a table, and preview the report:

Step 1 – Opening the Tutorial Project

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click Business Intelligence Development 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 File

1. 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 Preview tabs. This is the Report Designer component of Business Intelligence Development Studio. The report opens in Data view.

(5)

Page: 5

Step 3 – Connection to PI Server via PI OLEDB

1. In the Data tab, expand the contents of the Dataset drop-down list at the top of the page, and select New Dataset. 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 String

After 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 that retrieves the times someone has visited the

http://techsupport.osisoft.com

page.

(6)

Step 4 – SQL Query

1. Type the following or a similar query into the SQL pane of the generic query designer. The SQL pane is the top pane in the design tool. In our example the tag 'pioledb001_string' contains IP addresses that represent login times of a certain client PC. You may choose any other available tag and related query:

SELECT time FROM picomp2 WHERE

time >'*-7d' AND tag LIKE 'pioledb001_string' AND CAST(value AS string) = '192.168.100.5' GROUP BY time

2. To view the results of the query, click the Run (!) button on the query designer toolbar. Step 5 – Layout and Preview

1. Click the Layout tab and go through the dialogs that allow for specifying the format of your report. See SQL Server Books OnLine for more details.

2. Save the report project. On the File menu, click Save All.

(7)

Page: 7

Step 6 – Deploying Reports to IIS

The 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.

(8)

Reporting Models/Scenarios

Users 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 Integration

Reports 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 Part

Moreover, 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.

(9)

Page: 9

Figure 6. Reporting Services Integration Scenarios

Internet Reporting

One 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.

Conclusion

As 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. Together with PI OLEDB, the only “knowledge” that is needed is thus to know how to formulate the SQL query against the PI Server.

Figure

Figure 2. OLE DB Connection String

Figure 2.

OLE DB Connection String p.5
Figure 4. Reporting Services Structure

Figure 4.

Reporting Services Structure p.7