Report on states

For those reading, this discussion grew out of what was written here.

https://community.ptc.com/t5/Windchill/Query-Builder-Lifecycle-History-Example/m-p/444736

Here's some tips to help you get started.

First, it's pretty difficult to report 2 different types of things in one report. I'd recommend one report dedicated to parts, and a separate report for documents. Both reports would work similarly.

I'd start with something like this:

Start with the Part Master. This ensures we're looking at a single Part. (For Documents, you'd use the Document Master here). From there, link to 2 different Part objects. These are different iterations. Most likely the first iteration to be "In Work" is a different iteration than the one that was "Released".

Each Part is linked to a Life Cycle History. That gives us all state changes for that iteration.

The Criteria shown lets you compare when the first part iteration got to In Work against when another part iteration of the same revision got to Released.

From there, you just need to use the Date Difference database function to get the duration between the 2 states:

One caveat to look out for... this doesn't take into account if a part enters one of the states more than once in a single Revision. If you have that scenario, you'll need to add additional subselects to limit the Life Cycle History's considered. That gets more complicated, so hopefully you don't have that scenario.