Data transformations

Purpose

Contains XSLT templates for transforming the standard PDXpert XML file format, called PLMX, to other data formats; may include optional SQL-type query and report (RDLC) code.

Where used

Report/Export Wizard, item BOM tab's Markup list.

Data fields

General tab

Name
The name of the data transformation ("transform") template.
Description
A summary of how the data contained in the transform is used.
Active: users can select
Default member of collection
Permanent member of collection
For a description of these checkboxes, see the Collections reference > Managing collections help topic.

XML tab

A data transformation consists of the following sections (element and attribute names are case-sensitive unless noted otherwise):

  • <xsl:stylesheet></xsl> The optional XSL instructions transform the source data into the appropriate import/export/report file format.
  • <dataquery xmlns="http://www.plmx.org/DataQuery.xsd"></dataquery> This is an optional database SQL query, which creates a data source for the transform. If the attribute parameterized="true" is included, then the SQL statement can include parameters in the WHERE clause to select a document or part revision GUID, or a change form GUID. See syntax and examples in Parameterized SQL queries below.
  • <Report>RDLC code</Report> The optional Report Definition Language (Client-side) code is an XML-based report definition created by Microsoft for SQL Server reporting services.
  • <Provider>provider name</Provider> The Report/Export Wizard creates a table-formatted, XML, or report output based on this code.

    Provider name Provider ID (note 1) Description
    Report1 f61035e0-a334-4978-909b-b4d4733c7654 SQL Server RDLC report with PDXpert's standard header and footer format
    Report2 7505f796-a1b5-43cc-8ee3-218f7bf6ffdc Unmodified, fully custom SQL Server RDLC report (note 2)
    Table1 8fb9e24b-bbfb-44a0-903e-d526db8e3fd0 Tabular import/export data file with CSV, TAB, XLS or XLSX extension
    Table2 ef098d26-2f9d-4cb1-902c-b55cc9091b00 Tabular export data file with CSV, TAB or XLSX extension
    XML e2c4e89a-db62-43ad-a718-ec08726d42b3 Export an XML file

    Notes:

    1. The Provider ID may be used instead of the Provider name, and is retained for backward compatibility with older transforms.
    2. This provider excludes the <?xml...?> encoding declaration in the first line of an RDLC design file, but otherwise uses the complete <Report>...</Report> contents.
  • <settings /> This optional element allows options to be configured.

    • isImport="true/false" indicates whether transform represents, respectively, an assembly (e.g., CAD BOM) import or a data file export/report. If absent, value is assumed false.

    • fileSaveAs="preferred file name" suggests a file name for saving the export file or report. See syntax and examples in fileSaveAs setting below.

  • <context /> This optional element defines where, in addition to the Report/Export Wizard, the export or report transform is displayed. The transform context is defined by a location, and one or more objects that contain the location and are compatible with the transform. See <context /> element setting below.

    • location="name" indicates where the transform can be displayed.

    • part="true", change="false", etc. identifies the objects that are compatible with the export file or report. This also defines the parameter that's passed to the transform's query: item revision GUID or change item GUID.

Setup suggestions

A data transformation can be used to import, export or report product data formatted as XML, HTML, CSV, XLS or other text files.

The data flow for

  • Exports: Database » SQL data query » data transformation XML » [export file or [RDLC » report]]
  • Imports: BOM import file » data transformation XML » database

After upgrading to a new PDXpert release, always test your data transformations for proper operation.

A data transformation contains custom code that's not visible to PDXpert's upgrade process. It's impossible to ensure that a transform will remain forward-compatible with future PDXpert upgrades. An upgrade may contain new features that removes the need for an existing custom report or export. In some cases, a data transformation may require changes to maintain compatibility.

Data transformations included with your PDXpert system are provided to demonstrate initial capabilities, and may need updating for compatibility with future PDXpert releases.

Adding a new Data Transformation

When you receive or create a new XML data transformation as a text file:

  1. In the Collection Explorer, create a new Data Transformations collection member, and assign it a Name.
  2. Open the text file in a plain text editor like Notepad or Notepad++. Do not open or edit the file in a word processing application like Wordpad or Word.
  3. Select the entire contents (Ctrl+A), and copy (Ctrl+C) the text to the Windows Clipboard.
  4. On the data transformation window, paste the text (Ctrl+V) into the XML textbox.
  5. Lock the transform to save it.

Using a Data Transformation

After you save the new transformation, it's immediately available.

Designing a data transformation

SQL transformations

You can export data from an SQL query that extracts data from a public view into a comma-separated value ("CSV") file, or use a SQL query as the basis for a custom report. The query can search all database objects (a bulk query) or focus on objects selected by the user (a parameterized query).

The system always includes a SQL parameter, @P0_CurrentUser, for personalizing a bulk or parameterized SQL query. This is the SQL uniqueidentifier of the person who executes the query. For example, a query to export all items can instead export a user's items with the proper code, for example, WHERE Trustee=@P0_CurrentUser.

Do not declare variables beginning with the characters @P0… in your data transformation. These variables are reserved.

Bulk SQL queries

A SQL statement can be used to export bulk information in defined columns, with criteria and sorting as specified within the statement.

For example: SELECT * FROM [PDXpertDB].[viewer].[ItemView]

After creating your SQL query, create a new Data Transformation collection member, and embed the query within this XML framework:

<Definition>

 <dataquery xmlns="http://www.plmx.org/DataQuery.xsd">

  <choice>

   <sqlquery>

    <sql>

     <![CDATA[

     replace this line with a SQL SELECT statement

     ]]>

    </sql>

   </sqlquery>

  </choice>

 </dataquery>

 <Provider>8fb9e24b-bbfb-44a0-903e-d526db8e3fd0</Provider>

 <settings />

 <context />

</Definition>

To export the resulting data as a CSV file, select Tools | Report/Export Wizard... and select your transform.

Parameterized SQL queries

Do not declare a variable @PLIST or variables beginning with the characters @P0… to @P9… in your data transformation. These variables are declared automatically by PDXpert to identify reserved parameters and user-selected items.

Exact parameters

A parameterized SQL statement can output results from an item selected using the Report/Export Wizard. The syntax for a part or document is RevID=@P1 or PendingRevID=@P1. For a change form, use ItemID=@P1.

Part or document released revision example:

<Definition>

 <dataquery parameterized="true" xmlns="http://www.plmx.org/DataQuery.xsd">

  <choice>

   <sqlquery>

    <sql>

     <![CDATA[

     SELECT Owner, Type, Number, Revision, Description

     FROM [PDXpertDB].[viewer].[ItemView] WHERE RevID=@P1

     ]]>

    </sql>

   </sqlquery>

  </choice>

 </dataquery>

 <Provider>8fb9e24b-bbfb-44a0-903e-d526db8e3fd0</Provider>

 <settings fileSaveAs="Summary of Released {ItemTypeShort} {ItemNumber}" />

 <context location="TopLevel" part="true" document="true" />

</Definition>

Change form example:

<Definition>

 <dataquery parameterized="true" xmlns="http://www.plmx.org/DataQuery.xsd">

  <choice>

   <sqlquery>

    <sql>

     <![CDATA[

     SELECT Type, Number, Summary

     FROM [PDXpertDB].[viewer].[ChangeView] WHERE ItemID=@P1

     ]]>

    </sql>

   </sqlquery>

  </choice>

 </dataquery>

 <Provider>8fb9e24b-bbfb-44a0-903e-d526db8e3fd0</Provider>

 <settings fileSaveAs="Change Summary {ItemNumber}" />

 <context location="TopLevel" change="true" />

</Definition>

You can specify more than one item parameter in your query. As each item is added to the Report/Export Wizard, the system automatically creates a new T-SQL variable as DECLARE @Pn uniqueidentifier = {objId};, where n is 1 or higher. An error occurs if the user selects fewer items that your query requires. For example, if your query refers to @P1, @P2 and @P3 and the user drops only two items onto the Wizard, the query's reference to @P3 fails.

Table parameters

If you're not sure how many items a user will want to include, you can design a data transformation query that uses a SQL table variable. This table, declared as @PLIST, contains a list of part revision, document revision and change item identifiers. The table has two columns, and an unlimited number of rows:

  • The Ordinal column (SQL numeric data type int, starting at 0) contains the order that a part, document or change form is dropped onto the Report/Export Wizard. Use this value if it's important for the query to know the order that items are added (for example, the primary assembly of a BOM comparison).
  • The Id column (SQL data type uniqueidentifier) contains the revision identifier (if part or document) or change form's item identifier. To select the data records related to the user's items, your query must JOIN other tables or views to this column.

This transform accepts any number of released part and document iterations, and any number of change forms, and sorts them in the order they're added to the Report/Export Wizard:

<Definition>

 <dataquery parameterized="true" xmlns="http://www.plmx.org/DataQuery.xsd">

  <choice>

   <sqlquery>

    <sql>

     <![CDATA[

     SELECT

      [Class]

      ,[Owner]

      ,[Type]

      ,[Number]

      ,[Iteration]

      ,[Description]

     FROM

     (

      SELECT

       CASE [Class] WHEN 1 THEN 'Part' ELSE 'Document' END AS [Class]

       ,[Owner]

       ,[TypeShort] AS [Type]

       ,[Number]

       ,[Revision] + ' @ ' + [Lifecycle] AS [Iteration]

       ,[Description]

       ,LIST.[Ordinal]

      FROM

       @PLIST AS LIST

       INNER JOIN [PDXpertDB].[viewer].[ItemView] IV ON IV.RevID = LIST.Id

 

      UNION ALL

 

      SELECT

       'Change'

       ,''

       ,[TypeShort]

       ,[Number]

       ,''

       ,[Summary]

       ,LIST.[Ordinal]

      FROM

       @PLIST AS [LIST]

       INNER JOIN [PDXpertDB].[viewer].[ChangeView] CHG ON CHG.ItemId = LIST.Id

     ) ItemList

     ORDER BY

      [Ordinal]

     ]]>

    </sql>

   </sqlquery>

  </choice>

 </dataquery>

 <Provider>8fb9e24b-bbfb-44a0-903e-d526db8e3fd0</Provider>

 <settings fileSaveAs="Selected Items {FileDatetime}" />

 <context location="None" />

</Definition>

To export the resulting data as a CSV file:

  1. On the Tools menu, select Report/Export Wizard...
  2. Select the data transformation, and click the Next > button.
  3. Drag one or more items from the Item Explorer and drop onto the Report/Export Wizard. Click the Finish button.

fileSaveAs setting

When exporting data or saving a report, the fileSaveAs="preferred file name" setting defines the name that's shown to the user. The user can edit or replace the file name before the file is saved.

The suggested file name value can be simple text like fileSaveAs="Released parts", which is shown in the Save As dialog as Released parts.

You can combine text with current values; for example, "Saved on {FileDatetime@dddd} by {FileCreator}" becomes Saved on Friday by Pat Lee in the Save As dialog.

Value name (note 1) Applies to (note 2) Value inserted into file name
{FileCreator} All Current user's name from the Persons collection.
{FileDatetime} All Current local date and time as year-month-day hour-minute, like 2017-06-21 15-22 (note 3).
{ItemClass} Selection First selected item's class: Change, Document or Part
{ItemName} Selection First selected item's name / description of up to 100 characters (note 4).
{ItemNumber} Selection First selected item's number.
{ItemOwner} Selection First selected item's owner name from the Organizations collection.
{ItemOwnerShort} Selection First selected item's owner abbreviation (display name) from the Organizations collection.
{ItemType} Selection First selected item's part type, document type or change form.
{ItemTypeShort} Selection First selected item's part type, document type or change form as an abbreviation.
{MemberName} All Current transform's name.

Notes:

  1. The {ValueName} is case-sensitive, and must be used exactly as shown. Use only one instance of a value name in a template.

  2. If the user selects items to include in the output, data from the first selected item is inserted. Otherwise, these value names are ignored.

  3. You can use standard Windows .NET datetime formats as {FileDatetime@datetime format} or, for UTC, {FileDatetime@datetime formatZ}. See Custom attributes > Date format commands. Invalid file system characters are converted after the .NET format is applied.

  4. You can limit the text length as {ItemName@maximum}, where maximum value is 1-99. For example: {ItemName@20} limits the name Power Chassis Assembly to Power Chassis Assemb

If there's no fileSaveAs= setting, then the file name is the first selected item (formatted as fileSaveAs="{ItemOwnerShort} {ItemNumber} ({ItemTypeShort})") or the transform's name (as fileSaveAs="{MemberName}"). Use fileSaveAs=" " (with space character) to force an empty file name.

Invalid file system characters (such as / and :) are replaced by an underscore (_) character, and leading/trailing space characters are removed. The complete file name is limited to 200 characters, and the operating system may shorten the name further. Try to design the file name for fewer than 80 characters.

<context /> element

The location="name" setting defines where the export or report transform is shown. One or more objects (change, document, part, system) will display the transform at the named location. Any item that is not specified as true is ignored. See examples in Parameterized SQL queries > Exact parameters, above.

Any data transformation can be assigned to any location; it's the transform query, not the location, that defines its output. A transform designed to export released BOM data won't start exporting pending source data after it's assigned to the SourcesMarkupList.

A parameterized query needs an item as the named location (for example, part="true"). Only assign bulk queries to locations where system="true".

Location name (note 1) Description of named location Displays transform at named location GUID as SQL parameter @P1
AdministratorArea Tools menu (only users with administrator role). Transforms assigned to this location will not be shown in the Report/Export Wizard. system="true" none
AffectedItemsList Change form Affected list change="true" Change item
BillOfMaterialsCurrentList Part BOM list, for Current iteration part="true" Assembly's selected iteration
BillOfMaterialsMarkupList Part BOM list, for Markup iteration part="true" Assembly's selected iteration
MaterialsList Part Materials list part="true" Part's selected iteration
None  Report/Export Wizard tool (note 2) none none
ReferencesCurrentList Document or part References list, for Current iteration document="true", part="true" Item's selected iteration
ReferencesMarkupList Document or part References list, for Markup iteration document="true", part="true" Item's selected iteration
ResultsRow Item Explorer Previous and Search lists change="true", document="true", part="true" (note 3) Change item; document or part iteration (note 4)
SourcesCurrentList Part Sources list, for Current iteration part="true" Part's selected iteration
SourcesMarkupList Part Sources list, for Markup iteration part="true" Part's selected iteration
TasksList Document, part, or change form Tasks list change="true", document="true", part="true" Change item; document or part selected iteration
TopLevel All supported locations on document, part, or change form change="true", document="true", part="true" Change item; document or part selected iteration
UserArea Tools menu system="true" none

Notes:

  1. The location name is case-insensitive; for example, you may use TopLevel, toplevel or TOPLEVEL.

  2. Optional. This is the same as not including a <context /> element in the transform.

  3. If more than one item is selected in the Item Explorer results list, then only transforms supported by every selected item are shown.

  4. If a part or document has several iterations, then the released iteration is selected. If there's no released iteration, the pending iteration is selected. If there's no pending iteration, the most recent canceled iteration is selected. This sequence can be changed in the transform's SQL query by deriving the item from its iteration, and then selecting the preferred iteration.

Bulk (non-parameterized) transforms can be assigned to any location; @P1 is declared and assigned, but the query can ignore it.

If the same transform must be shown at several locations, create a copy in the Data Transformations collection, assign a new name, and update the copy's location value.

If an <context /> element isn't defined or its attributes are empty, then active transforms are available in the Report/Export Wizard. The user's assigned role must allow Data Transformations.

PLMX transformations

PLMX is no longer used for output files, and is maintained for backward compatibility only.

PLMX contains an XML representation of product data objects. PLMX may be used to import BOMs into assembly markups by mapping CAD fields to PDXpert part attributes.

2046

Learn More
Help Guide Contents [PDF]