Need help generating custom report to list part number prefixes

[Aaronm87]

I know this can be done with the search tool, but I'm having some trouble getting the results. I'm trying to make a custom report that can do the following.

1. Search by a prefix or sequence (preferably a drop down menu if thats possible?)
2. checkbox/option to return WT parts and/or CAD documents.

Any ideas on this one?

[MikeLockwood]

Either Search or Reports would focus on the "Number" attribute of the object type (e.g. WTPart).  In this case, Search really provides the same functionality as Reports.

I'm assuming that you are referring to prefixes assigned via OIR with auto-number, but they could be typed in by users if using manual Numbering.

For Search, the user is able to select one or both Object types.

For Reports, this is actually a bit harder to accomplish.

There is no way that I know of for the system to present a list of all the prefixes so that a user could select from them. 

If for example Numbers are like AG-8273, 756_THRN, WR8854, 923-A-66_76A, etc. the system has no idea what part of the string is a prefix.

May need to provide users with a list of all current prefixes somehow.  Sorry for not being more helpful.

[Aaronm87]

I don't mind making a report for each prefix, but I just cant find any real information on query building. Is there a guide that goes into detail on the functionality?

[avillanueva]

You can put multiple search criteria in the normal search fields separated by semicolon like this:

ABC*;123*;FOO-f*;BAR-G*

It would return all that match each of those patterns.

[Marco_Tosin]

Query builder report has always been a black box.

Over the years, a document has been built that collects various documentation on the subject

https://community.ptc.com/t5/Windchill-Tips/Resource-for-reporting/td-p/448738

It has also prompted PTC to create a HUB of information in this article

https://www.ptc.com/en/support/wnc-reporting-landing

[joe_morton]

Here's where I would start: https://www.ptc.com/en/support/article/cs138336

"QML Report to query latest version of all WTParts in Windchill PDMLink"

Once you can report on the latest version of an object, then you can start adding additional criteria.

I can't think of a good way to give a user the ability to toggle reporting between CAD and WTParts. The easiest way to do it would be 2 different reports. 

Dropdowns are possible in reports if the attribute is controlled by an Enum list. Otherwise, all you get is field to type in. 

[Aaronm87]

Is there a way to create an enum list?

[Marco_Tosin]

Take a look at the Windchill guide

https://support.ptc.com/help/wnc/r12.0.2.0/en/#page/Windchill_Help_Center%2FTypeMgrManageGlobalEnumAbout.html

[MikeLockwood]

Just adding a clarification.

For any attribute, a number of constraints can be applied - required or not, allowed list (enumerated list), has to be numbers or letters only, single value only, number of characters, etc.

Reports can utilize these constraints such that the user generating the report has these choices.

[Aaronm87]

Would it be possible to create a separate parameter that has these enumerations and then use criteria to select based off the enumerator parameter?

[joe_morton]

I did some playing around on our development system. The clean way to do it would be if you could extract the prefix into a calculated attribute, and then constrain that with an enum list, but I don't see a function that allows for extracting a substring. See: http://support.ptc.com/help//windchill/wc111_hc/whc_en/index.html#page/Windchill_Help_Center/TypeMgrAttrFormula.html

A possible way (up to you to determine if it would be worth it):

• Create a new document subtype - this will be a dummy type just to facilitate the report
• On this document, add a new attribute for your Prefixes
• Constrain the Prefixes attribute with an Enum list
• For the report to work, a document with each Enum value must exist. I would recommend making it a multi-valued attribute. You could then make a single document, and apply all values. Again, this is just a dummy to facilitate the report. Keep in mind also that if you are not bypassing access restrictions on the report, everyone who runs the report would need at least view access to this dummy document.
• Make your report to return latest WTParts (or CAD). You'd probably need to make 1 report for each.
• In those reports, add the dummy subtype to the "Tables and Joins" tab - but leave it unlinked. This is the trick. 
• Now, you can add a Constraint that is the dummy document's Prefix attribute. It will show up as a drop-down when a user goes to run the report.
• Next, you'll add a Criteria that relates the actual WTPart/CAD number prefix to the dummy document's prefix attribute (I have not fully tested this...)
  • Left side: Database function Substring (Actual Number attribute, 0, [number of characters the prefix is])
  • like
  • Right side: Database function To Character ( Report Attribute Reference > Column: dummy document's prefix attribute )

See http://support.ptc.com/help/wnc/r11.2.0.0/en/index.html#page/Windchill_Help_Center/QBSelectOrConstrainDatabaseFunction.html for details on Database Functions

Complicated.. probably too much so. Here's how it works:

Since the dummy document is not linked to the rest of the latest parts report, the report will cross every latest part with every dummy document that meets the search criteria. This is why a dummy document with each prefix must exist (otherwise, you will get no results for that prefix).

On the Criteria's left side, we're extracting the part number's prefix. We want that to be equal to whatever the user selected in the drop down. That's what we need to get on the Right side.

To access what the user set, we need to use the "Report Attribute Reference > Column: [column name]". In my testing, this didn't work on it's own. I had to wrap it in the "To Character" database function, so that it gets converted to a string.

One last thought - if your part number system ever added a new prefix, you'd have to update the enum list, and the dummy document for the reporting to work for the new prefix.

It would probably be easier to do this with saved reports. Make a saved report for each prefix. 😔

Best of luck!