How to determine the number of unique part numbers site wide in Windchill?

[JimTVancouverCa]

Hello all,

In order to support a business decision, I have need to determine a unique count of drawings, parts, and assemblies.  PTC support has offered the following sql query, but the numbers don't agree with advanced search results in the Windchill interface using the following search:

Criteria:

Type=CAD Document;Number=*.prt;Iteration=Latest;Revision=Latest;

Context=All Contexts

The sql PTC support suggested was:

select count(*) from epmdocument;

This gives total number of Creo Parts, Assemblies, and Drawings.

select count(*), mas.doctype from epmdocument epm, epmdocumentmaster mas where epm.ida3masterreference=mas.ida2a2 group by mas.doctype;

This cagetorizes into Parts, Assemblies, and Drawings.

Anyone have any experience with a similar objective?

Thanks again all.

[MikeLockwood]

Query Builder Report

- Type: EPMDocumentMaster

- Criteria: Number like *.PRT, *.ASM, *.DRW (maybe also *.FRM, *.LAY) - make parameter, change and run multiple times

- Select: function: COUNT(Persist Info, Object ID, ID)

[JimTVancouverCa]

Hello Mike,

Sorry dumb question but... this is what I see in attributes to add:

I haven't had the time to study the query building at any length.

Thanks again.

[MikeLockwood]

Use EPM Document Master rather than EPM Document Master Key

[JimTVancouverCa]

Doh!  That works much better.

[TomU · Accepted solution]

The EPMDocumentMaster table will contain a single entry for each CAD object.  The EPMDocument table will contain an entry for each unique version (iteration/revision).  To simply count all the unique CAD objects, you can use something like this:

SELECT COUNT(*) FROM windchill.EPMDocumentMaster WHERE authoringApplication='PROE';

To count just the unique part, assemblies, or drawings, simply add a wildcard for the CADName field:

SELECT COUNT(*) FROM windchill.EPMDocumentMaster WHERE authoringApplication='PROE' AND CADName LIKE '%.asm';

SELECT COUNT(*) FROM windchill.EPMDocumentMaster WHERE authoringApplication='PROE' AND CADName LIKE '%.prt';

SELECT COUNT(*) FROM windchill.EPMDocumentMaster WHERE authoringApplication='PROE' AND CADName LIKE '%.drw';

To count the total number of versions of all CAD objects, you need to link the two tables:

SELECT COUNT(*) FROM windchill.EPMDocumentMaster edm, windchill.EPMDocument ed WHERE ed.idA3masterReference = edm.idA2A2 AND authoringApplication='PROE';

[TomU]

That second query support gave you is actually pretty nice.  I added the "authoringApplication='PROE' and an ORDER BY to clean up the results a bit:

select COUNT(*), mas.docType
FROM windchill.EPMDocument epm, windchill.EPMDocumentMaster mas
WHERE epm.idA3masterReference = mas.idA2A2 AND authoringApplication = 'PROE'
GROUP BY mas.docType
ORDER BY mas.docType;

[JimTVancouverCa]

It's a good query, I asked back to PTC Support, are those all iterations of each docType of EPMDocument or are they unique parts?  Waiting on that answer.  For your case above do you have 354 388 Creo drawings?

[TomU]

That one was querying the EPMDocument table, so that was the total number of versions, not part numbers (or Creo unique file names).  To get unique objects, with different file names, just remove the EPMDocument stuff:

select COUNT(*), mas.docType
FROM windchill.EPMDocumentMaster mas
WHERE authoringApplication = 'PROE'
GROUP BY mas.docType
ORDER BY mas.docType;

[JimTVancouverCa]

Okay so we have two different methods.  I have attached qml files for the queries.  Hopefully I've built them correctly.

SQL all versions:

select count(*), mas.doctype from epmdocument epm, epmdocumentmaster mas where epm.ida3masterreference=mas.ida2a2 group by mas.doctype;

SQL unique parts courtesy Tom Uminn‌

select COUNT(*), mas.docType FROM EPMDocumentMaster mas WHERE authoringApplication = 'PROE' GROUP BY mas.docType ORDER BY mas.docType; 

(I had to remove Windchill from Windchill.EPMDocumentMaster)

I receive different counts than from the queries.  Further the queries don't add up either:

	Creo Drawing	Creo Part	Creo Assembly
SQL	55939	350008	67909
SQL Unique	8763	42597	8759
Report Builder Query	8763	42599	8759

The # drawings should roughly = # parts + # assemblies should it not?  Given there will be test parts and 'what if' assemblies for which no drawing will be present but the results I have above don't seem to make sense.

Can anyone think of a way to do a sanity check to make sure these counts are correct for the purpose of finding the number of unique parts, assemblies and drawings?  Or is it just likely the co-incidence that our # of drawings is only slightly higher than # of assemblies?

Can I build a query that shows the number of parts that have a drawing, number of assemblies that have a drawing etc.?

Best Regards,

- Jim

[TomU]

Your query builder report is returning unique document masters, not every version of each document.

Fundamentally which are you looking for, total number of all object versions in the system, or just the total number of unique objects (filenames)?

[JimTVancouverCa]

Hello Tom,

Thanks for your input on this question.

I'm looking for the total number of each of drawing, part, and assembly.  But I want to do a sanity check on the results.  In my case the number of drawings seems low based on the number of parts and assemblies.  Given many parts will be library parts or import data from sub-contractors and that may explain it but right now I can't prove it. 

But I guess with this type of investigation one has to cut it off somewhere and call the answer good enough for this purpose, maybe I've reached that point.

- Jim

[TomU]

I'm looking for the total number of each of drawing, part, and assembly.

This still seems nebulous.  Assume the following objects exist in the system.

abc.prt   -.0

abc.prt   -.1

abc.prt   -.2

abc.prt  A.0

abc.prt  A.1

Do you want the result to return '1', or '5'?

[TomU]

If you want '5', switch your query builder table to EPMDocument instead of EPMDocumentMaster.  (If you do this, change your criteria to use File Name instead of CAD Name.)

[TomU]

Here's a query builder report that gives the same output as my original SQL statement above (all unique versions).  Report is attached.

[TomU]

And here is a query builder report that only returns unique objects (unique number).  It too agrees with my second SQL post above.

[JimTVancouverCa]

I think I'll start a new thread, as my current thinking is building on this initial question which we've now collectively answered.  Thanks again Tom Uminn‌ and Mike Lockwood‌.

[Marco_Tosin]

Thanks Tom,

I added a link at your QML files to the Resource for reporting

Document is open, so if you want you could do the same in the future.

[JimTVancouverCa]

The result I expect is 1 in your example list above.

[syadala]

In case if you have access to datebase you may take a look at key table (EPMDOCUMENTMASTERKEY, WTPARTMASTERKEY etc). those will give unique parts and epmdocuments.