Looking for a Live Report that will return file numbers & summed sizes of objects in a project and e

I guess the title of this question says it all. I'm looking for a livereport that will return file numbers (counts) & file sizes (summed) of all objects found in a project, including any sub projects, and folders regardless of the depth of the structure. Is this possible? I currently have a live report that accomplishes this in a pure folder structure, but it fails to include projects found in any 'structure'.Dave EbelsJohnson Controls dave.j.ebels@jci.com

Find more posts tagged with

Comments

Colin_Schmidt

I'm also looking to report the structure and the childcount and the SUm of all of the Datasize columsn of these documents within a specified folder structure.I have this, but there's something wrong in how it works. it takes way too long.SELECT dt.dataid, dt.name, dt.childcount, (select sum(dv.datasize) from livelink.dtree dt3 join livelink.dversdata dv on dt3.dataid=dv.docid and dt3.versionnum=dv.version where dt3.dataid in (select dt4.dataid from livelink.dtree dt4 where dt.dataid=dt4.parentid and dt4.subtype <> 0)) "Total MBs", LEVEL, SYS_CONNECT_BY_PATH (dt.name, ' | ') "Path" FROM livelink.dtree dt where dt.childcount>0 and dt.subtype=0 CONNECT BY PRIOR dt.dataid = dt.parentid START WITH dt.parentid = 64420

Sunitha_Iyer

This is really helpful Query, but it incldes folder as child in count.

Is it possibl to just have document count for a given folder ID?

I am using below query:

: SELECT COUNT (*) FROM DTREE WHERE SubType = 144 AND ParentID = ‘INSERT ID of Folder Here’

THis gives only count for documents inside given folde and doesnt include subfolder doument.

Can you please help me to find that query?

Colin_Schmidt

The reason the script takes so long is the retrieval of the path. If you need that, it will just have to be tolerated. I have two LRs that retrieve information about an object by it's DataID. One with the Path and the other that just builds a partial URL to the Parent folder. Sometimes the Path LR times out and sometimes it returns.

Lindsay_Davies

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi Colin,

I suspect the reason this takes a long time is that there is an unfiltered join of DVersData and DTree lurking in there.

I think it will join all rows and produce a massive interim result set.

You would need to restructure or rewrite your query.

Regards

Lindsay

OpenText UK

From: eLink Discussion: Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 March 30, Wed 22:14
To: eLink Recipient
Subject: Folder dtree.name, Total of Documents(dtree.childcount), and sum(dversdata.datasize)

Folder dtree.name, Total of Documents(dtree.childcount), and sum(dversdata.datasize)		Posted by colin.schmidt@almacgroup.com (Schmidt, Colin J) on 2011/03/30 17:08
In reply to: Looking for a Live Report that will return file numbers & summed sizes of objects in a project and everything under it including subprojects & folders & all files		Posted by jocoadmin (Ebels, Dave) on 2003/10/24 11:58

I'm also looking to report the structure and the childcount and the SUm of all of the Datasize columsn of these documents within a specified folder structure.

I have this, but there's something wrong in how it works. it takes way too long.

SELECT dt.dataid, dt.name, dt.childcount,
(select sum(dv.datasize) from livelink.dtree dt3
join livelink.dversdata dv on dt3.dataid=dv.docid and dt3.versionnum=dv.version
where dt3.dataid in (select dt4.dataid from livelink.dtree dt4 where dt.dataid=dt4.parentid and dt4.subtype <> 0)) "Total MBs",
LEVEL, SYS_CONNECT_BY_PATH (dt.name, ' | ') "Path"
FROM livelink.dtree dt
where dt.childcount>0 and dt.subtype=0
CONNECT BY PRIOR dt.dataid = dt.parentid START WITH dt.parentid = 64420

[To reply to this thread, use your normal E-mail reply function.]
Topic:	Looking for a Live Report that will return file numbers & summed sizes of objects in a project and everything under it including subprojects & folders & all files
Discussion:	Live Reports Discussion
Livelink Server:	knowledge-wlweb01

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.