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

We are happy to announce the new Windchill Customization board! Learn more.

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

JimTVancouverCa
1-Newbie

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
TomU
23-Emerald IV
(To:JimTVancouverCa)

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

View solution in original post

19 REPLIES 19

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)

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.

Use EPM Document Master rather than EPM Document Master Key

Doh!  That works much better.

TomU
23-Emerald IV
(To:JimTVancouverCa)

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
23-Emerald IV
(To:JimTVancouverCa)

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

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
23-Emerald IV
(To:JimTVancouverCa)

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

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

TomU
23-Emerald IV
(To:JimTVancouverCa)

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

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
23-Emerald IV
(To:JimTVancouverCa)

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
23-Emerald IV
(To: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
23-Emerald IV
(To:TomU)

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

QB_Count_Objects.png

TomU
23-Emerald IV
(To:TomU)

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

QB_Count_Objects2.png

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
21-Topaz I
(To:TomU)

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.

Marco

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

syadala
5-Regular Member
(To:JimTVancouverCa)

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.

Top Tags