Remote Automation Solutions OpenEnterprise Reporting Overview User guide

Reference Guide

D5092

11-Dec-2007

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

Bristol OpenEnterprise Reference Guide

Reporting Overview

Reference Guide

D5092

11-Dec-2007 - Page 2

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

Contents

1 OpenEnterprise Report Suite Overview ................................................................................ 6

1.1 Report Examples.................................................................................................................6

1.2 The Report Plug-in ..............................................................................................................6

1.3 The Report Configuration Tool............................................................................................ 6

1.4 The Report Scheduler ......................................................................................................... 7

2 A Simple Report ....................................................................................................................... 9

2.1 Open the Report Configuration Tool ................................................................................... 9

2.2 Report Configuration Tool Interface.................................................................................. 10

2.3 Create a new Report Template......................................................................................... 11

2.3.1 Manual Report Template Creation ............................................................................. 12

2.3.2 General Configuration Page ....................................................................................... 12

2.3.3 Report Properties Data Service.................................................................................. 12

2.3.4 Report Properties Report Period ................................................................................ 13

2.3.5 Report Properties Event Logging ............................................................................... 14

2.3.6 Create a Query ........................................................................................................... 15

2.3.6.1 New Query from Context Menu ........................................................................... 16

2.3.6.2 New Query from Menu Bar .................................................................................. 16

2.3.6.3 New Query from Query Manager......................................................................... 17

2.3.6.4 The Query Wizard ................................................................................................ 18

2.3.6.4.1 Query Name Page .......................................................................................... 18

2.3.6.4.2 Query Dataservice Page ................................................................................ 19

2.3.6.4.3 Query Dataset Page ....................................................................................... 20

2.3.6.4.4 Query Signals Page........................................................................................ 21

2.3.6.4.4.1 Signal Browse Dialog ............................................................................... 22

2.3.6.4.5 Query Period Page ......................................................................................... 24

2.3.6.4.5.1 Verifying the Report Period ...................................................................... 24

2.3.6.4.6 Query Transform Page ................................................................................... 25

2.3.6.4.7 Query Output Page......................................................................................... 26

2.3.7 Final Touches - Renaming Sheets ............................................................................. 27

2.3.7.1 Change the Sheet name ...................................................................................... 27

2.3.7.2 Delete the other sheets ........................................................................................ 30

2.3.7.3 Make the headings bold....................................................................................... 30

2.3.8 Test Run the Query .................................................................................................... 30

2.3.8.1 Test Run the Query.............................................................................................. 30

2.3.8.2 The Simple Report Output ................................................................................... 31

2.4 Create the Report.............................................................................................................. 32

2.4.1 Report Configuration General Page ........................................................................... 33

2.4.2 Alias Values Page ...................................................................................................... 34

2.4.3 Publishing Page.......................................................................................................... 35

2.4.4 Formats Page ............................................................................................................. 36

2.4.5 Schedules Page ......................................................................................................... 37

2.4.6 Credentials Page ........................................................................................................ 38

Reference Guide

D5092

11-Dec-2007 - Page 3

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

2.4.7

Alarm Conditions Page............................................................................................... 39

2.4.8 Creating a Schedule ................................................................................................... 40

2.4.8.1 Schedule Wizard General Page........................................................................... 41

2.4.8.2 Schedule Type Properties.................................................................................... 42

2.4.8.3 Schedule Associated Reports.............................................................................. 42

2.4.8.4 Alarm Conditions.................................................................................................. 43

3 Advanced Reporting Techniques......................................................................................... 45

3.1 Advanced Report Example ............................................................................................... 45

3.2 The Report Template ........................................................................................................ 46

3.2.1 Advanced - Create the Report Template.................................................................... 46

3.2.2 Advanced - Configure First Query .............................................................................. 46

3.2.3 Advanced - Configure Second Query......................................................................... 48

3.2.4 Initial Preparation of Output Sheet ............................................................................. 50

3.2.4.1 Change Sheet Name............................................................................................ 50

3.2.4.2 Report Title........................................................................................................... 50

3.2.4.3 From and To Values - Inserting Functions........................................................... 51

3.2.4.4 Add the Labels ..................................................................................................... 51

3.2.4.5 Add the Function .................................................................................................. 52

3.2.4.6 Column Headings................................................................................................. 53

3.2.4.7 Report Borders..................................................................................................... 54

3.2.5 Automatic Data Array Naming .................................................................................... 56

3.2.5.1 Example Named Array......................................................................................... 57

3.2.6 Using Array Formulas................................................................................................. 58

3.2.6.1 Get the Data Array Name .................................................................................... 58

3.2.6.1.1 Select the Data Array...................................................................................... 58

3.2.6.1.2 Copy the Data Array Name ............................................................................ 59

3.2.6.2 Select the Target Cell Array................................................................................. 59

3.2.6.3 Build the Array Function....................................................................................... 60

3.2.6.3.1 Get the Source Data Array Name................................................................... 61

3.2.6.3.2 Select Target Cell Range ............................................................................... 62

3.2.6.3.3 Build the Array Function ................................................................................. 62

3.3 The Report Schedule ........................................................................................................ 63

3.3.1 Open the Report Configuration Tool .......................................................................... 63

3.3.2 Create a new Schedule .............................................................................................. 63

3.3.3 Schedule Configuration General Settings .................................................................. 64

3.3.4 Timed Schedule Settings............................................................................................ 65

3.3.5 Associated Reports .................................................................................................... 66

3.3.6 Alarm Conditions ........................................................................................................ 67

3.4 The Report Format............................................................................................................ 68

3.4.1 Open the Report Configuration Tool .......................................................................... 69

3.4.2 Create a new Format.................................................................................................. 69

3.4.3 Format General Settings ............................................................................................ 70

3.4.4 Format Purging........................................................................................................... 71

3.4.5 Recipient Associations ............................................................................................... 71

3.4.6 Alarm Conditions ........................................................................................................ 72

Reference Guide

D5092

11-Dec-2007 - Page 4

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

3.5

The Report ........................................................................................................................ 73

3.5.1 Open the Report Configuration Tool .......................................................................... 74

3.5.2 Create a new Report .................................................................................................. 74

3.5.3 Report General Page.................................................................................................. 75

3.5.4 Report Alias Values .................................................................................................... 75

3.5.5 Report Publishing ....................................................................................................... 76

3.5.6 Report Formats........................................................................................................... 77

3.5.7 Report Schedules ....................................................................................................... 78

3.5.8 Report Credentials...................................................................................................... 79

3.5.9 Report Alarm Conditions ............................................................................................ 80

4 Real World Example Report ................................................................................................. 82

4.1 Raw Water Pumpage ........................................................................................................ 82

4.2 Treated Water Pumpage................................................................................................... 82

4.3 Raw Water Turbidity.......................................................................................................... 83

4.4 Raw Water Alkalinity ......................................................................................................... 83

4.5 Settled Water Turbidity...................................................................................................... 83

4.6 Finished Water Turbidity ................................................................................................... 83

4.7 Monthly Pumpage Totals .................................................................................................. 83

4.8 Turbidity Historical Stream ................................................................................................ 83

4.8.1 Raw Pumpage ............................................................................................................ 84

4.8.2 Treated Pumpage ....................................................................................................... 84

4.8.3 Raw Turbidity.............................................................................................................. 84

4.8.4 Raw Alkalinity ............................................................................................................. 84

4.8.5 Settled Turbidity.......................................................................................................... 84

4.8.6 Finished Turbidity ....................................................................................................... 84

4.8.7 Create the Stream ...................................................................................................... 84

4.8.7.1 Select the Columns and Define the Compressed Values.................................... 86

4.8.7.2 Define the Raw Dataset ....................................................................................... 87

4.8.7.3 Define the Compressed Dataset.......................................................................... 88

4.8.7.4 Select the Signals ................................................................................................ 89

4.9 Turbidity Report Template................................................................................................. 90

4.9.1 Change the First Sheet Name.................................................................................... 92

4.9.2 Configure the Turbidity Query .................................................................................... 93

4.9.3 Turbidity Name, Dataservice and Dataset.................................................................. 93

4.9.4 Turbidity Objects......................................................................................................... 94

4.9.5 Turbidity Query Period................................................................................................ 94

4.9.6 Turbidity Data Transform............................................................................................ 95

4.9.7 Turbidity Query Output ............................................................................................... 96

4.9.8 Prepare the Turbidity Output Sheet............................................................................ 96

4.9.8.1 The Turbidity Report Title .................................................................................... 97

4.9.8.2 Automating the Month and Year .......................................................................... 98

4.9.8.3 Block Borders..................................................................................................... 102

4.9.8.4 Finished Turbidity Modifications......................................................................... 104

4.9.9 Mapping the Turbidity Arrays.................................................................................... 106

4.9.9.1 Getting the Source Array ................................................................................... 106

Reference Guide

D5092

11-Dec-2007 - Page 5

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

4.9.9.2

Defining the Target Array................................................................................... 108

4.9.9.3 Including IF Formulas......................................................................................... 108

4.9.9.3.1 Average Without an IF Formula.................................................................... 108

4.9.9.3.2 Creating and Including the IF Formula ......................................................... 109

4.9.9.3.3 Avarage after Including the IF Formula ........................................................ 109

4.9.10 The Finished Turbidity Report Template............................................................... 110

4.10 Turbidity Report Schedule ........................................................................................... 111

4.10.1 Turbidity Schedule Name and Type...................................................................... 112

4.10.2 Turbidity Schedule Type Configuration................................................................. 113

4.10.3 Turbidity Schedule Associated Report.................................................................. 113

4.11 Turbidity Report Format............................................................................................... 114

4.11.1 Turbidity Format Name, Type and Destination ..................................................... 115

4.12 Turbidity Report ........................................................................................................... 116

4.12.1 Turbidity Report General Page ............................................................................. 117

4.12.2 Turbidity Report Publishing Page ......................................................................... 118

4.12.3 Turbidity Report Formats Page ............................................................................. 118

4.12.4 Turbidity Report Schedules Page ......................................................................... 119

5 Index ..................................................................................................................................... 121

Reference Guide

D5092

11-Dec-2007 - Page 6

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

1 OpenEnterprise Report Suite Overview

The Report Suite enables the creation, scheduling, publication and distribution of reports. The three

components of the OpenEnterprise Report Suite are: -

1. The Report Plug-in

2. The Report Configuration Tool

3. The Report Scheduler

1.1 Report Examples

To help you in learning how to use the Report Suite to configure real world reports, two example

reports are detailed here. The first is a simple example, designed to take you step by step in detail

through the process of creating a basic report. The second example shows a more advanced method

of report creation using multiple queries.

1. A Simple Report

2. Advanced Reporting Techniques

3. A Real World Report

1.2 The Report Plug-in

This is an Excel add-in that enables Report Templates to be configured by directly importing

OpenEnterprise historical data into Excel. Each historical data import is associated with a selected

cell. A Query is then constructed by means of a wizard. More than one query can be configured within

a single Report Template. The Excel file with the configured Report Plug-in Query or Queries is

saved, and becomes a Report Template. It can then be used when configuring an OpenEnterprise

Report.

The Report Plug-in can be installed on an OpenEnterprise Server as part of the Reporting Suite, but

can also be installed separately on a computer that is neither an OpenEnterprise Server or

Workstation. This enables any computer to be used to configure Report Templates.

Note: for the Report Plug-in to work, Excel 2003 (Microsoft Office 2003 Professional or standalone

Excel 2003) must be installed on the host machine.

1.3 The Report Configuration Tool

This tool enables all aspects of report configuration, including access to the Report Plug-in for

creation of Report Templates. A configured OpenEnterprise Report must have an associated

Template and Schedule, and optionally a number of Formats and Recipients.

Reference Guide

D5092

11-Dec-2007 - Page 7

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

The Template defines the data that is in the Report, the Schedule defines when the Report will be run,

the Format defines the form in which the Report is to be published, and the Recipient defines where

the published report will be delivered.

1.4 The Report Scheduler

The Report Scheduler is used to run, publish and deliver all configured Reports. Without it, properly

configured reports will not run. It should be configured to run as part of an OpenEnterprise Session,

either part of an existing OpenEnterprise Session or a standalone Report Session.

Reference Guide

D5092

11-Dec-2007 - Page 8

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

The Report Scheduler's user interface provides the administrative user with information on the report

configuration found in the database, statistics on the reports that have been run and events related to

the running of reports. This information keeps the administrative user informed of all reporting issues.

Reference Guide

D5092

11-Dec-2007 - Page 9

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

2 A Simple Report

This example shows how to configure a simple Report that will capture the previous hour's raw data

for some Tank Levels. Its main purpose is to walk you through and familiarize you with the process of

report creation. This is what the report will look like. You can use any number of analog signals from a

raw or compressed dataset in your database to create this report. In this example we are using four

signals.

There are three main steps to configuring a report.

1. Step 1 - Open the Report configuration Tool

2. Step 2 - Create and Configure the Report Template

3. Step 3 - Configure the Report

2.1 Open the Report Configuration Tool

The Report Configuration Tool resides in the OpenEnterprise Toolbox. To access the Toolbox, select

the Windows Start button and select Start>Programs>OpenEnterprise>Toolbox. Then select the

Report Configuration Tool, which is circled in the image below.

Reference Guide

D5092

11-Dec-2007 - Page 10

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

When you have created the initial Report Template return to the Report Properties Data Service

page

within this documentation.

2.2 Report Configuration Tool Interface

When the Report Configuration Tool first loads, it displays this Welcome page, which provides an

initial example of how to create a Report using the Report Configuration tool, but contains no detailed

information on the process of creating a Report Template. You can stop this Welcome message

appearing the next time you open the Report Configuration Tool by un-checking the Show this form

on startup box. Then click the Close button.

When the Welcome page has closed, you will see the Report Configuration Tool's main user

interface:-

Reference Guide

D5092

11-Dec-2007 - Page 11

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

Note the tree view in the left pain has a list of empty headings underneath the main Report

Configuration object. We need to create a new Report, but if you right click on The Configured

Reports object, and select New Report, you will get this message:-

So, we first need to create a new Report Template object.

2.3 Create a new Report Template

Since we have no existing Report configuration, the first thing we need to do is create a Report

Template.

Select the Template icon and right click on the mouse. Then select the New Report Template option

from the context menu as shown below. This will open the General Template Configuration page.

Alternatively, you can click the Edit menu and select the New Template option.

Reference Guide

D5092

11-Dec-2007 - Page 12

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

2.3.1 Manual Report Template Creation

A new Report Template can also be created manually. Instructions for creating a Report Template

manually are given in the Report Plugin documentation

.

2.3.2 General Configuration Page

If you are using the Report Configuration tool to configure a new Report Template, the General

Template Configuration page is shown. Type the new template's name into the Name: field. The

Create button will be enabled. Click the Create... button, circled in the image below. This will launch a

new instance of Microsoft Excel as a new OpenEnterprise Report Template.

2.3.3 Report Properties Data Service

When a new Report Template opens for the first time, a Report Properties wizard prompts for some

global Report properties. The first Report property required is the Data Service of the OpenEnterprise

Server database to import data from. The default setting is rtrdb1. Change this value so that you can

connect to your own database. You can test the connection to the data service by clicking the Test

Connection button. For the purposes of this example, this is the correct data service, so leaving this

setting as it is, we move to the next page by selecting the Next>

button.

Reference Guide

D5092

11-Dec-2007 - Page 13

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

2.3.4 Report Properties Report Period

The next page is the Report Period page, which defines a default query period for the Report. The

default query period is Today. Our query period is for the previous hour, so click on the drop-down

Calendar Period list and select the Previous Hour option. Then move to the next page by selecting

the Next>

button.

Reference Guide

D5092

11-Dec-2007 - Page 14

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

2.3.5 Report Properties Event Logging

This page enables us to determine if and how we want our Report Template to log events. Event

logging is useful for troubleshooting when creating and running a Report. The default is for Event

Logging to be enabled, using the same name as the Report Template, a new Log file being created in

the same directory as the actual Template each time it is opened. This is fine, so we will leave the

default settings as they are. Click the Finish

button to finalize the global Report properties and close

the Report Properties wizard.

Reference Guide

D5092

11-Dec-2007 - Page 15

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

2.3.6 Create a Query

Once the Report properties have been configured, you will be returned to the open Report Template.

Reference Guide

D5092

11-Dec-2007 - Page 16

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

From here you can begin creating a query to import OpenEnterprise historical data by right clicking on

a cell and selecting the OpenEnterprise>Import Signal History

option. The Query wizard will begin,

which enables you to create a query for the Report Template.

You could also use the OpenEnterprise menu on the Excel menu bar

, or the Query Manager to

import the OpenEnterprise historical data.

2.3.6.1 New Query from Context Menu

Select a cell where you wan to run the query from (usually the top left cell). Then locate the

OpenEnterprise option at the bottom of the context menu, and select OpenEnterprise>Import

Signal History>New Query, as shown in the image below.

2.3.6.2 New Query from Menu Bar

Select OpenEnterprise>Import Signal History>New Query from the Excel menu bar as shown in

the image below.

Reference Guide

D5092

11-Dec-2007 - Page 17

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

2.3.6.3 New Query from Query Manager

Select OpenEnterprise>Query Manager from the Excel menu bar as shown in the image below.

Then select the Add button from the Query Manager dialog, as shown here.

Reference Guide

D5092

11-Dec-2007 - Page 18

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

2.3.6.4 The Query Wizard

The Query Wizard enables you to create a new query for the Report Template. In our test database,

we have four tanks with level signals, which are ramping up and down fairly quickly. We have

configured a dataset for these tank levels which takes a raw data sample every minute. This simple

query is going to display all level values logged by the Historian for the four tanks during the previous

hour.

There are seven pages of the Query Wizard. As you configure each page, select the Next button to

move to the next page. The pages are listed down the left hand side. Each time you move on, the

next page is highlighted and the previous page name is italicized. The seven pages are:-

1. The Query Name page.

2. The Dataservice page.

3. The Dataset page

4. The Signals page.

5. The Query Period page.

6. The Transform page.

7. The Output and Data Formatting page.

2.3.6.4.1 Query Name Page

This is the first of the Query configuration pages. It enables you to give the new Query a name. The

name of each query defaults to Query<n>, where <n> is a unique serial number automatically given to

each query as it is configured within the Report Template. This name can be changed if required by

entering a new name. We will leave the default Query name in there. Click the Next button to go to

the Query configuration Dataservice page

.

Reference Guide

D5092

11-Dec-2007 - Page 19

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

2.3.6.4.2 Query Dataservice Page

The Query configuration Dataservice page enables you to select the default data service or enter a

different service. We will also leave this as it is, since we are actually connecting to the default Report

DataService. Click the Next button to go to the Query Dataset Page

.

Reference Guide

D5092

11-Dec-2007 - Page 20

Reporting Overview

Remote Automation Solutions

Website: www.EmersonProcess.com/Remote

2.3.6.4.3 Query Dataset Page

This is where we select the actual dataset that we want to query. The major historical groups are

shown next to a plus sign on the left of the list. In the example below they are listed as "control id =

<ControlID>". These represent the historical 'streams' that have been configured in our database.

Page is loading ...

Remote Automation Solutions OpenEnterprise Reporting Overview User guide

Remote Automation Solutions OpenEnterprise Reporting Overview User guide

Related papers

Other documents