cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Highlighted

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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

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';

19 REPLIES 19

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

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)

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

Hello Mike,

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

2015-09-14_1417.png

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

Thanks again.

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

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';

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

Use EPM Document Master rather than EPM Document Master Key

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

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;

query2.PNG

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

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?

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

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;

query3.PNG

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

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 DrawingCreo PartCreo Assembly
SQL5593935000867909
SQL Unique8763425978759
Report Builder Query8763425998759

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

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

Doh!  That works much better.