Hi,
I want to make a livereport to count new document added last month in the system but I want to exlcude Personal workspaces from that.
Please help.
Thanks.
You’d have to exclude their WS or only look for anything under “DTREE.OWNERID = -2000”. If you have Projects or some other special “container”, you’d have to account for that too.
Select da.auditid, da.auditstr, da.auditdate, da.dataid, da.performerid, k.name “Performer”, da.valuekey
From dauditnew da
Join dtree dt on da.dataid = dt.dataid and dt.ownerid = -2000
Where da.auditid in (1, 11) and da.auditdate >= %1
Colin J
From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>Sent: Wednesday, October 10, 2018 12:35 PMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: Count new documents - Exclued Personal Workspaces
Count new documents - Exclued Personal Workspaces
Posted bybaber.amin@albertainnovates.ca (Amin, Baber) On 10/10/2018 01:29 PM
[To post a comment, use the normal reply function]
Forum:
Content Server LiveReports Forum
Content Server:
My Support
[EXTERNAL] - RE Count new documents - Exclued Personal Workspaces Posted bycolin.schmidt@cognizant.com (Schmidt, Colin)On 10/11/2018 07:40 AM You’d have to exclude their WS or only look for anything under “DTREE.OWNERID = -2000”. If you have Projects or some other special “container”, you’d have to account for that too. Select da.auditid, da.auditstr, da.auditdate, da.dataid, da.performerid, k.name “Performer”, da.valuekeyFrom dauditnew daJoin dtree dt on da.dataid = dt.dataid and dt.ownerid = -2000Where da.auditid in (1, 11) and da.auditdate >= %1 Colin J From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>Sent: Wednesday, October 10, 2018 12:35 PMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: Count new documents - Exclued Personal Workspaces Count new documents - Exclued Personal Workspaces Posted bybaber.amin@albertainnovates.ca (Amin, Baber) On 10/10/2018 01:29 PM Hi,I want to make a livereport to count new document added last month in the system but I want to exlcude Personal workspaces from that.Please help.Thanks.[To post a comment, use the normal reply function]Forum: Content Server LiveReports ForumContent Server: My Support
In the several, but not near a dozen, installations I’ve been in, ‘-2000’ is always EWS. I thought it was defined in the installation. I also thought about including an alternate that validated the Owner against the KUAF or DTREE (subtype = 142), but didn’t have the time at the moment.
From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>Sent: Thursday, October 11, 2018 10:17 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: Re [EXTERNAL] - RE Count new documents - Exclued Personal Workspaces
Re [EXTERNAL] - RE Count new documents - Exclued Personal Workspaces
Posted byappnair@gmail.com (Nair, Krishnankutty) On 10/11/2018 11:17 AM
Colin,
You know this but in the spirit ...
In some organizations, Enterprise Workspace DataID is not 2000 so the owner of -2000 is coming from that
Instead of hardcoding the OwnerID should be found out by just doing
select -(DataID) OwnerID ,OwnerID from DTree where SubType=141 ;/*Enterprise Workspace Node*/
That goes for many of the known workspaces in Livelink every once in a while I see hard codes in commercial products that
put 2004 as a Category DataiD and they can't find things.
Similarly, Personal Workspaces are type 142 and its OwnerID is the negative of theKUAF.ID of t he user
Lots of queries answering OwnerID can be found in the LR area.
Ciao,
..... appnairappunair( Livelink Rocks )
Well, if I called the wrong number, why did you answer the phone?James Thurber, New Yorker cartoon caption, June 5, 1937
On Thu, Oct 11, 2018 at 6:42 AM eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:
[EXTERNAL] - RE Count new documents - Exclued Personal Workspaces Posted bycolin.schmidt@cognizant.com (Schmidt, Colin) On 10/11/2018 07:40 AM You’d have to exclude their WS or only look for anything under “DTREE.OWNERID = -2000”. If you have Projects or some other special “container”, you’d have to account for that too. Select da.auditid, da.auditstr, da.auditdate, da.dataid, da.performerid,k.name “Performer”, da.valuekey From dauditnew daJoin dtree dt on da.dataid = dt.dataid and dt.ownerid = -2000Where da.auditid in (1, 11) and da.auditdate >= %1 Colin J From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>Sent: Wednesday, October 10, 2018 12:35 PMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: Count new documents - Exclued Personal Workspaces Count new documents - Exclued Personal Workspaces Posted by baber.amin@albertainnovates.ca (Amin, Baber) On 10/10/2018 01:29 PM Hi,I want to make a livereport to count new document added last month in the system but I want to exlcude Personal workspaces from that.Please help.Thanks.[To post a comment, use the normal reply function]Forum:Content Server LiveReports ForumContent Server:My Support [To post a comment, use the normal reply function]Topic: Count new documents - Exclued Personal WorkspacesForum: Content Server LiveReports ForumContent Server: My Support
[EXTERNAL] - RE Count new documents - Exclued Personal Workspaces
Posted bycolin.schmidt@cognizant.com (Schmidt, Colin) On 10/11/2018 07:40 AM
Select da.auditid, da.auditstr, da.auditdate, da.dataid, da.performerid,k.name “Performer”, da.valuekey
Posted by baber.amin@albertainnovates.ca (Amin, Baber) On 10/10/2018 01:29 PM
Topic:
OwnerID should always be checked / validated and nothing should be taken as a given because there may always be exceptions. There are a number of systems where the DataID for the EWS is not 2000 rather in some cases (those that were upgraded from 8.0.x of Livelink) could have an EWS of 2001. We have also seen 2002 as the ID for the EWS so it is always prudent to check volumes by using the SubType.
Cheers,
Brian G WalshPrincipal Technical Analyst
Content Server Team
OpenText Corporation1-800-540-7292bwalsh@opentext.com
From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>Sent: Thursday, October 11, 2018 11:38 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: RE Re [EXTERNAL] - RE Count new documents - Exclued Personal Workspaces
RE Re [EXTERNAL] - RE Count new documents - Exclued Personal Workspaces
Posted bycolin.schmidt@cognizant.com (Schmidt, Colin) On 10/11/2018 11:38 AM
Posted by appnair@gmail.com (Nair, Krishnankutty) On 10/11/2018 11:17 AM
[EXTERNAL] - RE Count new documents - Exclued Personal Workspaces Posted by colin.schmidt@cognizant.com (Schmidt, Colin) On 10/11/2018 07:40 AM You’d have to exclude their WS or only look for anything under “DTREE.OWNERID = -2000”. If you have Projects or some other special “container”, you’d have to account for that too. Select da.auditid, da.auditstr, da.auditdate, da.dataid, da.performerid,k.name “Performer”, da.valuekey From dauditnew daJoin dtree dt on da.dataid = dt.dataid and dt.ownerid = -2000Where da.auditid in (1, 11) and da.auditdate >= %1 Colin J From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>Sent: Wednesday, October 10, 2018 12:35 PMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: Count new documents - Exclued Personal Workspaces Count new documents - Exclued Personal Workspaces Posted by baber.amin@albertainnovates.ca (Amin, Baber) On 10/10/2018 01:29 PM Hi,I want to make a livereport to count new document added last month in the system but I want to exlcude Personal workspaces from that.Please help.Thanks.[To post a comment, use the normal reply function]Forum:Content Server LiveReports ForumContent Server:My Support [To post a comment, use the normal reply function]Topic:Count new documents - Exclued Personal Workspaces Forum:Content Server LiveReports ForumContent Server:My Support
Posted by colin.schmidt@cognizant.com (Schmidt, Colin) On 10/11/2018 07:40 AM
Thanks all of you for help.
I have a question here why we are including Dauditnew table when we can get CreateDate from Dtree table itself?
I just want to exclude Personal Workspace and want to count everything under Enterprise Workspace whether Project or any other type of objects.
In my case 2000 is object of Enterprise Workspace. So do you see any problems in the below query?
;WITH CTE AS( Select E.dataid, e.name, e.subtype, 0 Level , E.ParentId, e.CreateDate From dtree E Where E.parentid=2000 UNION ALL Select E.dataid, e.name, e.subtype, c.Level + 1 , E.ParentId, e.CreateDate From dtree E INNER JOIN CTE c on c.dataid = e.parentid)SELECT * FROM CTE where subtype in (0,144)
Hi Baber,
I wonder why would you not use of DTreeAncestors table for gathering children under EW. The following can provide the same result:
select dt.*
from DTreeAncestors anc, DTree dt
where anc.AncestorID = 2000 and
anc.DataID = dt.DataID and
dt.SubType in (0,144)
You may still need to add condition for getting objects with creation date >= specific date.
From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>Sent: Monday, October 15, 2018 3:49 PMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: RE RE Re [EXTERNAL] - RE Count new documents - Exclued Personal Workspaces
RE RE Re [EXTERNAL] - RE Count new documents - Exclued Personal Workspaces
Posted bybaber.amin@albertainnovates.ca (Amin, Baber) On 10/15/2018 03:44 PM
I heard but not sure sometimes Dtree and DtreeAncestors can have some inconsistencies. So that's why I was trying to avoid DtreeAncestors table.
Anyone agrees what I heard?
Right we need to put date filter.
My 2 cents worth...there were issues when it was first introduced but I want to say either V10 or V10.5 had those issues ironed out (definitely by V16). OT uses dTreeAncestors internally (in the oScript) to do some things (ex: disposition searching) so it has to be correct.
Cheers...
Hi Simons,
thanks very much for the explaination.
I use DTREEANCESTORS in a lot that have to do with sub-folders or under a specific Sub-Folder, but for this report, you just need to know what ID is EWS and grab anything underneath. Where this will cause issues of documents being excluded are(among others): Projects. Their Owner is EWS, but anything underneath does not use EWS.
From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>Sent: Tuesday, October 16, 2018 7:16 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: RE RE RE Re [EXTERNAL] - RE Count new documents - Exclued Personal Workspaces
RE RE RE Re [EXTERNAL] - RE Count new documents - Exclued Personal Workspaces
Posted byjohn.simon@capspire.com (Simon, John) On 10/16/2018 09:05 AM
