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 Automation, 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
iv
FactoryTalk Historian Tags and Attributes ...................... 51
Tag Search ........................................................................ 51
Tag Functions .................................................................... 54
● 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
viii
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
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
Reference these items using FactoryTalk Historian DataLink functions
Keep values updated when the spreadsheet recalculates.
Build a trend from the spreadsheet data or selected FactoryTalk
Historian data:
FactoryTalk Historian tag names and attributes.
FactoryTalk Historian Module Database (MDB) paths, aliases and
properties.
to calculate and filter data.
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
An add-in is installed for Microsoft Excel XP or 2003. This add-in
The differences between the add-ins reside primarily in design and user
interface (page 7) changes for Office 2007 and later.
2
run in earlier versions of Excel. This edition is configured
automatically if you have Excel 2007 on your system.
resembles earlier versions of FactoryTalk Historian DataLink and
must be configured manually.
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
2. Unzip the file into a temporary directory.
4
privileges.
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
FactoryTalk Historian
DataLink for Excel XP-2003
FactoryTalk Historian 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.
Depending on your version of Excel, you may work with either of the
following groups of FactoryTalk Historian DataLink components:
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
8
ribbon menu.
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.
Choose Recalculate (Resize) Function to recalculate (page 64) the
Choose <function name> to open a corresponding function dialog box
14
You should select an array before attempting to copy, cut or drag it to
a new spreadsheet location.
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.
(page 13) and update arguments to collect different data.
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
Select a FactoryTalk Historian server in the server pane to access
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
Historian server in the list. Clear a check box to close a connection.
connection settings.
An open connection to a FactoryTalk Historian server is required to add
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.
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
Some entry fields display a default entry when the dialog box or pane opens.
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
The percentage of good values over a sampling interval.
Value attributes.
Manually entered event annotations.
Source FactoryTalk Historian server names.
22
values.
Loading...