Rockwell-automation FactoryTalk Historian SE DataLink 4.2 User Guide Uživatelský manuál Strana 1

DATALINK USER GUIDE PUBLICATION HSEDL-UM024A-EN-E–June 2012 Supersedes Publication HSEDL-UM023A-EN-E

● ● ● ● ● FactoryTalk Historian DataLink User Guide 2 Combined with the computational, graphic and formatting capabilities of Microsoft Excel, Fact

● ● ● ● ● FactoryTalk Historian DataLink User Guide 92 Type Operator Syntax Example Meaning Arithmetic + A + B Addition: A + B - A - B Subtraction:

8 ● Supplementary Information ● ● ● ● ● 93 Type Operator Syntax Example Meaning ...BN) enclosed in the parentheses. If-Then-Else Expressions if the

● ● ● ● ● FactoryTalk Historian DataLink User Guide 94 Math Functions Name Description Abs Absolute value. Asin Arc sine. Acos Arc cosine. Atn Arc

8 ● Supplementary Information ● ● ● ● ● 95 Aggregate Functions Name Description Avg Average. Max Maximum. Median Median selector. Min Minimum. PSt

● ● ● ● ● FactoryTalk Historian DataLink User Guide 96 FactoryTalk Historian Archive Retrieval Name Description NextEvent Time of a point's n

8 ● Supplementary Information ● ● ● ● ● 97 FactoryTalk Historian Archive Statistics Name Description EventCount Number of Archive events. PctGood

● ● ● ● ● FactoryTalk Historian DataLink User Guide 98 Name Description TagZero Get a point's zero value. Time Functions Name Description B

8 ● Supplementary Information ● ● ● ● ● 99 Dynamic Response Name Description Arma Dynamic response from Auto Regressive Moving Average model. Dela

● ● ● ● ● FactoryTalk Historian DataLink User Guide 100 Name Description Format Formatting of a numerical number. InStr Instance of a sub-string. L

8 ● Supplementary Information ● ● ● ● ● 101 FactoryTalk Historian Expression Examples These examples use the tag delimiters : (colon) and . (period

1 ● Introduction ● ● ● ● ● 3 PI SDK PI SDK is installed with FactoryTalk Historian DataLink, and connects FactoryTalk Historian DataLink to your Fa

● ● ● ● ● FactoryTalk Historian DataLink User Guide 102 Use Array Functions When defining array functions follow these steps:  Select an appropri

8 ● Supplementary Information ● ● ● ● ● 103 For example, the expression below finds the difference between the natural logarithm of the value of th

● ● ● ● ● FactoryTalk Historian DataLink User Guide 104 Outcodes FactoryTalk Historian DataLink function syntax includes integer outcode arguments

8 ● Supplementary Information ● ● ● ● ● 105 The formula used to calculate a corresponding outcode is: Functions have different arguments and outco

● ● ● ● ● FactoryTalk Historian DataLink User Guide 106 value from a worksheet to a FactoryTalk Historian server. The Excel macro function PIPutVal

8 ● Supplementary Information ● ● ● ● ● 107 Array and Cell Limits FactoryTalk Historian DataLink is subject to an Excel limitation on the number of

● ● ● ● ● FactoryTalk Historian DataLink User Guide 108 Security Excel security features for add-ins, ActiveX controls and macros allow you to dete

8 ● Supplementary Information ● ● ● ● ● 109 Trust for the user, domain, application or machine (per Trust configurations). 2. If all attempts to l

● ● ● ● ● FactoryTalk Historian DataLink User Guide 110

Chapter 9 111 FactoryTalk Historian DataLink Function Reference FactoryTalk Historian DataLink function dialog boxes (XP-2003) or task panes (2007

● ● ● ● ● FactoryTalk Historian DataLink User Guide 4 System Requirements For up to date system requirements, see the KB article 42682 (https://roc

● ● ● ● ● FactoryTalk Historian DataLink User Guide 112 Arguments Argument Value tagname (string) The tag name or names matching the desired Factor

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 113 Argument Value outcode (integer) An output code to determine results placement,

● ● ● ● ● FactoryTalk Historian DataLink User Guide 114 Returns Outcode Archive Value Time stamp 0 output cell 1 left column output cell 2 output

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 115 Argument Value outcode (integer) An output code. PIServer (string) The target F

● ● ● ● ● FactoryTalk Historian DataLink User Guide 116 Argument Value outcode (integer) An output code. PIServer (string) The FactoryTalk Historia

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 117 Argument Value outcode (integer) An output code. PIServer (string) The target F

● ● ● ● ● FactoryTalk Historian DataLink User Guide 118 Argument Value filtcode (integer) A filter code, either 1 to return Filtered status or 0 to

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 119 Argument Value etime (string) The end time for the range (in FactoryTalk Histor

● ● ● ● ● FactoryTalk Historian DataLink User Guide 120 Arguments Argument Value tagname(string) The tag name or names matching the desired Factory

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 121 Time stamps are shown in column1 and values in column2. A Filtered status is re

1 ● Introduction ● ● ● ● ● 5 3. Run Setup.exe from the temp directory and follow the directions displayed in the installation wizard. After the in

● ● ● ● ● FactoryTalk Historian DataLink User Guide 122 Example: The following calculates the square root of the value of the tag sinusoid from the

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 123 =PITimeDat("mytag",b1:b12,,"interpolated") PITimeExpDat()

● ● ● ● ● FactoryTalk Historian DataLink User Guide 124 Calculation Functions Calculation functions compute new values from FactoryTalk Historian p

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 125  If the outcode is 1, the following applies: Mode Column 1 Column 2 Column 3

● ● ● ● ● FactoryTalk Historian DataLink User Guide 126 Mode Column 1 Column 2 Column 3 Column 4 Count Count value Percent good Mean Mean value P

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 127 Argument Explanation mode (string) The type of calculation to be used to comput

● ● ● ● ● FactoryTalk Historian DataLink User Guide 128 Arguments Argument Explanation tagname (string) The tag name or names matching the desired

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 129 will be sampled at the compressed events of cdf144. The minpctgood is 50 percen

● ● ● ● ● FactoryTalk Historian DataLink User Guide 130 Returns See specific information for PICalcVal() outcodes (page 124). Example: The followin

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 131 Argument Explanation mode (string) The type of calculation to be used to comput

● ● ● ● ● FactoryTalk Historian DataLink User Guide 6 2. Click Add-ins > Manage Disabled Items > Go and enable PIDatalink.UI.dll.manifest. 3

● ● ● ● ● FactoryTalk Historian DataLink User Guide 132 PICalcDat() Retrieves calculated FactoryTalk Historian point values using the following syn

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 133  4 produces values and percent good along 2 columns.  5 produces time stamp

● ● ● ● ● FactoryTalk Historian DataLink User Guide 134 Argument Explanation calcbasis (string) The weighting method used to calculate values. minp

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 135 Arguments Argument Explanation tagname (string) The tag name or names matching

● ● ● ● ● FactoryTalk Historian DataLink User Guide 136 =PIAdvCalcFilDat("cdf144","y","t","3h","'

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 137 Argument Explanation PIServer (string) The target FactoryTalk Historian server.

● ● ● ● ● FactoryTalk Historian DataLink User Guide 138 Argument Explanation time format). etime (string) The end time (in FactoryTalk Historian ti

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 139 sampled at the combined compressed events of cdf144 and cdt158. The minpctgood

● ● ● ● ● FactoryTalk Historian DataLink User Guide 140 An output code (page 104) of 2 transposes the output array that results from an output code

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 141 Returns  If the output code (page 104) is 0, 1, or 2 only the calculated valu

Chapter 2 7 Basics The following sections introduce basic FactoryTalk Historian DataLink concepts and features. You should familiarize yourself wi

● ● ● ● ● FactoryTalk Historian DataLink User Guide 142 Returns Tag name. Example: The following returns the tag name of the tag with point ID of 1

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 143 An output code (page 104) of 2 transposes the output array that results from an

● ● ● ● ● FactoryTalk Historian DataLink User Guide 144 Module Database Functions PIAliasToTag() Retrieves a tag name based on a FactoryTalk Histor

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 145 PIPropertyToValue() Retrieves the value of a specified FactoryTalk Historian p

● ● ● ● ● FactoryTalk Historian DataLink User Guide 146 Input Functions PIPutVal() PIPutVal(tagname, value, time stamp, PIServer, outcell) PIPutVal

9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● ● 147 The following puts the value located in the cell A5 into the FactoryTalk Histor

● ● ● ● ● FactoryTalk Historian DataLink User Guide 148

Chapter 10 149 Technical Support and Resources Rockwell provides dedicated technical support internationally, 24 hours a day, 7 days a week. You c

● ● ● ● ● FactoryTalk Historian DataLink User Guide 150 Worldwide Support If you are not located in North America and want to contact Rockwell Aut

10 ● Technical Support and Resources ● ● ● ● ● 151 When you contact Rockwell Technical Support, please provide:  Product name, version, and/or bu

● ● ● ● ● FactoryTalk Historian DataLink User Guide 8 Depending on your version of Excel, you may work with either of the following groups of Facto

● ● ● ● ● FactoryTalk Historian DataLink User Guide 152 directory and type the subsystem name followed by the option -v (for example, piarchss.exe

Index 153 Index A About this Document • 2 Aggregate Functions • 95 Alarm Status Functions • 99 Alias to Tag • 61 Appended Data • 22 Appended Da

● ● ● ● ● FactoryTalk Historian DataLink User Guide 154 FactoryTalk Historian Time Expressions • 87 FactoryTalk Historian Time String Examples • 88

● Index ● ● ● ● ● 155 PIPointIDToTag() • 141 PIPropertyToValue() • 145 PIPutVal() • 146 PISampDat() • 118 PISampFilDat() • 119 PIServer Argument •

● ● ● ● ● FactoryTalk Historian DataLink User Guide 156 W Worksheet Data • 75 Worldwide Support • 150 Write Data to FactoryTalk Historian • 105

2 ● Basics ● ● ● ● ● 9  Click an item in the PI ribbon to open a corresponding task pane or dialog box.  Place the cursor over an item to displ

● ● ● ● ● FactoryTalk Historian DataLink User Guide 10  Click in the desired output cell, and then click a function on the PI ribbon to add a fun

2 ● Basics ● ● ● ● ● 11 PI Menu FactoryTalk Historian DataLink for Excel XP-2003 adds a PI menu to the Excel menu bar once the DataLink add-in is l

● ● ● ● ● Copyright ii Contact Rockwell Automation Customer Support Telephone — 1.440.646.3434 Online Support — http://www.rockwellautomation.com/s

● ● ● ● ● FactoryTalk Historian DataLink User Guide 12 FactoryTalk Historian DataLink for Excel XP-2003 uses a standard menu configuration:

2 ● Basics ● ● ● ● ● 13  Choose an item from the PI menu to open a corresponding dialog box. Function Dialog Boxes FactoryTalk Historian DataLin

● ● ● ● ● FactoryTalk Historian DataLink User Guide 14  FactoryTalk Historian DataLink automatically retrieves values from FactoryTalk Historian

2 ● Basics ● ● ● ● ● 15 FactoryTalk Historian Server Connections Use the Connections dialog box to manage connections to FactoryTalk Historian ser

● ● ● ● ● FactoryTalk Historian DataLink User Guide 16 Choose Server > Add Server or right-click in the server pane and choose Add Server to dis

2 ● Basics ● ● ● ● ● 17 Historian server at a time, although you may need to specify the target server when you define a FactoryTalk Historian Data

● ● ● ● ● FactoryTalk Historian DataLink User Guide 18  Select an item from choices in a list , such as a calculation or a sampling method.  R

2 ● Basics ● ● ● ● ● 19  PI Server (page 20)  Output Cell (page 20) Tagname(s) or Expression The Tagname(s) or Expression field is required by

● ● ● ● ● FactoryTalk Historian DataLink User Guide 20 the time range are retrieved from the FactoryTalk Historian archive or calculated to create

2 ● Basics ● ● ● ● ● 21 Instead, only the results that fit within the specified array dimensions are displayed. Time Arguments Many FactoryTalk Hi

Contents iii Introduction ... 1 About this Document ...

● ● ● ● ● FactoryTalk Historian DataLink User Guide 22  To enter intervals in terms of frequency, convert the frequency to equivalent seconds. Fo

2 ● Basics ● ● ● ● ● 23 When specified, appended data appear in columns (or rows) adjacent to the primary values returned by a function according t

● ● ● ● ● FactoryTalk Historian DataLink User Guide 24 Show Timestamps A show timestamps check box appears when a function returns an array of Fact

2 ● Basics ● ● ● ● ● 25 Time stamps are displayed to match the specified row or column orientation. If all time stamps are shown, the order from le

● ● ● ● ● FactoryTalk Historian DataLink User Guide 26  Q = questionable, indicating that there is some reason to doubt the accuracy of the value

2 ● Basics ● ● ● ● ● 27 Display Formats You can change the default time and number formats used by FactoryTalk Historian DataLink to format data in

● ● ● ● ● FactoryTalk Historian DataLink User Guide 28 You can customize default format strings using any valid Excel format codes from the Format

2 ● Basics ● ● ● ● ● 29 Preference Settings Use the Settings dialog box to specify global preferences and default formatting for the output of PI D

● ● ● ● ● FactoryTalk Historian DataLink User Guide 30 Review the following for additional information: Copy items to sheet Choose In a row or In a

2 ● Basics ● ● ● ● ● 31 Number Format Type a number format to indicate how numeric function output should be displayed by default. The format strin

● ● ● ● ● FactoryTalk Historian DataLink User Guide iv Entry Fields ... 17 Standard

● ● ● ● ● FactoryTalk Historian DataLink User Guide 32 Your goals First consider your goals.  What data do you want to display in a spreadsheet

Chapter 3 33 FactoryTalk Historian DataLink Functions FactoryTalk Historian DataLink functions enable you to query, calculate and return FactoryTa

● ● ● ● ● FactoryTalk Historian DataLink User Guide 34 Example: To see the current value of the sinusoid tag, the following arguments are set for t

3 ● FactoryTalk Historian DataLink Functions ● ● ● ● ● 35 Retrieval Mode One of five modes can be selected to produce an archive value:  Previous

● ● ● ● ● FactoryTalk Historian DataLink User Guide 36 The resulting function array appears as: Compressed Data Returns either all values of a Fa

3 ● FactoryTalk Historian DataLink Functions ● ● ● ● ● 37 Filter Expression Add a filter expression to filter event values using a mathematical exp

● ● ● ● ● FactoryTalk Historian DataLink User Guide 38 Show Value Attributes Select show value attributes to display extended status bits associate

3 ● FactoryTalk Historian DataLink Functions ● ● ● ● ● 39 show timestamps column The resulting function array appears as: Note that all events bet

● ● ● ● ● FactoryTalk Historian DataLink User Guide 40 Time Interval Type a time interval as a FactoryTalk Historian time (page 86) expression. The

3 ● FactoryTalk Historian DataLink Functions ● ● ● ● ● 41 The resulting function array appears as: where a value is displayed for each 3-hour inte

● Contents ● ● ● ● ● v Point ID to Tag ... 54 Attribute Mask to Tag ...

● ● ● ● ● FactoryTalk Historian DataLink User Guide 42 Special Notes You can use a PI Expression (page 91) instead of a tag name for this function.

3 ● FactoryTalk Historian DataLink Functions ● ● ● ● ● 43 where the column of timed data appears in the column to the right, based on time stamps r

● ● ● ● ● FactoryTalk Historian DataLink User Guide 44 Calculation Mode The following calculations are provided:  Total  Minimum  Maximum

3 ● FactoryTalk Historian DataLink Functions ● ● ● ● ● 45 Expression Sampling Mode Different sampling modes are available for functions that includ

● ● ● ● ● FactoryTalk Historian DataLink User Guide 46 Minimum Percent Good Specify the minimum percentage of good data (page 25) required in each

3 ● FactoryTalk Historian DataLink Functions ● ● ● ● ● 47 Example: To see the hourly range of values beginning the same day at midnight until the p

● ● ● ● ● FactoryTalk Historian DataLink User Guide 48 where the range is calculated for each one hour interval. Note that any available calculatio

3 ● FactoryTalk Historian DataLink Functions ● ● ● ● ● 49 show percent good column The resulting function array appears as: where the results show

● ● ● ● ● FactoryTalk Historian DataLink User Guide 50

Chapter 4 51 FactoryTalk Historian Tags and Attributes A FactoryTalk Historian point is a stream of real-time data from a defined source, and is d

● ● ● ● ● FactoryTalk Historian DataLink User Guide vi High Availability Connection Preferences ... 84 FactoryTalk Hist

● ● ● ● ● FactoryTalk Historian DataLink User Guide 52 spreadsheets. Selected tags are copied to the spreadsheet, or added to a Tagname edit field

4 ● FactoryTalk Historian Tags and Attributes ● ● ● ● ● 53 Alias Search Provides a logical tree view of a FactoryTalk Historian server through the

● ● ● ● ● FactoryTalk Historian DataLink User Guide 54 Tag Functions The following section describes FactoryTalk Historian DataLink tag functions.

4 ● FactoryTalk Historian Tags and Attributes ● ● ● ● ● 55 where the tag name is displayed in the column to the right, based on a reference to the

● ● ● ● ● FactoryTalk Historian DataLink User Guide 56 Note that attribute strings must match attribute values in FactoryTalk Historian, and wildca

Chapter 5 57 Module Database Objects The FactoryTalk Historian Module Database provides an entirely different way to build the infrastructure of a

● ● ● ● ● FactoryTalk Historian DataLink User Guide 58 The following topics describe the tools provided by FactoryTalk Historian DataLink to access

5 ● Module Database Objects ● ● ● ● ● 59 Aliases and properties corresponding to FactoryTalk Historian points must be configured in your FactoryTal

● ● ● ● ● FactoryTalk Historian DataLink User Guide 60  Alias  Property  Module Use the PI Server drop-down list to display module databases

5 ● Module Database Objects ● ● ● ● ● 61  Click in the desired output cell, and then choose PI > <function name> to open a function dial

● Contents ● ● ● ● ● vii Troubleshooting ... 106 FactoryTalk Historian Menu Not Availab

● ● ● ● ● FactoryTalk Historian DataLink User Guide 62 DataLink can return values for Boolean, Date, Double, Integer, Long, Null, Single and String

Chapter 6 63 Spreadsheets When you add a function to a spreadsheet, FactoryTalk Historian DataLink retrieves the requested data from the FactoryTa

● ● ● ● ● FactoryTalk Historian DataLink User Guide 64 All workbooks open in the current Excel session are calculated immediately, and then at spec

6 ● Spreadsheets ● ● ● ● ● 65 Volatile Excel time functions such as now() and today() recalculate with the most frequency. Whenever a user edits a

● ● ● ● ● FactoryTalk Historian DataLink User Guide 66 Manual recalculation is the best method for variable-size arrays (page 69) where the number

6 ● Spreadsheets ● ● ● ● ● 67 For example, when you select a cell and add a Current Value function, you specify the name of the FactoryTalk Histori

● ● ● ● ● FactoryTalk Historian DataLink User Guide 68 You must move or clear an entire Excel array including all cells, or DataLink displays an er

6 ● Spreadsheets ● ● ● ● ● 69 Note that you can also edit function syntax directly by pressing F2 to edit in the Excel formula bar. Press Ctrl+Shif

● ● ● ● ● FactoryTalk Historian DataLink User Guide 70 the function. To maintain a constant size for Compressed Data (page 36) functions, specify t

6 ● Spreadsheets ● ● ● ● ● 71 site and use FactoryTalk Historian DataLink Server to display and recalculate FactoryTalk Historian function data.

● ● ● ● ● FactoryTalk Historian DataLink User Guide viii PICalcVal() ... 124 PIAdvCa

● ● ● ● ● FactoryTalk Historian DataLink User Guide 72

Chapter 7 73 Trend Displays FactoryTalk Historian DataLink includes an ActiveX trend control object that can be inserted into any Excel spreadshee

● ● ● ● ● FactoryTalk Historian DataLink User Guide 74 Create a Trend A wizard makes it easy to insert and configure trends. To insert a trend: 1.

7 ● Trend Displays ● ● ● ● ● 75 Worksheet Data If you choose Data on worksheet, the trend wizard prompts you to select the cell range containing th

● ● ● ● ● FactoryTalk Historian DataLink User Guide 76 The first element is the cell above the first value of the selected range if the data is arr

7 ● Trend Displays ● ● ● ● ● 77 1. Click in the Tag name field, then click and drag to select a cell range in the spreadsheet. You can also enter

● ● ● ● ● FactoryTalk Historian DataLink User Guide 78  Click the X and X All buttons to remove selected and all traces, respectively.  Click t

7 ● Trend Displays ● ● ● ● ● 79 Title and Placement Complete the trend and determine its location in the final screen. To complete the trend: 1.

● ● ● ● ● FactoryTalk Historian DataLink User Guide 80 If Existing worksheet is selected, then the cell (or range used to size the trend) where the

7 ● Trend Displays ● ● ● ● ● 81  Choose Format to edit the display of individual traces or the trend as a whole using the Trend Control Propertie

Chapter 1 1 Introduction FactoryTalk Historian DataLink is a Microsoft Excel add-in that enables you to retrieve information from your FactoryTalk

● ● ● ● ● FactoryTalk Historian DataLink User Guide 82  Choose Export Data to paste the values used to plot the trend into spreadsheet cells. You

Chapter 8 83 Supplementary Information Setup There are two ways for users to work with FactoryTalk Historian DataLink functions in Excel spreadshe

● ● ● ● ● FactoryTalk Historian DataLink User Guide 84 High Availability Connection Preferences The connection preference is set at the time of the

8 ● Supplementary Information ● ● ● ● ● 85 *.*.*.*, DISALLOW 192.168.100.*, ALLOW allows connections only from TCP/IP addresses starting with 192.1

● ● ● ● ● FactoryTalk Historian DataLink User Guide 86 The PIAdmin account is a super-privileged user and has read and write access to all tags. Fo

8 ● Supplementary Information ● ● ● ● ● 87 PI Times can also be expressed using certain constants: Constant Result * The current time. Today or t 1

● ● ● ● ● FactoryTalk Historian DataLink User Guide 88 When using FactoryTalk Historian times, follow these guidelines:  Use absolute or combined

8 ● Supplementary Information ● ● ● ● ● 89 PI Time String Meaning *-1h One hour ago t+8h 8:00:00 am today y-8h 4:00:00 pm on the day before yesterd

● ● ● ● ● FactoryTalk Historian DataLink User Guide 90 Data Type Support Digital (defined states) supported Int (16 and 32) supported Float (16, 32

8 ● Supplementary Information ● ● ● ● ● 91 Some functions also provide a field for a Filter Expression argument. You can supply a filter expression