Rockwell Automation FactoryTalk Historian SE DataLink 4.2 User Manual

DATALINK USER GUIDE

PUBLICATION HSEDL-UM024A-EN-E–June 2012

Supersedes Publication HSEDL-UM023A-EN-E

●

●

● Copyright

●

●

Contact Rockwell Automation

Customer Support Telephone — 1.440.646.3434

Online Support — http://www.rockwellautomation.com/support

Copyright Notice

©2012 Rockwell Automation Technologies, Inc. All rights reserved. Printed in USA.

©2010 OSIsoft, Inc. All rights reserved.

This document and any accompanying Rockwell Software products are copyrighted by Rockwell Automation Technologies, Inc. Any reproduction and/or distribution without prior written consent from Rockwell Automation Technologies, Inc. is strictly prohibited. Please refer to the license agreement for details.

Trademark Notices

FactoryTalk, Rockwell Automation, Rockwell Software, the Rockwell Software logo are registered trademarks of Rockwell Automat ion, Inc.

The following logos and products are trademarks of Rockwell Automation, Inc.:

FactoryTalk Historian Site Edition (SE), FactoryTalk Historian Machine Edition (ME), RSView, FactoryTalk View, RSView Studio, FactoryTalk ViewStudio, RSView Machine Edition, RSView ME Station, RSLinx Enterprise, FactoryTalk Services Platform, FactoryTalk Live Data, and FactoryTalk VantagePoint.

The following logos and products are trademarks of OSIsoft, Inc.:

PI System, Sequencia, Sigmafine, gRecipe, sRecipe, and RLINK.

Other Trademarks

ActiveX, Microsoft, Microsoft Access, SQL Server, Visual Basic, Visual C++, Visual SourceSafe, Windows, Windows ME, Windows NT, Windows 2000, Windows Server 2003, and Windows XP are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Adobe, Acrobat, and Reader are either registered trademarks or trademarks of Adobe Systems Incorporated in the United States and/or other countries.

ControlNet is a registered trademark of ControlNet International.

DeviceNet is a trademark of the Open DeviceNet Vendor Association, Inc. (ODVA).

Ethernet is a registered trademark of Digital Equipment Corporation, Intel, and Xerox Corporation.

OLE for Process Control (OPC) is a registered trademark of the OPC Foundation.

Oracle, SQL*Net, and SQL*Plus are registered trademarks of Oracle Corporation.

All other trademarks are the property of their respective holders and are hereby acknowledged.

Restricted Rights Legend

Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of the Rights in Technical Data and Computer Software clause at DFARS 252.227-7013.

Warranty

This product is warranted in accordance with the product license. The product’s performance may be affected by system configuration, the application being performed, operator control, maintenance, and other related factors. Rockwell Automation is not responsible for these intervening factors. The instructions in this document do not cover all the details or variations in the equipment, procedure, or process described, nor do they provide directions for meeting every possible contingency during installation, operation, or maintenance.

This product’s implementation may vary among users.

This document is current as of the time of release of the product; however, the accompanying software may have changed since the release. Rockwell Automation, Inc. reserves the right to change any information contained in this document or the software at anytime without prior notice. It is your responsibility to obtain the most current information available from Rockwell when installing or using this product.

ii

Contents

Introduction ............................................................	1
About this Document ............................................................	2
FactoryTalk Historian DataLink Editions .....................................	2
PI SDK...........................................................................	3
FactoryTalk Historian DataLink Language Support .....................	3
System Requirements............................................................	4
Installation and Upgrade........................................................	4
Installation to a Local PC ...................................................	4
Upgrade ........................................................................	5
Configuration .................................................................	5
Microsoft Excel 2007 .....................................................	5
Microsoft Excel 2010 .....................................................	6
Basics ....................................................................	7
User Interface.....................................................................	7
FactoryTalk Historian DataLink for Excel 2007 .........................	8
PI Ribbon...................................................................	8
Function Task Panes .....................................................	9
FactoryTalk Historian DataLink for Excel XP-2003 ....................	10
PI Menu....................................................................	11
Function Dialog Boxes ..................................................	13
Context Menu ................................................................	14
FactoryTalk Historian Server Connections ..................................	15
Graphic Function Components ................................................	17
	iii

●

●

● FactoryTalk Historian DataLink User Guide

●

●

Entry Fields...................................................................	17
Standard Arguments ........................................................	18
Tagname(s) or Expression..............................................	19
Start Time ................................................................	19
End Time ..................................................................	20
PI Server ..................................................................	20
Output Cell ...............................................................	20
Time Arguments .............................................................	21
Appended Data ..............................................................	22
Appended Data Array ...................................................	23
Show Timestamps .......................................................	24
Interval Timestamps ....................................................	24
Show Percent Good .....................................................	25
Show Value Attributes ..................................................	25
Show Annotations .......................................................	26
Orientation Controls ........................................................	26
Display Formats..............................................................	27
Preference Settings .............................................................	29
Spreadsheet Construction .....................................................	31
FactoryTalk Historian DataLink Functions.......................	33
Current Value ....................................................................	33
Archive Value ....................................................................	34
Compressed Data ................................................................	36
Sampled Data ....................................................................	39
Timed Data .......................................................................	41
Calculated Data .................................................................	43
Time Filtered ....................................................................	48
FactoryTalk Historian Tags and Attributes ......................	51
Tag Search........................................................................	51
Tag Functions ....................................................................	54

iv

		●
		●
	● Contents
		●
		●
		●
Point ID to Tag ...............................................................		54
Attribute Mask to Tag ......................................................		55
Tag Attributes ...............................................................		56
Module Database Objects ...........................................		57
Module Database Browse ......................................................		58
Module Database Functions ...................................................		60
Alias to Tag...................................................................		61
Property to Value ...........................................................		61
Spreadsheets ..........................................................		63
Calculation Frequency..........................................................		63
Automatic Update...........................................................		63
Triggered Recalculation....................................................		64
Manual Recalculation .......................................................		65
Functions and Array Values....................................................		66
Array Management ..............................................................		67
Update Arguments ..........................................................		68
Resize Arrays .................................................................		69
Share Spreadsheets .............................................................		70
Trend Displays ........................................................		73
Create a Trend...................................................................		74
Worksheet Data..............................................................		75
FactoryTalk Historian Data ................................................		76
Manage Traces ...............................................................		77
Specify the Time Range ........................................................		78
Title and Placement ............................................................		79
Context Menu ....................................................................		80
Supplementary Information ........................................		83
Setup ..............................................................................		83

v

●

●

● FactoryTalk Historian DataLink User Guide

●

●

High Availability Connection Preferences...............................	84
FactoryTalk Historian Server Connectivity..............................	84
Firewall Database .......................................................	84
FactoryTalk Historian Trusts ..........................................	85
Point Access Permissions...............................................	85
FactoryTalk Historian Time....................................................	86
FactoryTalk Historian Time Abbreviations ..........................	86
FactoryTalk Historian Time Expressions.............................	87
FactoryTalk Historian Time String Examples .......................	88
FactoryTalk Historian Data Type Support ...................................	89
FactoryTalk Historian Expressions............................................	90
Syntax .........................................................................	91
Operators .....................................................................	91
Performance Equation Functions .........................................	93
Math Functions...........................................................	94
Aggregate Functions ....................................................	95
Miscellaneous Functions................................................	95
FactoryTalk Historian Archive Retrieval.............................	96
FactoryTalk Historian Archive Search................................	96
FactoryTalk Historian Archive Statistics ............................	97
Point Attributes..........................................................	97
Time Functions ..........................................................	98
Dynamic Response.......................................................	99
Alarm Status Functions .................................................	99
String Functions .........................................................	99
String Conversion ......................................................	100
FactoryTalk Historian Expression Examples ..........................	101
Manual Function Entry........................................................	101
Use Array Functions.......................................................	102
Mathematical Functions..................................................	102
String Arguments ..........................................................	103
Cell References ............................................................	103
PIServer Argument ........................................................	103
Outcodes....................................................................	104
Write Data to FactoryTalk Historian .......................................	105

vi

		●
		●
	● Contents
		●
		●
		●
Troubleshooting ...............................................................		106
FactoryTalk Historian Menu Not Available ............................		106
Array and Cell Limits .....................................................		107
Row Limitations ...........................................................		107
Security .....................................................................		108
FactoryTalk Historian Security..........................................		108
FactoryTalk Historian DataLink Function Reference	........ 111
Single Value Functions .......................................................		111
PICurrVal()..................................................................		111
PIArcVal() ...................................................................		112
PIExpVal()...................................................................		113
Multiple Value Functions.....................................................		114
PINCompDat() ..............................................................		114
PINCompFilDat() ...........................................................		115
PICompDat()................................................................		116
PICompFilDat().............................................................		117
PISampDat() ................................................................		118
PISampFilDat() .............................................................		119
PIExpDat() ..................................................................		121
PITimeDat() ................................................................		122
PITimeExpDat() ............................................................		123
Calculation Functions.........................................................		124

vii

●

●

● FactoryTalk Historian DataLink User Guide

●

●

PICalcVal()..................................................................	124
PIAdvCalcVal() .............................................................	126
PIAdvCalcFilVal() ..........................................................	127
PIAdvCalcExpVal() .........................................................	129
PIAdvCalcExpFilVal()......................................................	130
PICalcDat() .................................................................	132
PIAdvCalcDat() .............................................................	133
PIAdvCalcFilDat() ..........................................................	134
PIAdvCalcExpDat() ........................................................	136
PIAdvCalcExpFilDat() .....................................................	137
PITimeFilter() ..............................................................	139
PITimeFilterVal() ..........................................................	140
Tag Functions ..................................................................	141
PIPointIDToTag() ..........................................................	141
PIAttributeMaskToTag () .................................................	142
PITagAtt()...................................................................	143
Module Database Functions .................................................	144
PIAliasToTag()..............................................................	144
PIPropertyToValue() ......................................................	145
Input Functions ................................................................	146
PIPutVal() ...................................................................	146
Technical Support and Resources ...............................	149
Technical Support .............................................................	149
Knowledgebase ............................................................	149
Worldwide Support........................................................	150
Training Programs .........................................................	150
Consulting Services .......................................................	150
TechConnect Support.....................................................	150
Find the Version and Build Numbers ...................................	151
View Computer Platform Information .................................	152
Index ..................................................................	153

viii

Chapter 1

Introduction

FactoryTalk Historian DataLink is a Microsoft Excel add-in that enables you to retrieve information from your FactoryTalk Historian server directly into a spreadsheet. With FactoryTalk Historian DataLink, you can:

Retrieve point values from a FactoryTalk Historian server.

Retrieve system metadata to create a structured view of FactoryTalk Historian data:

FactoryTalk Historian tag names and attributes.

FactoryTalk Historian Module Database (MDB) paths, aliases and properties.

Reference these items using FactoryTalk Historian DataLink functions to calculate and filter data.

Keep values updated when the spreadsheet recalculates.

Build a trend from the spreadsheet data or selected FactoryTalk Historian points.

FactoryTalk Historian DataLink provides a graphical interface to retrieve data and build functions and calculations. DataLink functions are embedded in spreadsheet cells and can provide active updates of real-time data from the FactoryTalk Historian server.

You can also use the rich calculation and formatting capabilities of Excel to organize and present FactoryTalk Historian system data to fit your purpose or audience.

1

●

●

● FactoryTalk Historian DataLink User Guide

●

●

Combined with the computational, graphic and formatting capabilities of Microsoft Excel, FactoryTalk Historian DataLink offers powerful tools for gathering, monitoring, analyzing, and reporting FactoryTalk Historian data.

About this Document

The FactoryTalk Historian DataLink Online Help provides a detailed description of product features and a complete reference to FactoryTalk Historian DataLink functions. The content of the print guide and the online help is identical. Trend control objects also launch a separate help file which includes information on programmatic controls for trends.

Specific references to FactoryTalk Historian DataLink for Excel XP-2003 are labeled (2003), and those specific to FactoryTalk Historian DataLink for Excel 2007 are labeled (2007), when necessary.

FactoryTalk Historian DataLink Editions

Throughout this book, references to Microsoft Excel 2007 apply to Microsoft Excel 2007 and later. References to Microsoft Office 2007 apply to Microsoft Office 2007 and later.

Two editions of FactoryTalk Historian DataLink are installed on your system:

An add-in is installed for Microsoft Excel 2007. This add-in cannot run in earlier versions of Excel. This edition is configured automatically if you have Excel 2007 on your system.

An add-in is installed for Microsoft Excel XP or 2003. This add-in resembles earlier versions of FactoryTalk Historian DataLink and must be configured manually.

The differences between the add-ins reside primarily in design and user interface (page 7) changes for Office 2007 and later.

2

			●
	1	● Introduction	●
			●

●

●

PI SDK

PI SDK is installed with FactoryTalk Historian DataLink, and connects FactoryTalk Historian DataLink to your FactoryTalk Historian server to retrieve FactoryTalk Historian point data. FactoryTalk Historian is based on OSIsoft PI and uses PI SDK. FactoryTalk Historian DataLink uses both PI SDK and the Microsoft Excel SDK. When an error occurs at the PI SDK level, FactoryTalk Historian DataLink displays the error in the cell. For example, if a function attempts to retrieve the current value of a tag that does not exist, the output cell displays a "Tag not found" message.

PI SDK also allows users with Microsoft Visual Basic for Applications (VBA) experience to develop Excel routines that call PI SDK functions. Users who plan to develop programs outside of Excel must purchase a separate PI SDK or PI API license. Contact your Rockwell Automation sales representative for more information.

FactoryTalk Historian DataLink Language Support

This release of FactoryTalk Historian DataLink provides multi-language support. The user interface of the add-in appears in the same language as the Microsoft Excel that it runs in, if the language is supported by FactoryTalk Historian DataLink. Otherwise the add-in appears in English.

To view the add-in in a different language, use the Microsoft Office language setting tool to change the language of Microsoft Excel.

To get the multi-language support, you must install the separate language pack.

3

●

●

● FactoryTalk Historian DataLink User Guide

●

●

System Requirements

For up to date system requirements, see the KB article 42682

(https://rockwellautomation.custhelp.com/app/answers/detail/a_id/42682)

at the Rockwell Automation Support Center.

Installation and Upgrade

Before installation, each user of FactoryTalk Historian DataLink must be licensed to use this product. Contact Rockwell Automation for more information.

The user may install FactoryTalk Historian DataLink from the distribution CD.

After a successful installation of FactoryTalk Historian DataLink to a network file server, other users will be able to install from the network drive to a local PC a complete standalone version of FactoryTalk Historian DataLink.

Installation of a standalone version places a copy of all the FactoryTalk Historian DataLink files onto the user's local PC.

Installation to a Local PC

In order to use FactoryTalk Historian DataLink, every user must install a complete standalone version. Installation may be performed either from the distribution CD or from the network drive.

A self-extracting zip file is supplied as the distribution kit for FactoryTalk Historian DataLink.

To install FactoryTalk Historian DataLink:

1.Log on to a user's PC with an account that has administrative privileges.

2.Unzip the file into a temporary directory.

4

			●
	1	● Introduction	●
			●

●

●

3.Run Setup.exe from the temp directory and follow the directions displayed in the installation wizard.

After the installation, you will be prompted to restart your computer.

You can view the results of the installation process by examining the setup log, fth_installer.log, saved under the following location:

C:\Program Files\Rockwell Software\FactoryTalk Historian\Installation Manager\<Name of the Historian suite>\FTHInstallerLogs\<Date and Time of the Installation>.

Upgrade

To upgrade FactoryTalk Historian DataLink to a newer version, remove the current version of the product from the computer and install the new one.

Configuration

After installation, you may need to configure the FactoryTalk Historian DataLink add-in in Microsoft Excel.

Microsoft Excel 2007

The FactoryTalk Historian DataLink setup program automatically installs the DataLink add-in for Microsoft Excel 2007 if this version is found on your system. No configuration is necessary.

Installation makes FactoryTalk Historian DataLink available to all users of the client machine. If an individual user deactivates FactoryTalk Historian DataLink or a related add-in, updates do not reactivate the add-in; it must be restored manually. To restore add-ins:

1.Click the Microsoft Office Button and select Excel Options.

5

●

●

● FactoryTalk Historian DataLink User Guide

●

●

2.Click Add-ins > Manage Disabled Items > Go and enable

PIDatalink.UI.dll.manifest.

3.Go to Add-ins > Manage Com Add-Ins > Go and enable PI DataLink.

4.Click OK.

Microsoft Excel 2010

The FactoryTalk Historian DataLink setup program automatically installs the DataLink add-in for Microsoft Excel 2010 if this version is found on your system. No configuration is necessary.

Installation makes FactoryTalk Historian DataLink available to all users of the client machine. If an individual user deactivates FactoryTalk Historian DataLink or a related add-in, updates do not reactivate the add-in; it must be restored manually. To restore add-ins:

1.Click the File tab and select Excel Options.

2.Click Add-ins > Manage Disabled Items > Go and enable

PIDatalink.UI.dll.manifest.

3.Go to Add-ins > Manage Com Add-Ins > Go and enable PI DataLink.

4.Click OK.

6

Chapter 2

Basics

The following sections introduce basic FactoryTalk Historian DataLink concepts and features. You should familiarize yourself with these sections before using FactoryTalk Historian DataLink to build a spreadsheet and retrieve FactoryTalk Historian data, particularly if you are new to FactoryTalk Historian DataLink or Office 2007.

These sections describe:

Common interface components, and how they differ between DataLink add-ins for Excel XP-2003 and Excel 2007.

Core features required to begin working with FactoryTalk Historian DataLink.

Different approaches to building a FactoryTalk Historian DataLink spreadsheet, depending on your goals, needs, and resources.

User Interface

FactoryTalk Historian DataLink is an add-in application to Microsoft Excel, and appears within the Microsoft Excel user interface in the form of menus, dialog boxes and task panes specific to FactoryTalk Historian DataLink.

While the underlying functionality of FactoryTalk Historian DataLink is the same for Excel XP-2003, and 2007, the user interface and tools used to build and manage FactoryTalk Historian DataLink spreadsheets differ slightly to reflect the design and objects available from the corresponding version of Microsoft Excel.

7

●

●

● FactoryTalk Historian DataLink User Guide

●

●

Depending on your version of Excel, you may work with either of the following groups of FactoryTalk Historian DataLink components:

FactoryTalk Historian	FactoryTalk Historian
DataLink for Excel XP-2003	DataLink for Excel 2007
PI menu.	PI ribbon.
Function dialog boxes.	Function task panes.
Right-click context menu.	Right-click context menu.
Trend Control Wizard.	Trend Control Wizard.

The following sections describes the visual and operational differences between DataLink add-ins.

FactoryTalk Historian DataLink for Excel 2007

FactoryTalk Historian DataLink for Excel 2007 includes design elements introduced in Microsoft Office 2007.

If you are upgrading from a previous version of FactoryTalk Historian DataLink, familiarize yourself with the new design elements before you begin working with FactoryTalk Historian DataLink.

PI Ribbon

The DataLink add-in for Microsoft Excel 2007 uses a ribbon menu.

The ribbon menu is a graphical menu that combines features of both a standard menu and a toolbar.

Click PI in the Excel menu bar to select the tab for the PI DataLink ribbon menu.

8

			●
	2	● Basics	●
			●

●

●

Click an item in the PI ribbon to open a corresponding task pane or dialog box.

Place the cursor over an item to display a descriptive tooltip.

Function Task Panes

FactoryTalk Historian DataLink for Microsoft Excel 2007 provides custom task panes to define FactoryTalk Historian DataLink functions.

A task pane is a moveable and dockable panel of controls that functions much like a dialog box, except that you can continue to work on a spreadsheet while a task pane is open.

FactoryTalk Historian DataLink opens a task pane when you add a function or select a cell in a function array for editing. To open a function task pane:

9

●

●

● FactoryTalk Historian DataLink User Guide

●

●

Click in the desired output cell, and then click a function on the PI ribbon to add a function.

Click a cell in an existing function array to display a corresponding task pane and edit function arguments.

Right-click a function array cell and choose the function name to manually display the task pane.

You can turn off automatic display (page 29) of task panes if you prefer.

Once a function task pane is open:

Type or change values, and then click OK to save your changes and close the task pane. Click Apply to save changes without closing the task pane.

Click the X button in the task pane title bar to close the task pane without saving changes.

Click the arrow button in the task pane title bar to detach and Move, or Resize the task pane.

You can also click the title bar to drag and dock a task pane, or place your cursor over the edge and drag to resize the pane.

Dialog boxes are used for other tasks including Tag Search, Preferences and Connections in the add-in for Excel 2007.

FactoryTalk Historian DataLink for Excel XP-2003

FactoryTalk Historian DataLink for Excel XP-2003 appears in Microsoft Excel as a standard menu and related dialog box controls.

If you have used previous releases of FactoryTalk Historian DataLink, the add-in for Excel XP-2003 maintains the same look and feel.

10

			●
	2	● Basics	●
			●

●

●

PI Menu

FactoryTalk Historian DataLink for Excel XP-2003 adds a PI menu to the Excel menu bar once the DataLink add-in is loaded. If you do not see the PI menu, you may need to configure the DataLink add-in for Excel.

11

●

●

● FactoryTalk Historian DataLink User Guide

●

●

FactoryTalk Historian DataLink for Excel XP-2003 uses a standard menu configuration:

12

			●
	2	● Basics	●
			●

●

●

Choose an item from the PI menu to open a corresponding dialog box.

Function Dialog Boxes

FactoryTalk Historian DataLink for Microsoft Excel XP-2003 provides dialog boxes to define FactoryTalk Historian DataLink functions.

FactoryTalk Historian DataLink opens a corresponding function dialog box when you add or edit a function. To open a function dialog box:

Click in a cell and then choose a function from the PI menu to add a function.

Right-click a cell in an existing function array to display the context menu, and then choose the function name from the menu to edit function arguments.

Once a function dialog box is open:

Type or change values, and then click OK to save your changes and close the dialog box.

13

●

●

● FactoryTalk Historian DataLink User Guide

●

●

FactoryTalk Historian DataLink automatically retrieves values from FactoryTalk Historian and populates the function array in your spreadsheet when you click OK.

Click Cancel or X to close the dialog box without saving changes.

Context Menu

FactoryTalk Historian DataLink adds several commands to the standard Microsoft Excel context menu. You can use these commands to manage function arrays that are already inserted into a spreadsheet.

Context menus are the same for both add-in versions of FactoryTalk Historian DataLink.

To display the context menu:

Right-click anywhere in a cell or group of cells that contains a FactoryTalk Historian DataLink function array.

A function array is the cluster of cells that contain the output from a single FactoryTalk Historian DataLink function. You can also rightclick an embedded trend control to see a related context menu (page 80).

The following items appear in the context menu:

Choose Select DataLink Function to select the entire function array.

You should select an array before attempting to copy, cut or drag it to a new spreadsheet location.

Choose Recalculate (Resize) Function to recalculate (page 64) the array, retrieving new values from FactoryTalk Historian.

For some functions, a different number of values may be available with each recalculation, causing the array to resize.

Choose <function name> to open a corresponding function dialog box (page 13) and update arguments to collect different data.

14

			●
	2	● Basics	●
			●

●

●

FactoryTalk Historian Server Connections

Use the Connections dialog box to manage connections to FactoryTalk Historian servers in your computing environment.

To connect to a FactoryTalk Historian server:

Click the Connections icon on the ribbon bar (2007), or choose PI > Connections (2003) to display the Connections dialog box.

Each configured FactoryTalk Historian server available to FactoryTalk Historian DataLink appears in the Server pane to the left.

If you do not see the desired server, you can add a connection.

To add additional FactoryTalk Historian server connections:

15

●

●

● FactoryTalk Historian DataLink User Guide

●

●

Choose Server > Add Server or right-click in the server pane and choose Add Server to display the Add Server dialog box and configure a new FactoryTalk Historian server connection. A selected check box next to a server name in the Connections dialog box indicates an open connection to the FactoryTalk Historian server.

To manage connection settings:

Click to select a check box and open a connection to a FactoryTalk Historian server in the list. Clear a check box to close a connection.

Select a FactoryTalk Historian server in the server pane to access connection settings.

An open connection to a FactoryTalk Historian server is required to add DataLink functions to a spreadsheet and retrieve values from FactoryTalk Historian. The name of the default FactoryTalk Historian server appears at the bottom right of the dialog box. You can maintain connections to more than one FactoryTalk

16

			●
	2	● Basics	●
			●

●

●

Historian server at a time, although you may need to specify the target server when you define a FactoryTalk Historian DataLink function.

Graphic Function Components

FactoryTalk Historian DataLink provides a graphical user interface to build functions in Excel. Common dialog box features make it easy to supply arguments to define the function.

The tool used to build a function depends on the version of FactoryTalk Historian DataLink installed:

The add-in for Excel XP-2003 provides dialog boxes (page 13) to define functions.

The add-in for Excel 2007 provides task panes (page 9) to define functions.

Advanced users familiar with FactoryTalk Historian DataLink and the FactoryTalk Historian server can type function syntax (page 101) directly into the Excel formula bar.

The following sections describe common features in FactoryTalk Historian DataLink function dialog boxes and task panes, how real-time data is acquired and presented, and how FactoryTalk Historian DataLink functions can be used within a spreadsheet.

Entry Fields

Labeled entry fields accept specific arguments used to define a function. Most arguments may be entered directly, or referenced in spreadsheet cells. While the options differ by argument, all entry fields allow one or more of the following choices:

Type text directly in an edit field , such as a tag name or the address of an output cell.

17

●

●

● FactoryTalk Historian DataLink User Guide

●

●

Select an item from choices in a list , such as a calculation or a sampling method.

Reference spreadsheet cells that contain arguments, such as a tag name, output cell location, or time stamp.

Be sure to type strings in cells that you may reference with a leading apostrophe ('). This forces Excel to interpret the contents as a string.

Select values from a FactoryTalk Historian server or other sources through a tag or module database search.

For example, you can type a tag name string into the Tagname field, or click the button next to the field to display the Tag Search dialog box, and search the FactoryTalk Historian server for tags.

You can also manually supply a reference to a spreadsheet cell that contains a tag name:

Click first in the edit field, and then click the cell (or click the cell and drag to an adjacent cell to select a range) on the spreadsheet.

DataLink automatically enters the cell reference into the edit field.

Some entry fields display a default entry when the dialog box or pane opens. Fields marked Optional are optional, and a value is not required.

Standard Arguments

Some arguments are common to most FactoryTalk Historian DataLink functions in dialog boxes or task panes, and are usually required to define the function:

Tagname(s) or Expression (page 19)

Start Time (page 19)

End Time (page 20)

18

			●
	2	● Basics	●
			●

●

●

PI Server (page 20)

Output Cell (page 20)

Tagname(s) or Expression

The Tagname(s) or Expression field is required by most functions to evaluate FactoryTalk Historian point (tag) data or the results of a FactoryTalk Historian expression (page 91). One or more tag names, or the syntax of a FactoryTalk Historian expression may be:

Typed directly in a field. For example, sinusoid.

Typed in one or more spreadsheet cells, which are then referenced in the field.

For example, a reference to the array Sheet1!$B$3:Sheet1!$B$4, which in turn contains the tag name strings 'sinusoid and 'cdt158.

Note that cell references allow multiple tag names to be specified for a function, whereas you cannot specify multiple tag names directly unless the field is labeled Tagname(s).

Note that single quotes are added to each tag name to denote string values in Excel. Both strings and time expressions referenced in cells should be enclosed in single quotes. For example:

TimeEq('CD:M158','y' ,'t', "Manual").

Selected in the Tag Search dialog box (page 51), based on a search for

tags by name, alias or attribute.

Start Time

The beginning of a time range (page 21) over which the function is evaluated, from Start Time to End Time. All event values corresponding to

19

●

●

● FactoryTalk Historian DataLink User Guide

●

●

the time range are retrieved from the FactoryTalk Historian archive or calculated to create the resulting function array.

End Time

The end of the time range, frequently expressed as the current time, where the Start Time is the current time minus a specified time interval.

Start and end times may be expressed in absolute or relative terms to establish a time range in the past, or up to and including the current time.

PI Server

An instance of a FactoryTalk Historian DataLink function runs against only one FactoryTalk Historian/PI server. The PI Server specifies a target FactoryTalk Historian server from a known servers list. If you do not select a server or leave the field blank, the default FactoryTalk Historian server

(page 15) is used.

Servers that appear above the dotted line in the servers list are currently connected; those below are disconnected.

Output Cell

Specify where you want to place the resulting function array using the Output Cell field. If you select a cell before the function dialog or task pane is displayed, the currently-selected cell is used as the default Output Cell value.

The output cell value always specifies the top left corner of the function array. If you append (page 22) time stamps and other data, keep in mind that the data column may be shifted downward or to the right of the output cell location, overwriting data in those adjacent cells.

If you click in the Output Cell field and then select a multi-cell array in the spreadsheet, you can override the default display of all function results.

20

			●
	2	● Basics	●
			●

●

●

Instead, only the results that fit within the specified array dimensions are displayed.

Time Arguments

Many FactoryTalk Historian DataLink functions require Start Time and End Time arguments to retrieve an array of event values over a specific time range. Follow these guidelines when specifying time arguments:

Time strings may be entered directly in edit fields in either absolute or relative FactoryTalk Historian time (page 86) formats (for example,

10-Dec-99 19:12 or -3h).

If the start time is more recent than the end time, results are displayed in reverse chronological order.

Time stamps referenced in spreadsheet cells may also use fixed or relative FactoryTalk Historian time formats, and should be preceded by an apostrophe to indicate a string (for example, '10-Dec-99 19:12 or '-3h).

Cell references may also use the absolute Excel time format (such as

39299.6146, equivalent to 8/5/2007 2:45:00 PM). Excel stores time stamps in this format, which represents the cumulative number of days since 1900. Excel can display the same time stamps using any date-time format assigned to the cell.

Some arguments call for an interval or duration of time, represented by a single value rather than start and end times. Whether referenced or entered directly, interval values should use relative FactoryTalk Historian time strings such as a number followed by a time variable (for example, 1d or 30m), and may not specify or refer to a fixed FactoryTalk Historian or Excel time stamp. For example, to specify an interval of 32 minutes, enter or reference a cell containing the string 32m.

21

●

●

● FactoryTalk Historian DataLink User Guide

●

●

To enter intervals in terms of frequency, convert the frequency to equivalent seconds. For example, a frequency of 25 Hz should be entered as a 0.04s interval (=1/25 of a second).

When using relative formats (for example, -2h), the reference time used to translate the time format is different for function start and end times. The current wall clock time is the reference for a relative start time, but the start time is then used as the reference for a relative end time.

FactoryTalk Historian DataLink supports only the default 1900 date system supported by Excel. FactoryTalk Historian DataLink does not support Excel's 1904 date system, and returns incorrect time stamps if this system is used.

Appended Data

Time stamps are particularly relevant to FactoryTalk Historian point events. Each event value is accompanied by a time stamp that gives it context.

Most FactoryTalk Historian DataLink functions can return corresponding time stamp values and other point data with FactoryTalk Historian event or calculated expression values. These data can be appended to values returned in a FactoryTalk Historian function array, and include:

Event time stamps.

Time stamps indicating start and end times of an interval.

Time stamps indicating the occurrence of minimum and maximum values.

The percentage of good values over a sampling interval.

Value attributes.

Manually entered event annotations.

Source FactoryTalk Historian server names.

22