Create a transpose function for assets

To start the wizard, in the Object Explorer pane right-click the DataT folder or the Functions folder in a database.

For more information, see also Object Explorer.

1. Select one of the following:

   • New Transpose Function > Dynamic Transpose Function (Asset)

     A dynamic transpose function automatically provides all of the attributes from the selected template.

   • New Transpose Function > Custom Transpose Function (Asset)

     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 element templates.

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

   Two types of functions are available:

   • Current hierarchy table-valued functions

     Use the functions listed below to join (cross-apply) the results of the functions with an element hierarchy table (ElementHierarchy, Element, ElementAttribute).

     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:

     • TransposeSnapshot (ElementID) retrieves current data.

     • TranposeInterpolateDiscrete (ElementID, Time) retrieves interpolated data at a particular time.

     • TransposeInterpolateRange (ElementID, StartTime, EndTime, TimeStep) retrieves interpolated data for a time range at a TimeStep frequency.

     • TransposeArchive (ElementID, StartTime, EndTime) retrieves archive data for a time range.

     • TransposeSummarize (ElementID, StartTime, EndTime, TimeStep, TimeType) retrieves summarized data for a time range at a TimeStep frequency.

   • Versioned table-valued functions

     Versioned tables are named with a v prefix. Use below listed functions to join (cross-apply) the results of the functions with a versioned element table (vElementHierarchy, vElement, vElementVersion, vElementAttribute).

     The following functions are available for this function type:

     • vTransposeSnapshot (ElementID) retrieves current data.

     • vTransposeInterpolateDiscrete (ElementVersionID, Time) retrieves interpolated data at a particular time.

     • vTransposeInterpolateRange (ElementVersionID, StartTime, EndTime, TimeStep) retrieves interpolated data for a time range at a TimeStep frequency.

     • vTransposeArchive (ElementVersionID, StartTime, EndTime) retrieves archive data for a time range.

     • vTransposeSummarize (ElementVersionID, StartTime, EndTime, TimeStep, TimeType) retrieves summarized data for a time range at a TimeStep frequency.

2. 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 element templates.

   

   Note: TransposeSummarize and vTransposeSummarize are only available for custom transpose functions.

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

3. In the Element Template window, select the element template for which you want to create a TVF and click Next.

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

   

   The list of element (AF) templates depends on the database you are working with. If the Select element template list is empty, the PI AF database to which you are connected has no PI AF element templates.

   For further details about PI AF element templates, see the PI System Explorer User Guide available at the AVEVA Customer Portal Products page or the PI System Explorer Help.

4. 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 element 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 element template that was selected in the previous step. The order in which the element template attributes are selected determines how the function is created. Ensure that you select the element 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

5. 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.

6. 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 don't 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.

   

7. Click Next.

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

   

8. 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