How to create a querybuilder report that counts the number of objects in a Product

[JeffBrodsky]

I am using Windchill 10.1 M040, and attempting to determine how many objects (parts, documents, epmdocs, whatever) are in each of my products.

I am thinking querybuilder can do this, but the count function gives me an error "A statement with aggregate function can not be used with access control."

Has anyone built such a query (and can share)?

[abhishekarya]

Jeff, Below Query will give you all object types by count in every Product Context by folder name:

SELECT b.total_objects,b.CLASSNAMEKEYROLEBOBJECTREF, subfolder.name, pdmlinkproduct.namecontainerinfo
FROM subfolder,
pdmlinkproduct,
(SELECT Count(*) total_objects, ida3a5,CLASSNAMEKEYROLEBOBJECTREF
FROM iterfoldermemberlink
WHERE classnamekeyroleaobjectref = 'wt.folder.SubFolder'
GROUP BY ida3a5,iterfoldermemberlink.CLASSNAMEKEYROLEBOBJECTREF) b
WHERE subfolder.ida2a2=b.ida3a5 AND subfolder.ida3containerreference=pdmlinkproduct.ida2a2
ORDER BY namecontainerinfo;

[StevenDumon]

Hi Jeff,

your question is still valid. I hoped to find how many CAD documents existed per context, and what was the latest modified date of those documents.

I am sure all this information is available in the database, but how on earth can I build this query ?

[joe_morton]

I'm using a newer version of Windchill, but I think it would be similar. Look for a checkbox on the query builder to "bypass access control"

[anursingh]

Hi @JeffBrodsky,

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

[Fadel]

take a look at https://www.ptc.com/en/support/article/CS149816