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 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):

• <Provider>provider name</Provider> The transform creates a DataGrid window; structured export file; or formatted report.

  Provider name (note 1)	Provider ID (note 2)	Description
  DataGrid		Tabular data window displays, filters and exports results within the PDXpert client
  PDXExport		PDX package export content definition
  Report1	f61035e0-a334-4978-909b-b4d4733c7654	SQL Server RDLC report with PDXpert's standard header and footer format (Obsolete)
  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. Obsolete: Use the Table2 provider. This provider uses Microsoft OLEDB, which is replaced by OpenXML. Use only when Excel 97-2003 files are required.
  Table2		Export tabular data file as CSV, TAB or XLSX (using OpenXML)
  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. Do not use the Provider ID for new transforms. Edit older transforms to use the Provider name.
  3. This provider uses the complete <Report>...</Report> contents of a standard RDLC design file, excluding the <?xml ...> encoding declaration in the first line.

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

• <dataquery xmlns="http://www.plmx.org/DataQuery.xsd"></dataquery> This contains 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.

• <settings /> This element configures options.

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

  • PDX packages contain one or more <kvp key="..." value="..." /> elements that adjust the package content. See PDX export transforms setting below.

• <xsl:stylesheet></xsl> The optional XSL instructions transform the source data into the appropriate import/export file format.

Setup §

Restrict roles that can add or change members of the Transforms collection. Transforms directly access your database, and inexperienced or malicious users can damage or destroy your data.

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 backward 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 transform, 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 transforms

  • Bulk SQL queries

  • Parameterized SQL queries

  • Housekeeping columns in DataGrid

  • <settings fileSaveAs=… /> setting

  • <context /> element

• PDX export transforms

  • Key-value pairs

  • Key path Actions and Value options

  • Creating a PDX export transform

• PLMX transforms

SQL transforms §

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.

Do not use transforms to update native PDXpertDB tables. Updates to native tables may affect data synchronization or cache coherence that result in client "can't save" transaction errors. However, a transform can create, update and delete temporary tables to, for example, aggregate data for reporting.

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

The DataGrid window has a text filter that looks at all values within each row. If row filtering doesn't work as expected, try formatting each column's data as text in the final SQL SELECT statement. Apply CAST() or CONVERT() functions to numeric, date, or other non-text values; convert NULL values to an empty string.

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:

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 §

Parameterized queries using @Pn or @PLIST must always include <dataquery parameterized="true" …

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:

Change form example:

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:

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.

<settings 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 2022-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: iteration's lifecycle, or change form's full lifecycle name.
{ItemReleaseDatetime}	Selection	First selected item: iteration's (SQL BasicItemRevision.ReleaseDate) or change form's (SQL Change.ReleaseDate) release date for correctly identifying the file import sequence after release (note 3).
{ItemReleaseFrom}	Selection	First selected item: iteration's release marker (SQL BasicItemRevision.FromNum) or internal change event number (SQL Change.ChangeNum) for correctly identifying the file import sequence after release (note 5).
{ItemReleaseState}	Selection	First selected item: iteration's release state (Pending, Released, Canceled) or change form's short lifecycle state enumeration (ORG, SUB, ..., CMP).
{MemberName}	All	Transform's member 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. A date without format is UTC as @yyMMdd-HHmmss; for example, 220523-140321 (UTC). An invalid date format causes an empty date. Invalid file system characters (like / and :) 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

5. Number of digits is specified using .NET Decimal ("D") formatter; for example, {ItemReleaseFrom@D6} formats event 1484 as 001484. Allowed range is D1 to D9, default value without a formatter (or with invalid format specifier) is D6.

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
AppearsOnBillOfMaterialsList	Part Appears On: BOM list	part="true"	Item's selected iteration
AppearsOnChangeFormsList	Document or part Appears On: Changes list	document="true", part="true"	Item's selected iteration
AppearsOnReferencesList	Document or part Appears On: References list	document="true", part="true"	Item's selected iteration
AppearsOnSourcesList	Part Appears On: Sources list	part="true"	Item's selected iteration
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.

PDX export transforms §

The PDX file format is defined by industry standard IPC-2570. A PDX export transform defines standard XML elements and attributes that are included or excluded.

The general format contains the Provider, context, dataquery and settings elements (exactly as shown). Within the settings, there is a set of one or more key-value pair <kvp …> elements:

Key-value pairs §

A key-value pair consists of:

• Key A unique path of IPC elements, followed by an action.

  • The path is a series of element names or abbreviations, separated by a period/full stop. For example, an IPC-defined Document element has a standard attribute globalProductTypeCode, thus the path to the action is Document.StdAttr.globalProductTypeCode

    Base path values: Change, Document, ManufacturerPart, Part, PDXPackage

    Intermediate path values: AddAttr, Attachment, BillOfMaterial, Custom, Material, StdAttr

    Only paths exported by the Export PDX Package dialog are supported.

  • The action defines a result. For example, the action at the end of path Document.StdAttr.globalProductTypeCode can be :Load (include the path object), or :Apply (replace the path object's standard value).

  Keys are fixed; you can only create a new key as a path to your custom attribute, and that path's action is always :Load.

• Value The desired result of the path's action. Values are usually one selection from a fixed set of choices, such as Yes / No, or Include / Exclude. Some values may be a numeric range (:Limit 1 to 20) or user-defined text (:Apply My text value).

Key path Actions and Value options §

Action	Purpose	Value options
:Apply	Assign text to attribute	Any text
:Enable	Use feature option	Yes No
:Filter	Specify whether a loaded value includes all, a subset, or none	Include Exclude NameOnly (note 2)
:Limit	Defines an integer value for BOM levels	From 1 to 20
:Load	Load path object(s) into package	Yes No
:Select	Specifies the set of objects that will be used by the :Filter	Comma-delimited list, such as part type names (note 3) or file types (note 4)

Notes:

1. This table describes PDX transform syntax, but is rarely needed. If you create the template using the instructions below, all values are automatically correct.

2. The NameOnly option is used only for file types. It specifies that only the file name and other metadata, not the actual file, is included in the package (the IPC attribute isFileIn is No).

3. If the object name has an embedded comma (for example, a part type named "Product,Final"), then use one of these characters to separate all items in the list: !?;|  If you use one of these characters, it must be the first character in the list, and cannot be used within any of the listed items: ;Assembly;Product,Final;Design  The Export PDX Package dialog automatically handles this for you.

4. Use file extensions without the "dot" separator: use pdf,txt,png not .pdf,.txt,.png

Creating a PDX export transform §

To create a new PDX export transform:

1. Open an item record, and select the Export PDX package command from the context menu.

2. Select a useful starting transform in the Select PDX starting values dropdown list at the bottom center of the dialog. Use the settings in the Export PDX Package dialog to add your changes.

3. Click the Save As… button to export the settings.

4. Add the file's content as a new Transforms collection member (see Adding a new transform, above).

   Do not change the file's <kvp key={path:action} text. Assign value={option} only according to the table above. If the key or option value is unexpected, the export file will use the default value. Default values can be viewed by saving the — PDX Standard Package — transform.

PLMX transforms §

The PLMX transform uses an XML/XSLT format for product data objects. Although PLMX is very flexible, XSLT code complexity and slow performance made these less popular than standard SQL datasets.

PLMX transforms are not actively supported, although some functions remain available for backward compatibility. PLMX example transforms that were included in early PDXpert releases have been replaced with improved SQL-based versions. These replacement transforms are available for download in the PDXpert software release notes.

2046