Skip to main content
J
JimTVancouverCa1-Visitor
1-Visitor
September 14, 2015
Solved

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

  • September 14, 2015
  • 4 replies
  • 11338 views

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.

Best answer by TomU

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

4 replies

M
MikeLockwood22-Sapphire I
22-Sapphire I
September 14, 2015

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)

J
JimTVancouverCa1-VisitorAuthor
1-Visitor
September 14, 2015

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.

M
MikeLockwood22-Sapphire I
22-Sapphire I
September 14, 2015

Use EPM Document Master rather than EPM Document Master Key

    T
    TomU23-Emerald IVAnswer
    23-Emerald IV
    September 14, 2015

    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';
      T
      TomU23-Emerald IV
      23-Emerald IV
      September 14, 2015

      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

      J
      JimTVancouverCa1-VisitorAuthor
      1-Visitor
      September 14, 2015

      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?

      T
      TomU23-Emerald IV
      23-Emerald IV
      September 14, 2015

      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

      J
      JimTVancouverCa1-VisitorAuthor
      1-Visitor
      September 14, 2015

      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

      T
      TomU23-Emerald IV
      23-Emerald IV
      September 14, 2015

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

      J
      JimTVancouverCa1-VisitorAuthor
      1-Visitor
      September 14, 2015

      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

      Terms & ConditionsAccessibility statement