Create a transpose function for event frames

Select one of the following:

New Transpose Function > Dynamic Transpose Function (Event Frame)

Note: A dynamic transpose function automatically provides all of the attributes from the selected template.
New Transpose Function > Custom Transpose Function (Event Frame)

Note: A custom transpose function requires you to select specific attributes for the selected template.

The Transpose Function Wizard is displayed. Use the transpose function list to create one or more TVFs for the selected PI AF event frame templates.

For details about the element hierarchy and versioning, see the PI OLEDB Enterprise User Guide available at the AVEVA Customer Portal Products page.

One type of function is available:

Current hierarchy table valued functions

Use the functions listed below to join (cross-apply) the results of the functions with an event frame hierarchy table (EventFrameHierarchy, EventFrame, EventFrameAttribute).

For details, see PI OLEDB Enterprise E-R model or Compendium of PI SQL Statements for PI AF in the PI OLEDB Enterprise User Guide.

The following functions are available for this function type:

TransposeEventFrameSnapshot (EventFrameID) retrieves current data.
TranposeEventFrameInterpolateDiscrete (EventFrameID, Time) retrieves interpolated data at a particular time.
TransposeEventFrameInterpolateRange (EventFrameID, StartTime, EndTime, TimeStep) retrieves interpolated data for a time range at a TimeStep frequency.
TransposeEventFrameArchive (EventFrameID, StartTime, EndTime) retrieves archive data for a time range.
TransposeEventFrameSummarize (EventFrameID, StartTime, EndTime, TimeStep, TimeType) retrieves summarized data for a time range at a TimeStep frequency.

In the Transpose Function window, select the transpose function that you want to implement and click Next.

Note: A dynamic transpose function allows you to select multiple transpose function templates and event frame templates.

Note: TransposeSummarize is only available for custom transpose functions.

For more information, see Create a TransposeSummarize or vTransposeSummarize function.

In the Event Frame Template window, select the event frame template for which you want to create a TVF and click Next.

From here you can always click Back to modify your previous action.

Note: The list of event frame templates depends on the database you are working with. If the Select event frame template list is empty, the PI AF database to which you are connected has no PI AF event frame templates. For further details about PI AF event frame templates, see the PI System Explorer User Guide available at the AVEVA Customer Portal Products page or the PI System Explorer Help.

Define your transpose function by choosing the appropriate method to apply element template attributes:

If you selected the dynamic transpose function, select the path that you want all your attributes to have. If you want to include the child attributes, select Include subtree. Click Next.
If you selected the custom transpose function, select the event frame template attributes you want to use by dragging each element and dropping it into the right-hand box. You can also double-click the element to add it. Click Next when finished.

Note: The list of available attributes depends on the event frame template that was selected in the previous step. The order in which the event frame template attributes are selected determines how the function is created. Ensure that you select the event frame template attributes in the order in which they will appear. If you make a mistake, you can delete the attributes and re-add them in the correct order. To delete an attribute, select it and press the Delete key or right-click and click Delete.

Note: If you had created a transpose function previously that used the same name as the function you are now creating, a warning sign will indicate A database object with the name 'name' already exists. If this occurs, you should either provide a new name for the function or exit the wizard and drop the existing function. For more information about how to set attributes for those functions, see Create a TransposeSummarize or vTransposeSummarize function.

Optional: you can change the column name of the attribute by selecting it and entering a new name. Click Next when finished.

The column name is the name that will be used in the new function. You can rename your columns as you wish as long as the column names are distinct.

The Show hidden attributes check box is active only if there are hidden attributes.

Select Values as VARIANT or Create function tables or both and click Next.

Function tables offer alternative syntax for calling table-valued functions (TVFs). They are intended to be used from third-party clients which do not allow you to execute queries containing TVFs directly (for example, SQL Server).

If Values as VARIANT is selected, then the resulting tables and functions also support OPTION (EMBED ERRORS). Transpose tables and functions support OPTION (IGNORE ERRORS).

Note: If you did not select the checkbox to create the function table, or if the function table was deleted, you can create it after the fact without going through the wizard by right clicking the function and selecting Create Transpose Function Table.

A summary of the options you selected in the previous steps is displayed in the next window.

Click Next.

The function name and the selected event frame template are displayed in the next window.

Click Execute to run the DML (data manipulation language).

The DML creates the function and also the function table if the option was selected. Subsequently, either one of the following is displayed:

indicates a successful creation of function and function table.
indicates an unsuccessful creation of function and function table.

Successful creation of function and function table

PI SQL Commander Lite

Create a transpose function for event frames

Table of Contents

Create a transpose function for event frames

Related Links