Need a livereport for items added to users personal workspace

I am looking for a live report that will count the total number of items in all users personal workspaces. This include all documents, emails, attachments, images, and folders including all items in subfolders. As output would want Users: First Name, Last Name, and Count of documents.We have a similiar live report like this that work well for all user created documents in livelink that we filter by CreateDate but this includes User Personal Workspaces.SELECT KUAF.FirstName, KUAF.LastName, count(*) AS 'Number of Items' FROM livelink.DTREE, livelink.KUAF WHERE DTREE.CreatedBy = KUAF.ID AND DTREE.CreateDate > %1 AND DTREE.CreateDate < %2 GROUP BY KUAF.FirstName, KUAF.LastName ORDER BY 'Number of Items' DESCSo basically we are after one similiar but just for items added to users personal workspace.Note: we use SQL Server 2005 not Oracle.Any help with this would be much appreciated.

Find more posts tagged with

Comments

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

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

Hi Paul,

There are many ways to skin a cat, but the following might do it for you.....

select k.name, k.firstname,k.lastname, k.deleted, pw.items-1 from kuaf k, (select ownerid, count(*) as items from dtree where ownerid in (select ownerid from dtree where subtype = 142) group by ownerid) pw where k.id = -(pw.ownerid)

This works for Oracle and should work for MS Sql server as there is no need to walk a folder tree.

I thought you might like to know the account/login and the deleted status of the account.

I am basically counting all the entries that by ownerID, which means they are in a given workspace - including the workspace itself - hence the reporting items-1 to ignore the workspace itself.

This may take a long time to run on a big system, so you might want to time it when running outside of Livelink (SQL Plus or Query Analyzer) before blocking a thread for some time.

Regards

Lindsay

European Escalation Team

Open Text UK

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 07 May 2009 08:45
To: eLink Recipient
Subject: Need a livereport for items added to users personal workspace

Need a livereport for items added to users personal workspace

Posted by tepuni01admin (Langham, Paul) on 2009/05/07 03:41

I am looking for a live report that will count the total number of items in all users personal workspaces. This include all documents, emails, attachments, images, and folders including all items in subfolders.

As output would want Users: First Name, Last Name, and Count of documents.

We have a similiar live report like this that work well for all user created documents in livelink that we filter by CreateDate but this includes User Personal Workspaces.

SELECT KUAF.FirstName, KUAF.LastName, count(*) AS 'Number of Items'
FROM livelink.DTREE, livelink.KUAF
WHERE DTREE.CreatedBy = KUAF.ID AND DTREE.CreateDate > %1 AND DTREE.CreateDate < %2
GROUP BY KUAF.FirstName, KUAF.LastName ORDER BY 'Number of Items' DESC

So basically we are after one similiar but just for items added to users personal workspace.

Note: we use SQL Server 2005 not Oracle.

Any help with this would be much appreciated.

Paul Langham

Thanks for the quick response Lindsay. Have ran it this morning and it seems to be working but would like to be able to filter by creation date so we can select a date range and will give the items added to users PW within that time frame. How would this SQL need to be modified to allow this?

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

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

Hi Paul,

Ok, if you go for a date range, you don't know whether or not the workspace will fall into that.

Therefore you need to exclude items if the ParentID is -1 (meaning they are a workspace).

I'd change the statement as follows

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 >= %1 AND CreateDate < %2 AND ParentID <> -1 group by ownerid) pw where k.id = -(pw.ownerid)

Running this as a Livereport, you would need to define date inputs for Start Date and End Date

and lower down define those inputs as User Input 1 and User Input 2.

You might consider that the end date should be included in the range, so would need to put %2 +1 so it runs to midnight of that day.

Alternatively, you could forget about the parameters and just count those created in the last 30 (or your chosen number of) days using

Oracle

AND CreateDate >= sysdate-30

MSSQL

AND CreateDate >= getdate( ) -30

By the way, you can use the same parameters ( %1 and &2) in the title of the report and if you tick the box "show prompts" the report can be executed repeatedly and you can change the inputs and resubmit without going backwards and forwards.

Good luck.

Regards

Lindsay

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 08 May 2009 01:03
To: eLink Recipient
Subject: Live report for items added to users personal workspace

Live report for items added to users personal workspace		Posted by tepuni01admin (Langham, Paul) on 2009/05/07 20:00
In reply to: RE Need a livereport for items added to users personal workspace		Posted by ldavies (Davies, Lindsay) on 2009/05/07 07:30

Paul Langham

Thanks for your help with this so far Lindsay. We have turned both of these into Livereports and they are working fine. I have one more question for you we are after another report that will allow as to count the number of itemsa user has added to livelink OUTSIDE of their personal workspace within a given date range. Would you happento have an idea of the SQL we could use to do this? Once again we want the users First and Last Name from the KUAF table as well as the count of documents within the inputed date range.thanks,

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

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

Hi Paul,

The last statement was....

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 >= %1 AND CreateDate < %2 AND ParentID <> -1 group by ownerid) pw where k.id = -(pw.ownerid)

This did what you wanted (as highlighted in bold) because of the following...

1. a real user from KUAF has an ID that is used as the personal workspace DataID.

2. all files in a workspace have the negative of the DataID for the workspace stored in the OwnerID column.

(note, in Livelink the column ownerID always means the Workspace that 'owns' the items within it and is not the same as the user who created an object)

3. personal workspace objects have a subtype of 142.

So we had a pre-select for personal workspace items from dtree and were calling it 'pw'.

To look for items outside of this, you need to change how we find the non-Personal Workspace items (so I call it 'npw')

We need to group by userid, not workspace (ownerid) but need to exclude items which are in the user's own workspace.

select k.name as Login, k.firstname,k.lastname, npw.items as "Count of Items" from kuaf k, (select UserID, count(*) as items from dtree where CreateDate >= %1 AND CreateDate < %2 AND UserID <> -(OwnerID) AND ParentID <> -1 group by UserID) npw where k.id = npw.UserID

That should work OK for you.

Good luck.

Regards

Lindsay
EU-Escalation Team

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 12 May 2009 23:42
To: eLink Recipient
Subject: Live report for documents added outside of their Personal Workspace

Live report for documents added outside of their Personal Workspace		Posted by tepuni01admin (Langham, Paul) on 2009/05/12 18:38
In reply to: RE Live report for items added to users personal workspace		Posted by ldavies (Davies, Lindsay) on 2009/05/08 06:59

Thanks for your help with this so far Lindsay. We have turned both of these into Livereports and they are working fine. I have one more question for you we are after another report that will allow as to count the number of items
a user has added to livelink OUTSIDE of their personal workspace within a given date range. Would you happen
to have an idea of the SQL we could use to do this? Once again we want the users First and Last Name from the KUAF table as well as the count of documents within the inputed date range.

thanks,