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

Community Tip - Need help navigating or using the PTC Community? Contact the community team. X

Latest revision of each view report

gscorpil-2
6-Contributor

Latest revision of each view report

We typically use this something like this to get the latest rev either in reports or sql.

"p.versionsortida2versioninfo = (select max (p1.versionsortida2versioninfo) from wcadmin.wtpart p1 where p1.ida3masterreference = p.ida3masterreference)
and p.latestiterationinfo = 1"

Does anyone know how to make this work for each view?  The issue comes up when the view revisions are not the same.  For example "part1 Rev A Design view" and "part1 Rev B Service view" excludes the design view.  Anyone know a trick to getting this to work?

1 ACCEPTED SOLUTION

Accepted Solutions

Hi,

I've been developing/experimenting with a lot of report templates (not the SQL directly though). I'll try to describe it as best I can.

When you do a max condition, normally you would get just a single result. If you want to get a max for each of something, then that something has to be defined in the main query, and the subselect has a criteria that goes back to the main query. 

So basically in your subquery, you would add a criteria that the view = the view of the WTpart from the main query. That way, you should get a result for the max of each view of each WTPart.

View solution in original post

7 REPLIES 7

Hi,

I've been developing/experimenting with a lot of report templates (not the SQL directly though). I'll try to describe it as best I can.

When you do a max condition, normally you would get just a single result. If you want to get a max for each of something, then that something has to be defined in the main query, and the subselect has a criteria that goes back to the main query. 

So basically in your subquery, you would add a criteria that the view = the view of the WTpart from the main query. That way, you should get a result for the max of each view of each WTPart.

gscorpil-2
6-Contributor
(To:joe_morton)

That worked great thanks.

Hello @gscorpil-2 

 

Can you share the sample implementation in QML or SQL format please.

Also, Report Builder Screenshot if you have developed it on Query Builder.

Thank you!

 

 

 

 

 

Visit: http://www.windchillguru.com

rleir
17-Peridot
(To:joe_morton)

Hi Joe

the WTDocument contains a field called "Branch Identifier" which seems to be largest for the most recent rev.

Is it correct to use this field to identify the most recent rev?

thanks -- Rick

 

Document [wt.doc.WTDocument]
Branch Identifier [branchIdentifier]
joe_morton
17-Peridot
(To:rleir)

Hi Rick,

Yeah, that's what I would use to get the latest revision. 

Maybe also this discussion can help other people who read this post.

 

https://community.ptc.com/t5/Windchill/Report-to-list-WTParts-of-particular-view/td-p/96770

 

 

Marco
Top Tags