Query Report for Management

BenLoosli · ‎Mar 11, 2014

Management is looking for a listing of all items contained in Windchill. They would like: Number, Name, Version and State. Does someone have a report that they may share that generates this information? Lacking an existing query report, can someone share how to build one?

MikeLockwood · ‎Mar 11, 2014

Much easier to get from the database. Export each result.

* Product Data

o WTParts (if you use): select wtpartnumber from wtpartmaster

o EPMDoc's: select documentnumber from epmdocumentmaster

o WTDoc's: select documentnumber from wtdocumentmaster

* Change Objects (if you use)

o Promotion Requests: select name from promotionnotice

o Problem Reports: select name from wtchangissuemaster

o Change Requests: select name from wtchangerequest2master

o Change Notices: select name from wtchangeorder2master

o Change Activities: select name from wtchangeactivitymaster

* Other

o Processes: select name from wfprocess

AL_ANDERSON · ‎Mar 11, 2014

" all items contained in Windchill" is not a reasonable requirement since
it would include every access control rule, context, role, user, every
workflow odd ball object etc.

However, if your intent is to list all revision controlled objects, then
make a simple query for all "Revision Controlled" objects where "Latest
Iteration = 1" and display the Name, Number, Revision, and State. That
will give you everything that is version controlled. One of the nice
things about query builder is that it recognizes the java object model
"interfaces" without needing to know the actual database table names. By
searching for "Revision Controlled" you actually get all tables that
implement the java "Revision Controlled" interface. Pretty cool stuff,
actually.

For us, we would get over 1 million objects in any such report, even for
just revision controlled objects. Since we have a DB query limit set for
performance reasons, we would actually write an SQL statement if we really
needed to list out all of our millions of revision controlled objects.

Al

[solutions] - Query Report for Management

Ben Loosli

bellj · ‎Mar 12, 2014

This is my (Oracle) SQL solution:
I use WC_OBJECT_FINDER for the basic search.
It includes Change objects but you can edit it to return just Revision Controlled objects.

For folders, I use the FOLDER_PKG to build a table of libraries and folders.
This is static so much be refreshed periodically.

To add folders and some other calculations, I tie it up with WC_OBJECTS.

To go even further, I use WC_AFFECTED, WC_CHANGEHISTORY, WC_ATTACHMENTS, and WC_ATTRIBUTES.

Run the attachments in this order:
CREATE_PERMISSIONS
WC_LIBRARY_FOLDER.TABLE
WC_CONTAINERS_V
WC_LIBRARY_FOLDER_VIEW
WC_OBJECT_FINDER
WC_FOLDER_PKG
WC_OBJECTS
WC_AFFECTED
WC_CHANGEHISTORY
WC_ATTACHMENTS
WC_ATTRIBUTES
REFRESH_JOB

Let me know if you have any problems with any of this.

joe bell
GSIMS Administrator
GPS Sustainment Information Management System
719-572-2890
bellj@gpssims.com<">mailto:bellj@gpssims.com>

amansfield · ‎Mar 12, 2014

Being an "Intralink 8.x" user, I don't have any reporting tools at my disposal other than running SQL queries from the command line. The downside to that is I don't have any idea how the tables are structured and which table contains what data. The queries I have were supplied to me by various people including PTC Tech Support & some on this list.

I would like to run a query and save the contents to a textfile that gives me the following information:

NUMBER,latest Revision, latest Iteration, Context &Folder,LastModified, Generic?, Instance?, Name

Ideally Context and Folder would be concatenated together (I could do this afterwards if necessary). Generic and Instance would be True / False or Yes / No.

This query would be run against all the data in our system (we have about 163,000 latest objects)

Would anyone be able to show me how to structure a SQLPlus query that would generate that for me including writing it to disk? We're running the Intralink version of Windchill 10.1 M040

Thanks in advance for any suggestions!

Andrew Mansfield

In Reply to Mike Lockwood:

Much easier to get from the database. Export each result.

* Product Data

o WTParts (if you use): select wtpartnumber from wtpartmaster

o EPMDoc's: select documentnumber from epmdocumentmaster

o WTDoc's: select documentnumber from wtdocumentmaster

* Change Objects (if you use)

o Promotion Requests: select name from promotionnotice

o Problem Reports: select name from wtchangissuemaster

o Change Requests: select name from wtchangerequest2master

o Change Notices: select name from wtchangeorder2master

o Change Activities: select name from wtchangeactivitymaster

* Other

o Processes: select name from wfprocess

bellj · ‎Mar 12, 2014

Although I am devolving, and am embracing my devolution, I haven't yet devolved to the point of using SQLPlus and text files.
I'm sure I'll get there soon enough.

All I can offer is some instructions I have previously posted here on using Excel to get Oracle queries.

Create the Oracle objects I provided in my earlier reply.
You can do this with SQLPlus.
You will also have to install and configure the Oracle client. NOT the Instant Client!

Please disregard if you do not have MS Excel.

joe bell
GSIMS Administrator
GPS Sustainment Information Management System
719-572-2890
bellj@gpssims.com<">mailto:bellj@gpssims.com>

ericstarkman · ‎Mar 12, 2014

Hi Andrew,

This should get you pretty close for EPMDocument (CAD) only... I am not
positive on family table generic vs instance (maybe somebody else can chime
in here) but think familytablestatus gets you close...

If you'd like to test on a subset, add "where rownum < 1000" at the end.

Thanks,
Eric

spool off

set echo off

set feedback off

set linesize 99999

set pagesize 0

spool "c:\temp\EPMDocs.csv"

select

"||trim(DocumentNumber)||",'||

"||trim(versionida2versioninfo)||",'||

"||trim(iterationida2iterationinfo)||",'||

"||trim(PDMLINKPRODUCT.NAMECONTAINERINFO)||",'||

"||trim(WTLIBRARY.NAMECONTAINERINFO)||",'||

"||trim(subfolder.name)||",'||

"||trim(epmdocument.modifystampa2)||",'||

"||trim(epmdocument.familytablestatus)||",'||

"||epmdocumentmaster.name||",'||

"||CADname||"

/*"||trim(DocumentNumber)||" as DocumentNumber,

"||trim(versionida2versioninfo)||" as Revision,

"||trim(iterationida2iterationinfo)||" as Iteration,

"||trim(PDMLINKPRODUCT.NAMECONTAINERINFO)||" AS ContextProduct,

"||trim(WTLIBRARY.NAMECONTAINERINFO)||" as ContextLibrary,

"||trim(subfolder.name)||" as FolderName,

"||trim(epmdocument.modifystampa2)||" as LastModified,

"||trim(epmdocument.familytablestatus)||" as FamilyTableStatus,

"||epmdocumentmaster.name||" as Name,

"||CADname||" as CADName*/

from EPMDocumentMaster

inner join EPMDocument

ON EPMDocument.CLASSNAMEKEYMASTERREFERENCE = EPMDocumentMASTER.CLASSNAMEA2A2

AND EPMDocument.IDA3MASTERREFERENCE = EPMDocumentMASTER.IDA2A2

and EPMDocument.latestiterationinfo = 1

and EPMDocument.STATECHECKOUTINFO = 'c/i'

and EPMDocument.VERSIONSORTIDA2VERSIONINFO = (select
max(epm2.VERSIONSORTIDA2VERSIONINFO) from EPMDocument epm2 where
epm2.IDA3MASTERREFERENCE = EPMDocumentMaster.ida2a2)

Left JOIN PDMLINKPRODUCT

ON epmdocument.IDA3CONTAINERREFERENCE = PDMLINKPRODUCT.IDA2A2

Left JOIN wtLIBRARY

ON epmdocument.IDA3CONTAINERREFERENCE = wtlibrary.ida2a2

left join subfolder

on epmdocument.ida3b2folderinginfo = subfolder.ida2a2

spool off

rhegde · ‎Mar 12, 2014

Andrew,

Family table information is stored in "FamilyTableStatus" column of
"EPMDocument" table. This can have following values:
0 - stands for "Stand Alone"
1 - stands for "Instance"
2 - stands for "Generic"

--
Rochan Hegde
Productspace Solutions Inc.

On Wed, Mar 12, 2014 at 4:39 PM, Eric Starkman <
estarkman@starkdifference.com> wrote:

> Hi Andrew,
>
>
>
> This should get you pretty close for EPMDocument (CAD) only... I am not positive on family table generic vs instance (maybe somebody else can chime in here) but think familytablestatus gets you close...
>
>
>
> If you'd like to test on a subset, add "where rownum < 1000" at the end.
>
>
>
> Thanks,
>
> Eric
>
>
>
>
>
>
>
> spool off
>
> set echo off
>
> set feedback off
>
> set linesize 99999
>
> set pagesize 0
>
>
>
> spool "c:\temp\EPMDocs.csv"
>
>
>
> select
>
> "||trim(DocumentNumber)||",'||
>
> "||trim(versionida2versioninfo)||",'||
>
> "||trim(iterationida2iterationinfo)||",'||
>
> "||trim(PDMLINKPRODUCT.NAMECONTAINERINFO)||",'||
>
> "||trim(WTLIBRARY.NAMECONTAINERINFO)||",'||
>
> "||trim(subfolder.name)||",'||
>
> "||trim(epmdocument.modifystampa2)||",'||
>
> "||trim(epmdocument.familytablestatus)||",'||
>
> "||epmdocumentmaster.name||",'||
>
> "||CADname||"
>
>
>
> /*"||trim(DocumentNumber)||" as DocumentNumber,
>
> "||trim(versionida2versioninfo)||" as Revision,
>
> "||trim(iterationida2iterationinfo)||" as Iteration,
>
> "||trim(PDMLINKPRODUCT.NAMECONTAINERINFO)||" AS ContextProduct,
>
> "||trim(WTLIBRARY.NAMECONTAINERINFO)||" as ContextLibrary,
>
> "||trim(subfolder.name)||" as FolderName,
>
> "||trim(epmdocument.modifystampa2)||" as LastModified,
>
> "||trim(epmdocument.familytablestatus)||" as FamilyTableStatus,
>
> "||epmdocumentmaster.name||" as Name,
>
> "||CADname||" as CADName*/
>
>
>
> from EPMDocumentMaster
>
> inner join EPMDocument
>
> ON EPMDocument.CLASSNAMEKEYMASTERREFERENCE =
> EPMDocumentMASTER.CLASSNAMEA2A2
>
> AND EPMDocument.IDA3MASTERREFERENCE = EPMDocumentMASTER.IDA2A2
>
> and EPMDocument.latestiterationinfo = 1
>
> and EPMDocument.STATECHECKOUTINFO = 'c/i'
>
> and EPMDocument.VERSIONSORTIDA2VERSIONINFO = (select
> max(epm2.VERSIONSORTIDA2VERSIONINFO) from EPMDocument epm2 where
> epm2.IDA3MASTERREFERENCE = EPMDocumentMaster.ida2a2)
>
>
>
> Left JOIN PDMLINKPRODUCT
>
> ON epmdocument.IDA3CONTAINERREFERENCE = PDMLINKPRODUCT.IDA2A2
>
>
>
> Left JOIN wtLIBRARY
>
> ON epmdocument.IDA3CONTAINERREFERENCE = wtlibrary.ida2a2
>
>
>
> left join subfolder
>
> on epmdocument.ida3b2folderinginfo = subfolder.ida2a2
>
>
>
> spool off
>
>
>
>
>
>
>
>
>
> *From:* Andrew Mansfield [
> disposal other than running SQL queries from the command line. The downside
> to that is I don't have any idea how the tables are structured and which
> table contains what data. The queries I have were supplied to me by various
> people including PTC Tech Support & some on this list.
>
> I would like ...

ericstarkman · ‎Mar 12, 2014

So to get two columns Generic? and Instance? we can replace

"||trim(epmdocument.familytablestatus)||",'||

With...

"||CASE WHEN epmdocument.familytablestatus = '2' THEN 'Yes' ELSE 'No' END
||",'||

"||CASE WHEN epmdocument.familytablestatus = '1' THEN 'Yes' ELSE 'No' END
||",'||