Skip to main content
T
towen1-Visitor
1-Visitor
January 22, 2013
Solved

Windchill 9.1 - Query Builder

  • January 22, 2013
  • 1 reply
  • 5777 views

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

Best answer by vaillan

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

1 reply

V
vaillan10-Marble
10-Marble
January 22, 2013

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

T
towen1-VisitorAuthor
1-Visitor
January 23, 2013

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

V
vaillan10-MarbleAnswer
10-Marble
January 23, 2013

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

Terms & ConditionsAccessibility statement