Windchill 9.1 - Query Builder

[towen]

Hello everyone,

I have recently started utilising the Report Manager (Query Builder), to produce a number of reports on CAD data, and I am struggling to produce a report to meet my needs!

I am trying to create a report that lists all the latest (version and iteration) EPMDocuments and their associated WTParts in certain contexts. The key part I am struggling with is the link between the EPMDocs and the WTParts, although clarification on how to definitively only receive the latest version and iteration of the document would also be appreciated.

I am able to create individual reports on both EPMDocuments and WTParts, however, linking the two is proving harder than I thought.

Any advice or explanation on how to do this would be greatly appreciated, if at all possible I would like to understand the solution rather than copy it.

Thank you,

Tristan Owen

Graduate Engineer,

Renold PLC

[vaillan]

This article indicates the tables involved are EPMBuildRule & EPMDescribeLink; there are some suggestions in this article which might also be helpfu too.

[towen]

Thanks Stephen, that's given me a bit more to go on. Could anyone explain how I could construct the query to incorporate these tables? I've had a go myself, but still struggling.

Thanks

[vaillan · Accepted solution]

Tony,

I pulled the two SQL snippets below from a gather info report, which is report that captures SQL the method server sends to Oracle, I suspect you will need to create two reports which are similar to these statements. It unfortunatly involves iterating, but when I used to create query builder reports I'd first create the SQL by had that I wanted, and then use the view SQL capability of QueryBuilder until I found the join combinations which matched the SQL.

I suspect your SQL will like also include the EPMDocumentMaster table; the EPMDocument table links to it using the idA3masterReference column. i.e. EPMDocument .idA3masterReference=EPMDocumentMaster .idA2A2

SQL1:

FROM EPMDocument A0,EPMBuildRule A1,WTPart A2,WTPartMaster A2B

WHERE A2.idA3masterReference=A2B.idA2A2

AND A0.branchIditerationInfo = A1.branchIdA3A5

AND A2.branchIditerationInfo = A1.branchIdA3B5

SQL2:

FROM WTPart A0,WTPartMaster A0B,EPMDocument A1,EPMDescribeLink A2

WHERE A0.idA3masterReference=A0B.idA2A2

AND A0.idA2A2 = A2.idA3A5

AND A1.idA2A2 = A2.idA3B5

[towen]

That's great, I will definitely be using the view SQL capability more often now, and your snippets will help a lot generating these reports.

Thank you very much for your time.

[PhilK]

Hey, this is very helpfull - is it possible to combine these reports in one? So that I have one report that gives me all association types?