Manage PI DataLink settings from Excel

Use the Settings window to specify global preferences and default output formatting for PI DataLink functions. These settings apply to a particular user on a particular computer. From this window, you can also clear the cache of configuration data for PI points and non-time-based data from PI AF.

1. On the PI DataLink tab, in the Resources group, select Settings.

2. To clear the PI DataLink cache manually, select Clear Cache.

   By default, PI DataLink caches configuration data for PI points and non-time-based data from PI AF (that is, data other than PI point data references and events). This cache improves performance. Before calculating a function, PI DataLink checks when the cache was last cleared; if the cache has not been cleared within the last six hours, PI DataLink automatically clears the cache. To clear the cache and obtain updated data sooner, clear the cache manually.

3. To update settings, enter the preferred setting and select OK.

   Setting	Description
   Display #N/A instead of blanks	Select to show #N/A (not applicable) rather than blanks in cells without values when a recalculation returns fewer values than a function array can show. This feature is particularly useful if you use the Excel charting package to plot function results.
   Locale independent	Select to interpret input time strings according to PI time format rules, regardless of the locale settings of the client workstation. PI time assumes all strings are English and use the date-time format dd-mmm-yyyy hh:mm:ss. Clear the check box to parse date-time formats according to regional settings on the client workstation, falling back to PI time format rules only if necessary.
   Disable automatic task pane display on select	Select to turn off the automatic opening of the function task pane when you select a function cell.
   Disable "Resize to show all values" message	Select to display no warning message when a recalculation returns more values than a function array can show.
   Client time zone	Select to interpret the input time stamps and display output time stamps in the client-workstation time zone. Some functions will display certain time stamps in the Data Archive server time zone. See Time-zone-setting limitations for these exceptions.
   PI Data Archive time zone	Select to interpret the input time stamps and display output time stamps in the Data Archive server time zone. This setting only applies to data items or expressions that contain either a PI point or a PI AF attribute that is a PI point data reference. If the data item or expression contains a PI AF attribute that is not a PI point data reference, PI DataLink interprets the input and output time stamps as being in the client-workstation time zone. This option is relevant only if a Data Archive server has a time-zone setting different from the client workstation.
   UTC time zone	Select to interpret the input time stamps and display output time stamps in coordinated universal time (UTC). Some functions do not support this setting. See Time-zone-setting limitations for details about these exceptions.
   In a row	Select to paste multiple results from a search into a row of values in the worksheet.
   In a column	Select to paste multiple results from a search into a column of values in the worksheet. Default value.
   Number format	Enter the format for numbers in function output. The format string may be any valid number format code from the Excel Format window (see Display formats). During installation, PI DataLink enters the default number format from Excel in this field, unless you have set a different format preference previously in PI DataLink on this computer. If your version of Excel runs a different locale, this format includes correct syntax to reflect the locale. For example: 35.03 would appear as 35,03 in French Excel.
   Time format	Enter the format for time stamps in function output. The time format string may be any valid date-time format code from the Excel Format window. During installation, PI DataLink enters the standard PI time stamp format in this field, unless you have set a different format preference previously in PI DataLink on this computer. If your version of Excel runs a different locale, this format includes correct syntax to reflect the locale. For example: dd-mmm-yy hh:mm:ss would appear as jj-mmm-aa hh:mm:ss in French Excel.
   Maximum event count	Enter the maximum number of events that the Explore Events and Compare Events functions return to the preview and to the worksheet.
   Maximum filter search count	Enter the maximum number of elements that the Asset Filter Search function returns to the worksheet, except when you specify an attribute-value filter for an attribute with a data reference. In this case, enter the total number of elements among which you want to search for an attribute with a data reference; note that if you set this number too low, the function might return fewer results than expected.
   Calculate (F9)	Select to recalculate all volatile functions (and any functions that reference them) during each recalculation that the Automatic Update feature initiates. The Current Value function is the only volatile PI DataLink function.
   Full calculate (Ctrl+Alt+Shift+F9)	Select to recalculate all functions, regardless of volatility, during each recalculation that the Automatic Update feature initiates.
   Interval (seconds)	Enter the number of seconds between each recalculation that the Automatic Update feature initiates. The minimum value is five seconds. Enter 0 to have PI DataLink compute an automatic interval based on the duration of previous calculation times.