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.
Contents Introduction ............................................................ 1 About this Document ............................................................ 2 FactoryTalk Historian DataLink Editions ..................................... 2 PI SDK........................................................................... 3 FactoryTalk Historian DataLink Language Support ..................... 3 System Requirements............................................................
● ● ● FactoryTalk Historian DataLink User Guide ● ● Entry Fields ................................................................... 17 Standard Arguments ........................................................ 18 Tagname(s) or Expression .............................................. 19 Start Time ................................................................ 19 End Time .................................................................. 20 PI Server ...................................................
● ● 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 ...................................................
● ● ● 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 ...................................................
● ● Contents ● ● ● ● Troubleshooting ............................................................... FactoryTalk Historian Menu Not Available ............................ Array and Cell Limits ..................................................... Row Limitations ........................................................... Security ..................................................................... FactoryTalk Historian Security ..........................................
● ● ● FactoryTalk Historian DataLink User Guide ● ● PICalcVal() .................................................................. PIAdvCalcVal() ............................................................. PIAdvCalcFilVal() .......................................................... PIAdvCalcExpVal() ......................................................... PIAdvCalcExpFilVal() ...................................................... PICalcDat() ............................................................
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 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.
● 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.
● ● ● 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.
● 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\\FTHInstallerLogs\.
● ● ● 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.
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.
● ● ● 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 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.
● 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 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.
● 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.
● ● ● 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.
● ● ● 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.
● ● 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.
● ● ● 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.
● 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.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Select an item from choices in a list sampling method. Reference spreadsheet cells that contain arguments, such as a tag name, output cell location, or time stamp. , such as a calculation or a 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 through a tag or module database search.
● 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.
● ● ● 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.
● 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).
● ● ● 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.
● ● 2 ● Basics ● ● ● When specified, appended data appear in columns (or rows) adjacent to the primary values returned by a function according to the following rules: Time data are added in columns to the left (or rows above) primary values. Other related data are added in columns to the right (or rows below) primary values. Appended Data Array The following table illustrates how data are appended per function.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Show Timestamps A show timestamps check box appears when a function returns an array of FactoryTalk Historian values over time. If selected, this option returns a corresponding time stamp with each FactoryTalk Historian event value. For functions that return multiple event values (for example, Sampled Data), the orientation controls (page 26) determine how time stamps are displayed in relation to each value.
● 2 ● Basics ● ● ● ● Time stamps are displayed to match the specified row or column orientation. If all time stamps are shown, the order from left column to right or top row to bottom is start time, end time, minimum value time, maximum value time, event value. Show Percent Good Select show percent good to display the percentage of time for which good values are returned over the total time range of the array.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Q = questionable, indicating that there is some reason to doubt the accuracy of the value. Value attributes are displayed in a column to the right of event values. Show Annotations Select show annotations to display any annotations associated with returned event values in a column to the right. Annotations are notes or comments appended to a data value in FactoryTalk Historian for descriptive purposes.
● 2 ● Basics ● ● ● ● Display Formats You can change the default time and number formats used by FactoryTalk Historian DataLink to format data in function arrays. Default formats are specified in the Settings (page 29) dialog box, where two settings are available for formats: The default number format General formats numbers (and all nontime stamp data) to match the formatting Category General in the Excel Format Cells dialog box. The default time format dd-mmm-yy hh:mm:ss.
● ● ● FactoryTalk Historian DataLink User Guide ● ● You can customize default format strings using any valid Excel format codes from the Format Cells dialog box in Excel. For example, German equivalents of General and dd-mmm-yyyy hh:mm:ss are Standard and TTMM-JJJJ hh:mm:ss, respectively. You can also apply individual time and date formatting to any spreadsheet cell, including those containing FactoryTalk Historian DataLink functions, by choosing Format > Cells > Number.
● 2 ● Basics ● ● ● ● Preference Settings Use the Settings dialog box to specify global preferences and default formatting for the output of PI DataLink functions. To access the settings: 1. Click Settings on the PI ribbon (2007), or choose PI > Settings (2003). 2. Set the desired preferences and click OK.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Review the following for additional information: Copy items to sheet Choose In a row or In a column to copy the names of multiple tags selected in a Tag Search (or items in a Module Browse search) to the spreadsheet in a row or column of values, respectively. The default setting is In a column. This command is also available as a shortcut to the Tag Search ribbon item (2007).
● ● 2 ● Basics ● ● ● Number Format Type a number format to indicate how numeric function output should be displayed by default. The format string may be any valid number format code from the Excel Format dialog box (page 26). On install, FactoryTalk Historian DataLink assigns the default number format from Excel to this field, unless you have set your own format preference previously in DataLink. If your version of Excel runs a different locale, this format includes correct syntax to reflect the locale.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Your goals First consider your goals.
Chapter 3 FactoryTalk Historian DataLink Functions FactoryTalk Historian DataLink functions enable you to query, calculate and return FactoryTalk Historian point values and attributes to spreadsheet cells. Like other Excel functions, FactoryTalk Historian DataLink function results are displayed in arrays which can be recalculated to display updated event values as needed.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Example: To see the current value of the sinusoid tag, the following arguments are set for the Current Value function: Tagname = sinusoid time at left The resulting function array appears as: Archive Value Retrieves a FactoryTalk Historian point value, or evaluates an expression corresponding to a specified time stamp.
● ● 3 ● FactoryTalk Historian DataLink Functions ● ● ● Retrieval Mode One of five modes can be selected to produce an archive value: Previous Special Notes Retrieves the value exactly matching or preceding the specified time stamp. Previous only Retrieves the value preceding the specified time stamp. Interpolated Interpolates the value at the specified time, or follows Previous only behavior. Auto Interpolates the value at the specified time, or follows Previous behavior.
● ● ● FactoryTalk Historian DataLink User Guide ● ● The resulting function array appears as: Compressed Data Returns either all values of a FactoryTalk Historian point occurring within a specified time range, or a number of point values beginning at a certain time. Compressed values are data recorded by the FactoryTalk Historian server after a compression algorithm has removed all values that represent the same slope.
● 3 ● FactoryTalk Historian DataLink Functions ● ● ● ● Filter Expression Add a filter expression to filter event values using a mathematical expression, eliminating data for which the expression evaluates as false. The filter expression is applied to the raw data from FactoryTalk Historian (and not values that result from the calculation itself). For example, the simple filter expression: sinusoid < 70 would remove all values over 70 from the calculation.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Show Value Attributes Select show value attributes to display extended status bits associated with returned event values. The returned bits correspond to value attributes stored in FactoryTalk Historian, and may be one of the following: A = annotated, indicating a comment has been added to the event. S = substituted, indicating the event value has been changed from its original value.
● 3 ● FactoryTalk Historian DataLink Functions ● ● ● ● show timestamps column The resulting function array appears as: Note that all events between two specified times could also be retrieved by the same function using the Time Range argument. Sampled Data Returns evenly-spaced, interpolated sample values for a FactoryTalk Historian point or expression over a regular interval.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Time Interval Type a time interval as a FactoryTalk Historian time (page 86) expression. The interval is the sampling frequency used to collect or calculate values over the course of the time range. For example, an interval of 15m (15 minutes) returns a value for every 15 minute interval. Filter Expression Add a filter expression to filter event values using a mathematical expression, eliminating data for which the expression evaluates as false.
● ● 3 ● FactoryTalk Historian DataLink Functions ● ● ● The resulting function array appears as: where a value is displayed for each 3-hour interval. Timed Data Returns actual or interpolated sample values for a FactoryTalk Historian point to match an array of specified time stamps. Required Arguments Tagname Timestamp A reference to one or more spreadsheet cells containing time stamp values.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Special Notes You can use a PI Expression (page 91) instead of a tag name for this function. Reference a range of tag names to display sampled values for each. Note that by default, values are displayed in rows for a column of referenced tags, and in columns for a row of referenced tags. For tags with step attributes, the value preceding a specified time stamp is substituted for an interpolated value when the calculation mode is Interpolated.
● ● 3 ● FactoryTalk Historian DataLink Functions ● ● ● where the column of timed data appears in the column to the right, based on time stamps retrieved for a different tag in the first two columns at left. Calculated Data Returns one or more evenly-spaced, calculated values based on FactoryTalk Historian point values or evaluated expressions. A range of different calculation and sampling preferences are provided.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Calculation Mode The following calculations are provided: Total Minimum Maximum Standard deviation Range Average Count Mean All calculation modes are time-weighted except for the Mean function, which is an event-weighted version of the Average function. Calculation Basis Select either a time- or event-weighted basis for the calculation: Calculations are time-weighted by default.
● 3 ● FactoryTalk Historian DataLink Functions ● ● ● ● Expression Sampling Mode Different sampling modes are available for functions that include expressions: For FactoryTalk Historian tag calculations, select one of three sampling modes. Point Compressed mode is the default used for FactoryTalk Historian tag calculations. This mode considers the time stamps for the FactoryTalk Historian tag for which the calculation is being performed.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Minimum Percent Good Specify the minimum percentage of good data (page 25) required in each time range to calculate and return a value. Insufficient good data is substituted as a placeholder when a value is not returned. Timestamps Select show start time to display the Start time of each interval used to calculate a value. Start times are added in a column to the left or a row above calculated values.
● 3 ● FactoryTalk Historian DataLink Functions ● ● ● ● Example: To see the hourly range of values beginning the same day at midnight until the present time for the sinusoid tag, the following arguments are set for the Calculated Data function: PI Tag Tagname = sinusoid Start Time = t End Time = * Time Interval = 1h Calculation Mode = range Calculation Basis = time-weighted show start time show end time column The resulting function array appears as: 47
● ● ● FactoryTalk Historian DataLink User Guide ● ● where the range is calculated for each one hour interval. Note that any available calculation could be specified over any interval. Time Filtered Returns the amount of time over which a FactoryTalk Historian expression evaluates as true for a specified time range. Time Filtered results vary slightly depending on your FactoryTalk Historian server version.
● 3 ● FactoryTalk Historian DataLink Functions ● ● ● ● show percent good column The resulting function array appears as: where the results show the tag value was over 75 for 7.85 hours over the previous 7 days.
● ● ● FactoryTalk Historian DataLink User Guide ● ● 50
Chapter 4 FactoryTalk Historian Tags and Attributes A FactoryTalk Historian point is a stream of real-time data from a defined source, and is described by a corresponding tag name and other attributes. In FactoryTalk Historian DataLink and this online help, FactoryTalk Historian points are frequently referred to as FactoryTalk Historian tags, and the terms are used somewhat interchangeably. However, a tag is simply a name for a FactoryTalk Historian point.
● ● ● FactoryTalk Historian DataLink User Guide ● ● spreadsheets. Selected tags are copied to the spreadsheet, or added to a Tagname edit field as a function argument. To access tag search: Click Tag Search on the PI ribbon (2007), or choose PI > Tag Search (2003), or click Tag Search next to a Tagname field. The Tag Search dialog box provides three types of searches: 52 Basic Search Allows you to create a tag mask by specifying FactoryTalk Historian point attributes.
● 4 ● FactoryTalk Historian Tags and Attributes ● ● ● ● Alias Search Provides a logical tree view of a FactoryTalk Historian server through the FactoryTalk Historian Module Database, which you can use to select tags by their descriptive aliases. To search for tags: 1. Click a tab to choose a Basic, Advanced or Alias search. 2. Type the required search criteria and click Search. Use '*' or '?' as wildcard characters to search for tag names and attributes.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Tag Functions The following section describes FactoryTalk Historian DataLink tag functions. These functions can be used to resolve FactoryTalk Historian point IDs or other point attributes into a matching tag name. You can also retrieve other point attribute values based on a tag name.
● 4 ● FactoryTalk Historian Tags and Attributes ● ● ● ● where the tag name is displayed in the column to the right, based on a reference to the cell containing the point ID on the left. Attribute Mask to Tag Returns a list of tag names in FactoryTalk Historian that match one or more specified tag attribute values.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Note that attribute strings must match attribute values in FactoryTalk Historian, and wildcards can be used. The function operates the same as a tag search (page 51). Tag Attributes Retrieves an attribute associated with a specified FactoryTalk Historian tag name. Required Fields Tagname Attribute Attribute Specify the tag attribute to be retrieved from FactoryTalk Historian.
Chapter 5 Module Database Objects The FactoryTalk Historian Module Database provides an entirely different way to build the infrastructure of a spreadsheet. If your FactoryTalk Historian server's module database is configured and includes objects that represent the tags you want to use, your spreadsheets can be easier to build and provide more flexibility and potential for re-use.
● ● ● FactoryTalk Historian DataLink User Guide ● ● The following topics describe the tools provided by FactoryTalk Historian DataLink to access FactoryTalk Historian Module Database objects. See your FactoryTalk Historian server documentation for more information about module databases and configuration.
● 5 ● Module Database Objects ● ● ● ● Aliases and properties corresponding to FactoryTalk Historian points must be configured in your FactoryTalk Historian server Module Database to use the browse and Module Database functions. To browse and select from the FactoryTalk Historian Module Database: 1. Select a cell and click Module Browse on the PI ribbon, or choose PI > Module Browse. The Module Database dialog box appears. 2.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Alias Property Module Use the PI Server drop-down list to display module databases for different connected FactoryTalk Historian servers. 3. Specify a Query Date and time to supply a temporal context for objects displayed in the module database, if necessary, to select the correct object version. 4. Browse the tree control to find the desired item, and add it or a parent collection object to the spreadsheet: 5.
● 5 ● Module Database Objects ● ● ● ● Click in the desired output cell, and then choose PI > to open a function dialog box (XP-2003), or click a function on the FactoryTalk Historian ribbon to open a new function task pane (2007). You cannot resolve an alias or property object into a tag name without a module path. The path provides a context for the alias that maps it to a specific FactoryTalk Historian point. Module Database functions use common function components (page 17).
● ● ● FactoryTalk Historian DataLink User Guide ● ● DataLink can return values for Boolean, Date, Double, Integer, Long, Null, Single and String property types, or Double, Integer, Long and Single array property types. Required Arguments Property Module Path Query Date 62 Property Specify a FactoryTalk Historian property name. A property can be entered directly or referenced in a cell. You can also browse to a property in the FactoryTalk Historian Module Database.
Chapter 6 Spreadsheets When you add a function to a spreadsheet, FactoryTalk Historian DataLink retrieves the requested data from the FactoryTalk Historian server into an array. The size of the array can vary depending on the function, how many matching archive values are available in FactoryTalk Historian, the number of values requested, or the number of input references.
● ● ● FactoryTalk Historian DataLink User Guide ● ● All workbooks open in the current Excel session are calculated immediately, and then at specified intervals based on your Automatic Update preference settings. Automatic Update remains activated (with an indicator message in the Excel status bar) until it is toggled off. During a recalculation event the status bar also displays the calculation interval. Automatic Update cannot update protected workbooks, which are read-only.
● ● 6 ● Spreadsheets ● ● ● Volatile Excel time functions such as now() and today() recalculate with the most frequency. Whenever a user edits a spreadsheet cell, or presses F9, Excel updates all volatile time functions in the spreadsheet. The update in turn triggers any functions that reference a time stamp based on a volatile time function. To base a FactoryTalk Historian DataLink function on a volatile Excel function: 1. Use the Excel function bar to enter a volatile function in a spreadsheet cell.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Manual recalculation is the best method for variable-size arrays (page 69) where the number of values returned may differ. If more values are available than the current size of the array, an array does not expand to display the additional values on recalculation unless it is manually recalculated. To manually recalculate a single function: Right-click in any part of an array and choose Recalculate (Resize) Function from the context menu.
● ● 6 ● Spreadsheets ● ● ● For example, when you select a cell and add a Current Value function, you specify the name of the FactoryTalk Historian tag whose current value you want to see in the Tagname field. If you type the sinusoid tag, FactoryTalk Historian DataLink pastes the following function into the target cell: =PICurrVal("sinusoid",0,) Excel evaluates this function, retrieves the latest value of sinusoid from FactoryTalk Historian, and displays it in the cell.
● ● ● FactoryTalk Historian DataLink User Guide ● ● You must move or clear an entire Excel array including all cells, or DataLink displays an error message: You cannot change part of an array. You can also copy the values from a function array if you want to use them elsewhere in a spreadsheet. Once you copy and paste the values, they are no longer part of a function array; they are simply cell values and are not updated.
● ● 6 ● Spreadsheets ● ● ● Note that you can also edit function syntax directly by pressing F2 to edit in the Excel formula bar. Press Ctrl+Shift+Enter to save the edits when complete. Resize Arrays When a spreadsheet is called up at different times, a different number of values may be available for a defined function. If the number of values exceeds the current size of the array, the array does not expand to display the additional values unless you manually recalculate (page 65) the function.
● ● ● FactoryTalk Historian DataLink User Guide ● ● the function. To maintain a constant size for Compressed Data (page 36) functions, specify the number of values to retrieve rather than a time range. Or to display only a predetermined portion of results, specify a limited output cell range (page 18).
● 6 ● Spreadsheets ● ● ● ● site and use FactoryTalk Historian DataLink Server to display and recalculate FactoryTalk Historian function data.
● ● ● FactoryTalk Historian DataLink User Guide ● ● 72
Chapter 7 Trend Displays FactoryTalk Historian DataLink includes an ActiveX trend control object that can be inserted into any Excel spreadsheet to display the trend of event values over time. Embedded trend objects can display data from both the spreadsheet and selected FactoryTalk Historian points.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Create a Trend A wizard makes it easy to insert and configure trends. To insert a trend: 1. Click in the cell where you want to place the top left corner of the trend object, click PI > Insert Trend (XP-2003), or click Insert Trend on the PI ribbon (2007) to display the Trend Add-in Wizard. When selecting the input cell, consider that the default size of the trend object requires approximately 14 x 8 standard cells. 2. 3.
● ● 7 ● Trend Displays ● ● ● Worksheet Data If you choose Data on worksheet, the trend wizard prompts you to select the cell range containing the data you want to trend. To specify worksheet data for the trend: 1. Click in the Cell Range containing data field, then click and drag to select a cell range in the spreadsheet. Unless you clear Include all cells in array, you only need to select one cell of a function array.
● ● ● FactoryTalk Historian DataLink User Guide ● ● The first element is the cell above the first value of the selected range if the data is arranged in column format, and the first cell to the left of the first value of the range for data in row format. 3. Select Stepped to plot a stepped trend instead of a continuous trace. This option is usually specified for discrete tags. 4. Click Add to add the selected cell range to the trend. 5.
● ● 7 ● Trend Displays ● ● ● 1. Click in the Tag name field, then click and drag to select a cell range in the spreadsheet. You can also enter a cell reference directly, or use the reference button to display a reference dialog used to browse and add cell references. You can also click the selection button to search for tags (page 51) in FactoryTalk Historian. Tags selected through a tag search are added automatically added to the list of traces. 2.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Click the X and X All buttons to remove selected and all traces, respectively. Click the Rename button to rename a selected trace in the trace legend. Specify the Time Range When the trend traces are determined, the wizard prompts you to specify a time range for the trend if any of the traces are drawn directly from FactoryTalk Historian. To specify a time range: 1.
● 7 ● Trend Displays ● ● ● ● Title and Placement Complete the trend and determine its location in the final screen. To complete the trend: 1. Type a Trend title. 2. Clear Show Value Attributes if you do not want value attributes to appear along trend traces. The icons appear when the range of data contains events that have the Questionable, Substituted, or Annotated bits set. 3.
● ● ● FactoryTalk Historian DataLink User Guide ● ● If Existing worksheet is selected, then the cell (or range used to size the trend) where the trend is to be placed must be specified or referenced in the accompanying field. If you change your default placement cell, make sure the new cell does not contain data (which will be covered by the trend display). Context Menu Once a trend is inserted into a spreadsheet, you can update, edit or manage it using the context menu.
● 7 ● Trend Displays ● ● ● ● Choose Format to edit the display of individual traces or the trend as a whole using the Trend Control Properties dialog box. You can change the color or line thickness of a trace, or add legend items to the trend. Choose Define Trend to display the trace definition screen of the trend wizard to update or change the trace sources. Choose Define Trend Time Range to display the time range definition screen of the trend wizard to update the time range.
● ● ● FactoryTalk Historian DataLink User Guide ● ● 82 Choose Export Data to paste the values used to plot the trend into spreadsheet cells. You can specify row or columnar format, and also export trace names as headings. Choose Move/Resize and enter a cell location to move the upper left hand corner of the trend to the specified cell. Enter a cell range to resize the trend.
Chapter 8 Supplementary Information Setup There are two ways for users to work with FactoryTalk Historian DataLink functions in Excel spreadsheets: Users can install a stand-alone version of FactoryTalk Historian DataLink on their local PC. Web users may view spreadsheets with FactoryTalk Historian DataLink functions using FactoryTalk Historian DataLink Server. Contact your Rockwell Automation sales representative for more information.
● ● ● FactoryTalk Historian DataLink User Guide ● ● High Availability Connection Preferences The connection preference is set at the time of the first connection. For FactoryTalk Historian DataLink 3.1.6 and above, the connection preference for DataLink functions is set to Any. However, since the connection preference is set at the time of the connection, the order in which the connection is made matters.
● ● 8 ● Supplementary Information ● ● ● *.*.*.*, DISALLOW 192.168.100.*, ALLOW allows connections only from TCP/IP addresses starting with 192.168.100. FactoryTalk Historian Trusts A FactoryTalk Historian trust allows clients to connect to a FactoryTalk Historian server machine and retrieve data without explicit user login. A trust may instead allow a user to log in based on an IP address, Windows domain, user name, application or other criteria.
● ● ● FactoryTalk Historian DataLink User Guide ● ● The PIAdmin account is a super-privileged user and has read and write access to all tags. For this reason, System Administrators should not normally allow FactoryTalk Historian DataLink users to log in as PIAdmin. FactoryTalk Historian Time FactoryTalk Historian Time abbreviations and FactoryTalk Historian Time expressions allow you to specify times and time ranges for data using constants, variables, and short expressions.
● ● 8 ● Supplementary Information ● ● ● PI Times can also be expressed using certain constants: Constant Result * The current time. Today or t 12:00 am of the current day. Yesterday or y 12:00 am of the previous day. Sunday or sun 00:00:00 (midnight) on the most recent past Sunday (in reference to the Historian Server). FactoryTalk Historian Time Expressions FactoryTalk Historian allows three types of time expressions: relative time, combined time, and absolute time.
● ● ● FactoryTalk Historian DataLink User Guide ● ● When using FactoryTalk Historian times, follow these guidelines: Use absolute or combined time expressions. Avoid using relative time expressions. Multiple relative time expressions in a time range may cause an incorrect start time or an error message, depending on the context of the expression. Relative and combined time expressions contain only a single operator: either a single plus sign (+) or a single minus sign (-).
● 8 ● Supplementary Information ● ● ● ● 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 yesterday mon+14.5h 2:30:00 pm last Monday sat-1m 11:59:00 pm last Friday Time Interval Examples In interval expressions, a positive or unmarked interval is based on the start time, and a negative interval is based on the end time of a time expression.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Data Type Support Digital (defined states) supported Int (16 and 32) supported Float (16, 32 and 64) supported String (text) supported Timestamp supported Blob not supported FactoryTalk Historian Expressions FactoryTalk Historian expressions (also known as FactoryTalk Historian Performance Equations) are used for functions that incorporate mathematical operations and calculations based on FactoryTalk Historian points.
● 8 ● Supplementary Information ● ● ● ● Some functions also provide a field for a Filter Expression argument. You can supply a filter expression to limit the values returned by a function, and reference a filter expression in a worksheet cell.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Type Operator Syntax Example Meaning Arithmetic + A+B Addition: A + B - A-B Subtraction: A minus B * A*B Multiplication: A times B / A/B Division: A divided by B ^ A^B Raising to a power: A to the power of B (AB) Mod A mod B Modulus: the remainder of A divided by B < A A>B Greater than: returns true if A is greater than B <= A <= B
● 8 ● Supplementary Information ● ● ● ● Type Operator Syntax Example Meaning If-Then-Else Expressions if then else ...BN) enclosed in the parentheses.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Math Functions 94 Name Description Abs Absolute value. Asin Arc sine. Acos Arc cosine. Atn Arc tangent. Atn2 Arc tangent (two arguments). Cos Cosine. Cosh Hyperbolic cosine. Exp Exponential. Float Conversion of string to number. Frac Fractional part of number. Int Integer part of number. Log Natural logarithm. Log10 Common logarithm. Poly Evaluate polynomial. Round Round to nearest unit. Sgn Numerical sign. Sin Sine.
● 8 ● Supplementary Information ● ● ● ● Aggregate Functions Name Description Avg Average. Max Maximum. Median Median selector. Min Minimum. PStDev Population standard deviation. SStDev Sample standard deviation. Total Sum. Miscellaneous Functions Name Description BadVal See if a value is bad (not a number or time). Curve Get value of a curve. DigState Get digital state from a string. IsDST Test whether a time is in local daylight savings time period.
● ● ● FactoryTalk Historian DataLink User Guide ● ● FactoryTalk Historian Archive Retrieval Name Description NextEvent Time of a point's next Archive event. NextVal Point's next value after a time. PrevEvent Time of a point's previous Archive event. PrevVal Point's previous value before a time. TagVal Point's value at a time. FactoryTalk Historian Archive Search 96 Name Description FindEq Time stamp when point = value. FindGE Time stamp when point >= value.
● 8 ● Supplementary Information ● ● ● ● FactoryTalk Historian Archive Statistics Name Description EventCount Number of Archive events. PctGood Percent of good time in a period. Range Range of minimum to maximum value. StDev Time-weighted standard deviation. TagAvg Time-weighted average. TagMean Event-weighted average. TagMax Maximum value in a period. TagMin Minimum value in a period. TagTot Time integral over a period.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Name Description TagZero Get a point's zero value. Time Functions 98 Name Description Bod Time stamp for beginning of the day for given time. Bom Time stamp for beginning of the month for given time. Bonm Time stamp for first of the next month for given time. Day Day of the month from a time. DaySec Seconds since midnight from a time. Hour Hour from a time. Minute Minute from a time. Month Month from a time.
● 8 ● Supplementary Information ● ● ● ● Dynamic Response Name Description Arma Dynamic response from Auto Regressive Moving Average model. Delay Introduce time delay. MedianFilt Select the median value of time series. Impulse Dynamic response characterized by impulse response shape. Alarm Status Functions Name Description AlmAckStat Alarm acknowledgment status code. AlmCondition Condition code number for Alarm State. AlmCondText Alarm condition as text.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Name Description Format Formatting of a numerical number. InStr Instance of a sub-string. LCase Conversion of all characters to lower case. Len Length of a string. Left First characters in a string. LTrim Removal of blanks on the left side of a string. Mid Extraction of a sub-string from a string. Right Last characters in a string. RTrim Removal of blanks on the right side of a string.
● ● 8 ● Supplementary Information ● ● ● FactoryTalk Historian Expression Examples These examples use the tag delimiters : (colon) and . (period). Your FactoryTalk Historian system may use different delimiters. The following expression determines the sum of the value of sinusoid cubed and the value of cdf144 divided by 10: (('sinusoid')^3 + 'cdf144'/10) The expression below evaluates to true (non-zero) if the absolute value of the tag mytag is at least 14.65. (abs('my:tag') >= 14.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Use Array Functions When defining array functions follow these steps: Select an appropriate output range for the array based on the number of expected values. Enter the FactoryTalk Historian function and its arguments into the Excel formula bar. Place the FactoryTalk Historian function into the selected output cells with the Ctrl+Shift+Enter combination of keystrokes.
● 8 ● Supplementary Information ● ● ● ● For example, the expression below finds the difference between the natural logarithm of the value of the tag cdep158 and the cosine of the value of the tag cdep158. (log('cdep158') - cos('cdep158')) String Arguments String arguments must be surrounded by double quotes.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Outcodes FactoryTalk Historian DataLink function syntax includes integer outcode arguments. The outcodes determine how appended data (page 22) are returned and output cells are oriented. Outcodes are automatically generated by function dialog boxes and task panes. However, if you enter a function manually in Excel's function bar, you may need to calculate and include appropriate outcode values yourself.
● ● 8 ● Supplementary Information ● ● ● The formula used to calculate a corresponding outcode is: Functions have different arguments and outcodes. Those valid for one function are not necessarily valid for another. See the function reference (page 111) for outcode specifications by function. Example: Suppose we want a Calculated Data function to display Percent Good, Start Time, and Min/Max Time. Since Start Time and Min/Max Time are displayed, the Show Timestamps bit must also be set.
● ● ● FactoryTalk Historian DataLink User Guide ● ● value from a worksheet to a FactoryTalk Historian server. The Excel macro function PIPutVal() (page 146) replaces an existing FactoryTalk Historian archive event with a matching time stamp with a new value supplied by the user. Note that VBA does not recognize FactoryTalk Historian DataLink functions, but you can formulate a function as a text string, and then set the formula Array property of a range of cells to the text string value.
● ● 8 ● Supplementary Information ● ● ● Array and Cell Limits FactoryTalk Historian DataLink is subject to an Excel limitation on the number of elements in a calculation array. The maximum numbers of values that can be returned for a single FactoryTalk Historian function, per retrieval are: 65,536 for Excel XP-2003. 1,048,576 for Excel 2007 and later. The Excel SDK used by FactoryTalk Historian DataLink limits strings to 255 characters in length for Excel XP and later.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Security Excel security features for add-ins, ActiveX controls and macros allow you to determine the types of components that can run inside Excel. Different levels of settings provide a means to disable, enable with prompting, or completely enable these types of objects. Excel security settings may conflict with certain FactoryTalk Historian DataLink features: FactoryTalk Historian DataLink is an Excel add-in.
● ● 8 ● Supplementary Information ● ● ● Trust for the user, domain, application or machine (per Trust configurations). 2. If all attempts to login to the FactoryTalk Historian server using FactoryTalk Security/Windows credentials or other machine-related information fail, then you must have a FactoryTalk Historian ME native security user account (not FactoryTalk Security/Windows security-based).
● ● ● FactoryTalk Historian DataLink User Guide ● ● 110
Chapter 9 FactoryTalk Historian DataLink Function Reference FactoryTalk Historian DataLink function dialog boxes (XP-2003) or task panes (2007) (page 17) are commonly used to generate FactoryTalk Historian DataLink functions, with the resulting function depending on the options and arguments selected. These same functions may also be entered directly (page 101) in the Excel formula bar. The following complete reference to supported FactoryTalk Historian DataLink functions groups functions by type.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Arguments Argument Value tagname (string) The tag name or names matching the desired FactoryTalk Historian points. outcode (integer) An output code to determine results placement, either 0, 1, or 2. PIServer (string) The target FactoryTalk Historian server.
● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● ● Argument Value outcode (integer) An output code to determine results placement, either 0, 1, or 2. PIServer (string) The target FactoryTalk Historian server. mode (string) The mode used to determine which values are retrieved.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Returns Outcode Archive Value Time stamp 0 output cell 1 left column output cell 2 output cell row above Example: The following calculates the square root of the value of the tag sinusoid from the FactoryTalk Historian server thevax at midnight yesterday: =PIExpVal("sqr('sinusoid')","y",0,"thevax") Multiple Value Functions Multiple value functions associate a FactoryTalk Historian point with a range of time over which there can be one or ma
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● Argument Value outcode (integer) An output code. PIServer (string) The target FactoryTalk Historian server. mode (string) The method of handling values at boundaries of the time range. Returns Results display is determined by an output code (page 104) value. Example: The following retrieves 10 compressed data points and corresponding time stamps for the tag cdf144 starting from 1:00 a.m.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Argument Value outcode (integer) An output code. PIServer (string) The FactoryTalk Historian server. mode (string) The method of handling values at boundaries of the time range. Returns Results display is determined by an output code (page 104) value. Example: The following retrieves 10 compressed data points and the corresponding times, starting at 2:00 a.m.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● Argument Value outcode (integer) An output code. PIServer (string) The target FactoryTalk Historian server. mode (string) The method of handling values at boundaries of the time range. Returns Results display is determined by an output code (page 104) value. Example: The following retrieves compressed data points and corresponding time stamps for the tag cdf144 starting from 1:00 a.m. to 3:00 a.m.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Argument Value filtcode (integer) A filter code, either 1 to return Filtered status or 0 to skip. outcode (integer) An output code. PIServer (string) The target FactoryTalk Historian server. mode (string) The method of handling values at boundaries of the time range. Returns Results display is determined by an output code (page 104) value.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● Argument Value etime (string) The end time for the range (in FactoryTalk Historian time format). interval (string) The interval between sampled values (in FactoryTalk Historian time format). outcode (integer) An output code, either 0, 1, 2, or 3. PIServer (string) The target FactoryTalk Historian server.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Arguments Argument Value tagname(string) The tag name or names matching the desired FactoryTalk Historian points. stime (string) The start time for the range of sampling (in FactoryTalk Historian time format). etime (string) The end time in FactoryTalk Historian time format. interval (string) The interval between sampled values (in FactoryTalk Historian time format). filtexp (string) The filter expression used to filter results.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● Time stamps are shown in column1 and values in column2. A Filtered status is returned for values do not satisfy the filter condition in cell A1. PIExpDat() Retrieves FactoryTalk Historian point values based on an expression, using the following syntax: PIExpDat(expression, stime, etime, interval, outcode, PIServer) Arguments Argument Explanation expression (string) A FactoryTalk Historian calculation expression.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Example: The following calculates the square root of the value of the tag sinusoid from the FactoryTalk Historian server thevax at one-hour intervals, starting from midnight yesterday to midnight today: =PIExpDat("sqr('sinusoid')","y","t","1h",1,"thevax") It also displays the time stamps.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● =PITimeDat("mytag",b1:b12,,"interpolated") PITimeExpDat() Retrieves FactoryTalk Historian point values based on an expression, and corresponding to a specified array of time stamps, using the following syntax: PITimeExpDat(expression, timestamps, PIServer) Arguments Argument Explanation expression (string) A FactoryTalk Historian calculation expression.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Calculation Functions Calculation functions compute new values from FactoryTalk Historian point values corresponding to a specific time range. PICalcVal() Retrieves a calculated FactoryTalk Historian point value using the following syntax: PICalcVal(tagname, stime, etime, mode, cfactor, outcode, PIServer) PICalcVal() is generated only by the Calculated Data dialog box in XP-2003 versions. 2007 versions use PIAdvCalcVal().
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● If the outcode is 1, the following applies: Mode Column 1 Column 2 Total Total value Minimum Time of min. Value Minimum value Maximum Time of max. Value Maximum value Stdev Stdev. Value Range Time of min. Value Average Average value Count Count value Mean Mean value Time of max.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Mode Column 1 Column 2 Count Count value Percent good Mean Mean value Percent good Column 3 Column 4 An output code of 6 transposes the output array that results from an output code of 4, and results are returned in rows instead of in columns. An output code of 7 transposes the output array that results from an output code of 5, and results are returned in rows instead of in columns.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● Argument Explanation mode (string) The type of calculation to be used to compute point values. calcbasis (string) The weighting method used to calculate values. minpctgood (number) The minimum percentage of good data required to calculate and return a value. cfactor (number) The time unit of flow for the source tag, required for Total calculations. outcode (integer) An output code, either 0, 1, 2, 4, 5, 6, or 7, and not 3.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Arguments Argument Explanation tagname (string) The tag name or names matching the desired FactoryTalk Historian points for calculation. stime (string) The start time for the range of calculation (in FactoryTalk Historian time format). etime (string) The end time (in FactoryTalk Historian time format). filtexp (string) The filter expression used to filter results. mode (string) The type of calculation to be used to compute point values.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● will be sampled at the compressed events of cdf144. The minpctgood is 50 percent so result will only be displayed if percent good is 50 percent or greater. The percent good is returned next to the total.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Returns See specific information for PICalcVal() outcodes (page 124). Example: The following calculates a time-weighted total for the expression 'cdf144'+'cdt158' from yesterday to today from the default FactoryTalk Historian server: =PIAdvCalcExpVal("'cdf144'+'cdt158'","y","t","total","timeweighted","compressed","10m",50,1,4,) The result is multiplied by 1.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● Argument Explanation mode (string) The type of calculation to be used to compute point values. calcbasis (string) The weighting method used to calculate values. sampmode (string) The sampling method used for calculation expressions. sampfreq (string) The frequency used for interpolated sampling. minpctgood (number) The minimum percentage of good data required to calculate and return a value.
● ● ● FactoryTalk Historian DataLink User Guide ● ● PICalcDat() Retrieves calculated FactoryTalk Historian point values using the following syntax: PICalcDat(tagname, stime, etime, interval, mode, cfactor, outcode, PIServer) PICalcDat() is generated only by the Calculated Data dialog box in XP-2003 versions. 2007 versions use PIAdvCalcDat(). Arguments Argument Explanation tagname (string) The tag name or names matching the desired FactoryTalk Historian points for calculation.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● 4 produces values and percent good along 2 columns. 5 produces time stamps, values, and percent good along 3 columns. 6 produces values and percent good along 2 rows. 7 produces time stamps, values, and percent good along 3 rows.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Argument Explanation calcbasis (string) The weighting method used to calculate values. minpctgood (number) The minimum percentage of good data required to calculate and return a value. cfactor (number) The time unit of flow for the source tag, required for Total calculations. outcode (integer) An ouput code, 0-7. PIServer (string) The target FactoryTalk Historian server. Returns See specific information for PICalcDat() outcodes (page 132).
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● Arguments Argument Explanation tagname (string) The tag name or names matching the desired FactoryTalk Historian points for calculation. stime (string) The start time for the range of calculation (in FactoryTalk Historian time format). etime (string) The end time (in FactoryTalk Historian time format). interval (string) The interval between calculated values (in FactoryTalk Historian time format).
● ● ● FactoryTalk Historian DataLink User Guide ● ● =PIAdvCalcFilDat("cdf144","y","t","3h","'cdm158'=""Manual""", "total","time-weighted","pt. compressed","10m",50,1,4,) The result is multiplied by 1. Since point compressed is the specified sampling mode, the sampling frequency is ignored and the filter expression will be sampled at the compressed events of cdf144. The percent good is returned next to the total.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● Argument Explanation PIServer (string) The target FactoryTalk Historian server. Returns See specific information for PICalcDat() outcodes (page 132).
● ● ● FactoryTalk Historian DataLink User Guide ● ● Argument Explanation time format). etime (string) The end time (in FactoryTalk Historian time format). interval (string) The interval between calculated values (in FactoryTalk Historian time format). filtexp (string) The filter expression used to filter results. mode (string) The type of calculation to be used to compute point values. calcbasis (string) The weighting method used to calculate values.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● sampled at the combined compressed events of cdf144 and cdt158. The minpctgood is 50 percent so result will only be displayed if percent good is 50 percent or greater. The percent good is returned next to the total.
● ● ● FactoryTalk Historian DataLink User Guide ● ● An output code (page 104) of 2 transposes the output array that results from an output code of 0 and an output code of 3 transposes the output array that results from an output code of 1.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● Returns If the output code (page 104) is 0, 1, or 2 only the calculated value is returned. Outcode of 3 is not used. If the outcode is 4 or 5, the percent good is returned to the right of the value cell. If the outcode is 6 or 7, the percent good is returned below the value cell.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Returns Tag name. Example: The following returns the tag name of the tag with point ID of 123 from server named thevax. =PIPointIDToTag("123","thevax") PIAttributeMaskToTag () Retrieves a tag name corresponding to one or more point attributes, using the following syntax: PIAttributeMaskToTag(tagname, descriptor, pointsource, pointtype, pointclass, engunits, outcode, PIServer) Arguments Argument Explanation tagname (string) Tagname mask.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● An output code (page 104) of 2 transposes the output array that results from an output code of 1. Example: The following returns the tag name of the tags which start with "sin" from server named thevax.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Module Database Functions PIAliasToTag() Retrieves a tag name based on a FactoryTalk Historian point alias, using the following syntax: PIAliasToTag(alias, modulepath, querydate, outcode, PIServer) Arguments Argument Explanation alias (string) The module database alias corresponding to the desired FactoryTalk Historian point. modulepath (string) The full module path to the alias.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● PIPropertyToValue() Retrieves the value of a specified FactoryTalk Historian point property, using the following syntax: PIPropertyToValue(property, modulepath, querydate, PIServer, outcode) Arguments Argument Explanation Property (string) The module database property corresponding to the desired FactoryTalk Historian point. modulepath (string) The full module path to the property.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Input Functions PIPutVal() PIPutVal(tagname, value, time stamp, PIServer, outcell) PIPutVal supports all FactoryTalk Historian 3 data types (including string input to string tags), as well as sub-second data. Time stamps are interpreted using client machine time zone information for FactoryTalk Historian 3 unless the Use PI Server Time Zone is enabled.
● ● 9 ● FactoryTalk Historian DataLink Function Reference ● ● ● The following puts the value located in the cell A5 into the FactoryTalk Historian server of the default FactoryTalk Historian server for midnight today for the tag testtag: =PIPutVal("testtag",a5,"t",,e5) The result of the macro function is placed into cell E5 on the macro sheet.
● ● ● FactoryTalk Historian DataLink User Guide ● ● 148
Chapter 10 Technical Support and Resources Rockwell provides dedicated technical support internationally, 24 hours a day, 7 days a week. You can read complete information about technical support options, and access all of the following resources at the Rockwell Automation Support Web site (http://www.rockwellautomation.com/support/). Technical Support Please visit Rockwell Automation Customer Support Center (http://www.rockwellautomation.
● ● ● FactoryTalk Historian DataLink User Guide ● ● Worldwide Support If you are not located in North America and want to contact Rockwell Automation Support, use the Worldwide Locator (http://www.rockwellautomation.com/locations/) for worldwide contact information. Training Programs Rockwell Automation offers a wide range of training programs that include e-learning, regularly scheduled and custom-tailored classes, self-paced training, and certificate programs.
● ● 10 ● Technical Support and Resources ● ● ● When you contact Rockwell Technical Support, please provide: Product name, version, and/or build numbers. Computer platform (CPU type, operating system, and version number). Exact wording of any messages that appeared on your screen. The message log(s) at that time. Descriptions of: What happened and what you were doing when the problem occurred. How you tried to solve the problem.
● ● ● FactoryTalk Historian DataLink User Guide ● ● directory and type the subsystem name followed by the option -v (for example, piarchss.exe -v). View Computer Platform Information To view platform specifications, right-click My Computer and select Properties. For more detailed information, choose Start > Run, and type msinfo32.exe.
Index Index A About this Document • 2 Aggregate Functions • 95 Alarm Status Functions • 99 Alias to Tag • 61 Appended Data • 22 Appended Data Array • 23 Archive Value • 34 Array and Cell Limits • 107 Array Management • 67 Attribute Mask to Tag • 55 Automatic Update • 63 B Basics • 7 C Calculated Data • 43 Calculation Frequency • 63 Calculation Functions • 124 Cell References • 103 Compressed Data • 36 Configuration • 5 Consulting Services • 150 Context Menu • 14, 80 Create a Trend • 74 Current Value • 3
● ● ● FactoryTalk Historian DataLink User Guide ● ● FactoryTalk Historian Time Expressions • 87 FactoryTalk Historian Time String Examples • 88 FactoryTalk Historian Trusts • 85 Find the Version and Build Numbers • 151 Firewall Database • 84 Function Dialog Boxes • 13 Function Task Panes • 9 Functions and Array Values • 66 G Module Database Browse • 58 Module Database Functions • 60, 144 Module Database Objects • 57 Multiple Value Functions • 114 O Operators • 91 Orientation Controls • 26 Outcodes • 1
● ● Index ● ● ● ● PIPointIDToTag() • 141 PIPropertyToValue() • 145 PIPutVal() • 146 PISampDat() • 118 PISampFilDat() • 119 PIServer Argument • 103 PITagAtt() • 143 PITimeDat() • 122 PITimeExpDat() • 123 PITimeFilter() • 139 PITimeFilterVal() • 140 Point Access Permissions • 85 Point Attributes • 97 Point ID to Tag • 54 Preference Settings • 29 Property to Value • 61 R Resize Arrays • 69 Row Limitations • 107 S Sampled Data • 39 Security • 108 Setup • 83 Share Spreadsheets • 70 Show Annotations • 26 Show
● ● ● FactoryTalk Historian DataLink User Guide ● ● W Worksheet Data • 75 Worldwide Support • 150 Write Data to FactoryTalk Historian • 105 156
