Folder Path with Count of documents under it

Jayalakshmi_Mohan

Hi,

I am looking a query that could give a result as below. I have multiple folders and I need to display the count of each folder in a single report.

Sample Output

Folder Path                                Count - No of Documents

Enterprise\ABC\SF1\SF2             12

Enterprise\EFG\SF1\SF2              20

Can someone help with a query to achive this ?

 

Nick Goddard

Hi Jayalakshmi

Easiest Option (?) here is to use a WebReport with the Live Report Source of:

Select DataID, Childcount from DTree where DataID IN (1,2,etc..)   - I am assuming that there are not masses of folders? – so it would not be time consuming to get the DataIDs

Or

Select DataID, Childcount from DTree where DataID IN (Select DataID From DTree where ParentID = x UNION Select DataID From DTree where ParentID = Y etc..) - if that makes things any easier.

Then in the Row Section of the WebReport use the following Tags:

[LL_REPTAG=DataID NODEINFO:PATH /], [LL_REPTAG=ChildCount /] or use [LL_REPTAG=DataID NODEINFO:CHILDCOUNT /],

Note you may want to run the Admin Regenerate Child Counts utility 1^st – here - ?func=admin.DbRepairUtils as sometimes ChildCounts in DTreeCore (which DTree is based on) can get out of Sync and be incorrect (this is only available on 16.x only I believe?). Or run a Database Verification. WARNING - best try both of this in a non-production replica environment.

If you want to do it purely in a Live Report – this is also possible but is a lot more effort in my opinion. You could use a CTE – there should be some examples floating about in this forum if you filter the forum on ‘CTE’ (assuming you have a SQL Database) to get the hierarchy path of each folder, and then join with a query that counts all rows of the matching ParentIDs

Cheers

Nick