Creating Live reports

[psparla]

Hi

we currently have a Live report which searches the folder for Projects and returns the coordinators of the projects.
But we need to search also the subfolders of the folder. So for example

FOLDER 1
-Project 1
-Project 2
-Folder 2
--Project 4
--Folder 3
-Project 3

We need to enter the folder (folder 1) and it returns only the coordinators of Projects 1-2-3. We need to adjust the query so it also searches the subfolders and also returns the coordinator of Project 4

Any idea?

Query
(
select d.name as "Project", d.dataid as "Nickname",
k2.lastname+ ', ' + k2.firstname + ' (' + k2.name + ')' "User",
case k1.name when 'Coordinators' then 'coordinator' when 'Members' then 'member' when 'Guests' then 'guest' end "Role",
'' "User added as group member of"
from livelink.dtree d,
livelink.kuaf k1,
livelink.kuafchildren kc1,
livelink.kuaf k2
where d.parentid = %1 and
d.dataid = k1.type and
k1.id = kc1.id and
kc1.childid = k2.id and
d.subtype = 202 and
k2.type = 0 and
k1.name = 'Coordinators'
union
select d.name as "Project", d.dataid as "Nickname",
k3.lastname+ ', ' + k3.firstname + ' (' + k3.name + ')' "User",
case k1.name when 'Coordinators' then 'coordinator' when 'Members' then 'member' when 'Guests' then 'guest' end "Role",
k2.name "User added as group member of"
from livelink.dtree d,
livelink.kuaf k1,
livelink.kuafchildren kc1,
livelink.kuaf k2,
livelink.kuafchildren kc2,
livelink.kuaf k3
where d.parentid = %1 and
d.dataid = k1.type and
k1.id = kc1.id and
kc1.childid = k2.id and
d.subtype = 202 and
k2.type = 1 and
k2.id = kc2.id and
kc2.childid = k3.id and
k2.type = 1 and
k1.name = 'Coordinators'
)
order by "Project"



--------------------------------------------------------------------------------

Inputs: # Prompt Type Prompt Text
1 Container Select a folder:

Parameters: % Parameter Type
%1 User Input 1

 

[appnair]

what is your livelink database.if it is oracle you can search for 'connect by' clause.If you are on sqlserver for object traversal you should use CTE's or functions.If your livelink is on the latest and greatest patch a simple query involving dtreeancestors can also be used.All of these have many examples in the livelink knowledge base.

http://knowledge.opentext.com

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair