Personal Workspace Individual size

Emma_Tutty

Hi
I am trying to create a Live Report that will have the below categories

• User ID
• Department
• Number of items in Personal workspace (split folders/documents, etc)
• Size of each personal workspace

I have managed to produce the below report that provide login, first
name, last name and will count the contents of the personal workspace.

SELECT k.name as Login, k.firstname,k.lastname, pw.items as "Count of
Items" from kuaf k, (select ownerid, count(*) as items
FROM dtree
WHERE ownerid in (SELECT ownerid FROM dtree WHERE subtype = 142)
AND CreateDate >= sysdate-365 -1 group by ownerid) pw WHERE k.id = -
(pw.ownerid)

I have then come up with one that will get me the department with first
name and last name.

SELECT k.FirstName"Firstname", k.LastName"Lastname", g.name"Department"
FROM DAuditnew,KUAF k,KUAF g WHERE k.ID = DAuditnew.Performerid AND
k.GroupID = g.ID
AND upper(DAuditnew.AuditStr)='LOGIN'
GROUP BY DAuditnew.performerID, k.FirstName, k.LastName, g.name

I would like to be able to combine these and also to have a column
displaying the size of each workspace if it all possible.
Any help would be much appreciated.

Colin_Schmidt

SELECT k.name as Login, k.firstname||' '||k.lastname||'  ['||kd.name||']', pw.items as "Count of Items" 

from kuaf k, kuaf kd, (select ownerid, count(*) as items FROM dtree WHERE ownerid in (SELECT ownerid FROM dtree WHERE subtype = 142) AND CreateDate >= sysdate-365 -1 group by ownerid) pw 

WHERE (k.id = -(pw.ownerid) and k.type = 0 and k.deleted = 0) and (k.groupid = kd.id) 

using your code, but dealing with just active UserIDs.

Colin J. 

On Thu, Feb 5, 2015 at 4:34 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Personal Workspace Individual size

Posted byemma.tutty@access.uk.com (Tutty, Emma)On 02/05/2015 04:28 AM

Hi I am trying to create a Live Report that will have the below categories

• User ID
• Department
• Number of items in Personal workspace (split folders/documents, etc)
• Size of each personal workspace

I have managed to produce the below report that provide login, first
name, last name and will count the contents of the personal workspace.

SELECT k.name as Login, k.firstname,k.lastname, pw.items as "Count of
Items" from kuaf k, (select ownerid, count(*) as items
FROM dtree
WHERE ownerid in (SELECT ownerid FROM dtree WHERE subtype = 142)
AND CreateDate >= sysdate-365 -1 group by ownerid) pw WHERE k.id = -
(pw.ownerid)

I have then come up with one that will get me the department with first
name and last name.

SELECT k.FirstName"Firstname", k.LastName"Lastname", g.name"Department"
FROM DAuditnew,KUAF k,KUAF g WHERE k.ID = DAuditnew.Performerid AND
k.GroupID = g.ID
AND upper(DAuditnew.AuditStr)='LOGIN'
GROUP BY DAuditnew.performerID, k.FirstName, k.LastName, g.name

I would like to be able to combine these and also to have a column
displaying the size of each workspace if it all possible.
Any help would be much appreciated.

---

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

This electronic message contains information from MeadWestvaco Corporation (mwv.com) or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.