LiveReport for Reserved Items in a folder/subfolder

I ned to create a report that will show all reserved item within a folder structure.Can anyone help me get a jumpstart on that?

Find more posts tagged with

Comments

Jim_Coursey

Message from Coursey, Jim <jim.coursey@ngc.com> via eLink

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

You're looking for a "From Here" version of the Reserverd Items LiveReport?

Jim

Chris_Kriegler_(eu0013289_-_(deleted))

Yes, that would be perfect, but it must also search subfolders.

Jeff Lang

The Recommender module maintains a table, although I think it sometimes gets out of sync, called DTreeAncestors. You can use this table to retrieve all the ancestor DataIDs of a node. You can either walk down or up the tree.If you have builder, look at the Recommender Ospace to see how to use the DTreeAncestors table in your query.

Chris_Kriegler_(eu0013289_-_(deleted))

We haven't purchased the builder.I guess I was hoping for a less complicated way.

Jim_Coursey

Message from Coursey, Jim <jim.coursey@ngc.com> via eLink

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

Shouldn't the Schema provide sufficient info for a good DBA to determine what SQL to use?

Jim

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

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

You can use the standard search facility. If you go to advanced search and select 'System Attributes' to display, then you can select 'Reserved Date' from the drop down list, then choose, say, 'The past year', you will then get up all items that were reserved in the past year. To limit your search to just one folder, go to the specific folder and select 'From Here Expanded' in the search bar at the top of the folder, select your system attributes as above and then make sure your scope is 'Within <current folder name>'. Search results are permissioned, so to get all reserved documents in a given area, it would be best to run the search while logged in as a user with System Administration privileges.

- Bhupinder

------------------------------------------------------
Bhupinder Singh, B.Math., B.Ed.
Senior Product Specialist, Customer Support
Open Text Corporation, Waterloo, Ontario, Canada
Customer support e-mail: support@opentext.com
Customer Support Telephone: 800-540-7292
------------------------------------------------------

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, January 25, 2007 11:59 AM
To: eLink Recipient
Subject: LiveReport for Reserved Items in a folder/subfolder

LiveReport for Reserved Items in a folder/subfolder

Posted by eu0013289 (Fisher, Brian) on 01/25/2007 11:53 AM

I ned to create a report that will show all reserved item within a folder structure.
Can anyone help me get a jumpstart on that?

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

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

If you do wish to write a LiveReport (instead of using the search facility recommended earlier), you could look at any one or combination of the following fields in your DTREE table.

RESERVED

RESERVEDBY

RESERVEDDATE

I noticed on my system that if a document is reserved, the DTREE.Reserved field has a value of 128.

- Bhupinder

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, January 25, 2007 1:37 PM
To: eLink Recipient
Subject: RE Too comlpicated......

RE Too comlpicated......		Posted by grmmun02user8 (Coursey, Jim) on 01/25/2007 01:36 PM
In reply to: Too comlpicated......		Posted by eu0013289 (Fisher, Brian) on 01/25/2007 01:23 PM

Message from Coursey, Jim <jim.coursey@ngc.com> via eLink

Shouldn't the Schema provide sufficient info for a good DBA to determine what SQL to use? Jim

Chris_Kriegler_(eu0013289_-_(deleted))

This tells me items that were erserved in the past year, but the item may not be currently reserved. I want to see all currently reserved documents within a folder/subfolder.

Jeff Lang

Try this SQL. Replace 2000 with the DataID of the top level folder you are looking at.select * from DTree where Reserved != 0 andDataID IN (SELECT DataID from DTreeAncestors where AncestorID = 2000 OR AncestorID = (-1 * 2000)) order by Name

Chris_Kriegler_(eu0013289_-_(deleted))

I modified your query to this below:select * from DTree where Reserved != 0 and DataID IN (SELECT DataID from DTreeAncestors where AncestorID = 9962 OR AncestorID = (-1 * 9962)) order by Name9962 is the ObjectID of the folder I want to search in, is that not the correct number?

Jeff Lang

I would remove the Reserved != 0 part and see what nodes are returned. Check to see if any are missing from the hierarchy. If so, it's a bug in recommender. I think there is a section on the admin.index page that lets you recreate the DTreeAncestors table. You might try that, but it can take a while for the recreation.If there are missing nodes from the returned query, I would report it to support as a bug.

Kenneth_Huie

Guys this is a simple query isn't it? I mean there is already a Report from Personal>Reserved Items and there is a canned LiveReport to show ALL reserved items. I'm no DBA, but there has GOT to be a simple means to show all CURRENTLY reserved items in a particular directory in Content Server.

Kyle_Swidrovich

Hello Kenneth,To restrict to a specific folder, tweaking the canned report with a restriction against ParentID would suffice, however, it would not include all child folders. To include all child folders, one would need something like a CONNECT BY PRIOR (or MS SQL equivalent) to recursively walk the ParentID/DataID structure.The previously suggested query by Jeff Lang to go against DTreeAncestors should work, provided that your DTreeAncestors table data is accurate. With older codelines, it may be inaccurate, however, the most current codeline should have resolved these inaccuracies.