Query builder -Number of CAD documents per product and the latest Last Modified date in that product

[StevenDumon]

Version: Windchill 12.0

Use Case: I have close to 1000 (Engineer to order) products, designs starting from 2006 up to today. Most products are built some are still active. Today I use a spreadsheet to indicate the number of CAD objects in each context, and what is the latest modification date. For each context separately, that requires a separate search and sorting to find the latest modified date. My goal is to decide for each context whether to purge unnecessary iterations and provide vaulting rules to move the physical files to a medium that doesn't need te be included in our backup every day.

Description:

How do I approach this issue in Windchill's Query Builder ? I can link EPMDocument and Context, but I don't find how to count the number of objects, or how to apply the maximum-function on the Modified date per context...

[Marco_Tosin · Accepted solution]

Take a look at this article

Report to list the count of EPM Documents in each context with total file size

This is about WTDocument but you could use it as an idea also for EPMDocs

Report to fetch all WTDocuments not modified after specific date

This is about the use of Maximum function

How to apply maximum function on an attribute in QML report

[StevenDumon]

I got it, but had to experiment a little bit with SQL statements to better understand the idea behind this. The example report of file size per context was of great help.

My report is now set up as follows :

Tables and Joins appear very simple, but was in fact the last issue: that arrow direction is important: FROM EPM Document. I don't fully understand why, but it is important.

The example report also showed me how to add database functions correctly in the new user interface. Also, the Count of Identifier.Id was really helpful here.

Eventually the Results Table shows exactly what I expected.

My qml-file is also attached.

[anursingh]

Hi @StevenDumon,

I wanted to see if you got the help you needed.

If so, please mark the appropriate reply as the Accepted Solution. It will help other members who may have the same question.
Of course, if you have more to share on your issue, please pursue the conversation. 

Thanks,
Anurag

[StevenDumon]

I have managed to import the QML file in the organization where I want to see this summary, but I don't understand why I need to add Criteria there. I tried to only report on Number of Latest Iterations = 1, only on a specific Context Name, ... but always get 0 results ?

Is it possible to retrieve a table for all contexts ?

Edit: Select All criteria and leave all inputs empty now resulted in 746 contexts, but the filesize can't be that small 🙂