Report on documents within a project

Andy_Cater_(stmshipadmin_-_(deleted))

Does anyone know how to create a LiveReport to show all documents for a selected Project. I have a report that only shows documents in the root level of the project, and not in any of the sub folders. Once I have this report I can modify it to show checked out docs (my ultimate goal).Thanks in advance,Simon

eLink User

Message from David Slimmon via eLinkHi there,To construct a LiveReport that gets at the kind of information you're looking for,you'll need to do some digging in the database first. Specifically, you'll need toget the ParentID, DataID, and Name from the DTree table for each of the elements inquestion (the project you're interested in, its sub-folders, and the sub-folders'documents). But I'll give you a hand through the use of an example.Here's what you might try:1. Query the DTree table to find the DataID for the project you're interested in.For example, in my database, I ran the following query:SELECT Name, DataIDFROM DTreeWHERE SubType=202This returned a listing for all my projects. From this listing, I determined thatthe DataID for the project I was interested in was 6054. Once I had thisinformation, I took another look at DTree to find out which objects are its children.You'll be able to recognize its children by looking at their ParentID value -- itwill be same value as the DataID for the project you're after EXCEPT that it will(should) be a negative number. In my case, this means that the value in ParentID Iwas looking for was -6054.This meant that in my case, I could have run something like the following:SELECT DataID, Name, SubTypeFROM DTreeWHERE ParentID=-6054This returns some interesting results, but it's not quite specific enough. This isprobably similar to what you have now. Using this kind of query, you can probablyfigure out which documents are directly beneath the Project and you might even beable to pick them out by name. (If not you can certainly look for SubType=144 whichis the SubType for a document). But the problem with this is that we want to get atthe folders underneath this project. To find this stuff out, just append anadditional SubType statement to the end of the previous query in order to weed outanything that's not a folder. For example:SELECT DataID, NameFROM DTreeWHERE ParentID=-6054AND SubType=0This query returns the DataID and the Name for ONLY the folders beneath the projectwe're interested in. In my case, I got back two (2) folders. They were calledSubFolder1 and SubFolder2 and their DataIDs were 9773 and 9663 respectively. Inorder to get at all the documents underneath each of these 2 folders, we're actuallygoing to do more or less the same thing that we did to figure out which folders wereunderneath the main project. The only difference is, we're not going to replace theParentID with a negative value. So, I should get all of the documents under all ofthe sub-folders in the project we're interested in if I issue the following query:SELECT DataID, ParentID, NameFROM DTreeWHERE ParentID=9773OR ParentID=9663AND SubType=144ORDER BY ParentIDFor me, the results end up looking like this:DataID ParentID Name=============================9672 9663 document1.txt9668 9663 document2.txt9664 9663 document3.txt9680 9773 document4.txt9676 9773 document5.txt9553 9773 document6.txtAll of the documents ordered by the ParentID 9663 belong to one subfolder in theproject and all of the documents ordered by the ParentID 9773 belong to the othersubfolder.These are just some ideas to get you started. By all means drop me a note in thisforum or contact us directly at Customer Support if you have any further questions onthis issue. We can be reached at support@opentext.com.Regards,Dave-----Original Message-----Report on documents within a projectDoes anyone know how to create a LiveReport to show all documents for aselected Project. I have a report that only shows documents in the rootlevel of the project, and not in any of the sub folders. Once I have thisreport I can modify it to show checked out docs (my ultimate goal).Thanks in advance,Simon