Transforms

Collection Explorer General group Transforms collection

Purpose§

Defines a template for reporting, exporting and displaying data using custom data queries.

Where used§

Report/Export Wizard or, if defined within the transform, on menus.

Data fields§

General§

Name§
The name of the transform ("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§
See the Managing collections: Common attributes help topic.

XML§

A transform 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 that makes a data source. 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 specified by Microsoft for SQL Server reporting services.
  • <Provider>provider name</Provider> The Report/Export Wizard outputs a table-formatted, XML, or report output based on this code.

    Provider name (note 1) Provider ID (note 2) Description
    DataGrid   Tabular data window displays, filters and exports results within the PDXpert client
    Report1 f61035e0-a334-4978-909b-b4d4733c7654 SQL Server RDLC report with PDXpert's standard header and footer format (old version)
    Report2 7505f796-a1b5-43cc-8ee3-218f7bf6ffdc Unmodified, fully custom SQL Server RDLC report (note 3)
    Table1 8fb9e24b-bbfb-44a0-903e-d526db8e3fd0 Export tabular data file with CSV, TAB, XLS or XLSX extension (old version)
    Table2 ef098d26-2f9d-4cb1-902c-b55cc9091b00 Export tabular data file with CSV, TAB or XLSX extension
    XML e2c4e89a-db62-43ad-a718-ec08726d42b3 Export tabular data as a simple XML file

    Notes:

    1. The Provider name is case-sensitive. Use the value exactly as shown.
    2. The Provider ID may be used instead of the Provider name, and is retained only for backward compatibility with older transforms.
    3. This provider uses the complete <Report>...</Report> contents of a standard RDLC design file, excluding the <?xml ...> encoding declaration in the first line.
  • <settings /> This optional element lets options to be configured.

    • isImport="true/false" identifies whether the transform represents, respectively, an assembly (e.g., CAD BOM) import or a datagrid/export/report. If missing, the value is 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 specifies added areas where the transform is shown. The transform context is specified 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" identifies where the transform can be shown.

    • 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§

A transform can be used to show product data in a DataGrid window or printable report, or export it to a file.

The flow for output data is

  • Database SQL data query transform XML [datagrid window or export file or [RDLC report]]

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

A transform contains custom code that's not visible to PDXpert's upgrade procedure. 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 transform may require changes to maintain compatibility.

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

Import (input) transforms are supported for backwards compatibility with older PDXpert releases. Use the Standard BOM Import format instead.

Adding a new transform§

When you receive or make a new XML transform as a text file:

  1. Add a new Transforms collection member.
  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 transform window, select the XML page, and paste the text (Ctrl+V) into the XML textbox.
  5. Save the transform.

Using a transform§

After you save the new transformation, it's immediately available. For showing or exporting data, see the Use the Report/Export Wizard help topic. Selected transforms can be on context menus, as specified in the transform's <context /> element.

Designing a transform§

SQL transformations§

You can export data from an SQL query into a comma-separated value ("CSV") file, or use a SQL query as the basis for a custom report or datagrid view. 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 the current user's items with the proper code, for example, WHERE Trustee=@P0_CurrentUser.

A P0_ parameter is provided by the system, not the user. Using it in a SQL query doesn't require the parameterized="true" attribute in the <dataquery> element.

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

PLM data is complex, and the PDXpert schema is highly-normalized. For example, multi-level BOM queries that include sources, references, file metadata, materials, and cutom attributes need many joins. Where performance is a problem, use the de-normalized Matererialized… and viewer.… tables/views. If your query takes longer than a few minutes, consider upgrading your SQL Server edition and server hardware; redesigning the query to limit data, subqueries, joins and calculations; and/or copying the database to a separate instance dedicated to reporting. For serious data analytics, consider data extraction to a de-normalized data warehouse architecture.

Bulk SQL queries§

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

For example: SELECT * FROM PDXpertDB.viewer.ItemView

After writing your SQL query, add a new transform 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>DataGrid</Provider>

 <settings />

 <context />

</Definition>

To show the results, select Tools Report/Export Wizard... and select your transform.

The DataGrid provider can display approximately 100,000 cells. When using the DataGrid provider, limit the number of rows that can be returned by using TOP (n) in your SQL query. The value of n is usually 10000 or less. If users need more than 100,000 cells, use the Table2 provider to export the data.

Parameterized SQL queries§

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 (or canceled) revision example:

<Definition>

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

  <choice>

   <sqlquery>

    <sql>

     <![CDATA[

     SELECT Owner, Type, Number, Revision, Description

     FROM viewer.ItemView

     WHERE RevID=@P1

     ]]>

    </sql>

   </sqlquery>

  </choice>

 </dataquery>

 <Provider>DataGrid</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 viewer.ChangeView WHERE ItemID=@P1

     ]]>

    </sql>

   </sqlquery>

  </choice>

 </dataquery>

 <Provider>DataGrid</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 declares 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 transform 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 sequence 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 sequence 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 sequence 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 viewer.ItemView IV ON IV.RevID = LIST.Id

 

      UNION ALL

 

      SELECT

       'Change'

       ,''

       ,TypeShort

       ,Number

       ,''

       ,Summary

       ,LIST.Ordinal

      FROM

       @PLIST AS [LIST]

       INNER JOIN viewer.ChangeView CHG ON CHG.ItemId = LIST.Id

     ) ItemList

     ORDER BY

      Ordinal

     ]]>

    </sql>

   </sqlquery>

  </choice>

 </dataquery>

 <Provider>DataGrid</Provider>

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

 <context location="None" />

</Definition>

To show the resulting data:

  1. On the Tools menu, select Report/Export Wizard...
  2. Select the transform, 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.

Housekeeping columns in DataGrid§

PDXpert public views may include housekeeping columns. These are named as [HKnumber]; for example, [HK1]. In most cases, housekeeping columns are useful only to PDXpert; they aren't interesting to users, and their definition may change.

The DataGrid hides all housekeeping columns automatically. Housekeeping columns are not included when copying or exporting. If you want a housekeeping column shown in the DataGrid, you must rename the column in your SQL query, like SELECT [HK1] AS [ID1]

HK100 column§

Include an ID column named [HK100] in your SQL query to let users open a document, part, change form or collection member from the DataGrid. The column shows an Open link in the DataGrid.

The SQL column named [HK100] must contain the ItemID (not revision ID) of a document, part, or change form; or a collection member ID. If the ID cannot be matched to an item or collection member record, then nothing is opened. If [HK100] is a null value, then the Open link is not shown in that row. Do not include more than one [HK100] column in your SQL query. The link column header is always blank.

If a row contains more than one item, consider putting the Open column immediately after the selected item's number, or collection member name. This example opens the part's source:

  SELECT Number AS [Part], Number_Child AS [Source], ItemId_Child AS [HK100] FROM viewer.SourcePairView

fileSaveAs setting§

When exporting data or saving a report, the fileSaveAs="preferred file name" setting suggests a file name 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 mix 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.
{ItemRevision} Selection First selected item's revision.
{ItemLifecycle} Selection First selected item's lifecycle.
{ItemReleaseState} Selection First selected item's release state: Pending, Released, Canceled
{MemberName} All 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 the Custom attributes: Date format commands help topic. 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 show the transform at the named location. Any item that is not specified as true is ignored. See examples in the Exact parameters section of this topic.

A transform can be added 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 added to the SourcesMarkupList.

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

Location name (note 1) Description of named location Shows transform at named location GUID as SQL parameter @P1
AdministratorArea Tools menu (only users with administrator role). Transforms added 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: Current list part="true" Assembly's selected iteration
BillOfMaterialsMarkupList Part BOM: Markup list 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: Current list document="true", part="true" Item's selected iteration
ReferencesMarkupList Document or part References: Markup list document="true", part="true" Item's selected iteration
ResultsRow Item Explorer Recent and Search lists change="true", document="true", part="true" (note 3) Change item; document or part iteration (note 4)
SourcesCurrentList Part Sources: Current list part="true" Part's selected iteration
SourcesMarkupList Part Sources: Markup list 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 not case-sensitive; 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 added to any location; @P1 is declared and a value is assigned, but the query can ignore it.

If the same transform must be shown at several locations, add a copy in the Transforms collection, give it a new name, and update the copy's location value.

If an <context /> element isn't specified or its attributes are empty, then active transforms are available in the Report/Export Wizard. The user's role must Allow Transforms.

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

Help Guide Contents [as PDF]