More Project Reports

Cynthia_Philpott

I have a department administrator the is responsible for all the users that belong to that departments projects. All the projects are located in a specific folder. The administrator wants to get a count of users per project and in a separate list all users and the projects they belong to within her departments folder. Using the department folder's as the parent id, I was able to run a report of users within the projects having that Parent Id. Unfortunatly the report does not take into account that some users in the projects are actually user groups (and I miss those users in the count) and many of the projects contain subprojects that don't show up at all in my report because their parent id is not the department folder it's the project dataid. Can I get all the projects and all the users within the groups both in the projects and the subprojects. I don't care if a user is counted twice because they belong to more than one project in my project user count report. But I don't want to count users in all projects in livelink just this departments projects. Here is the code I'm using for the Project Users Count report(Oracle)SELECT d1.name "project", count(d1.Name) "Participant Count" FROM KUAF,DTree d1,KUAFChildren,KUAF KUAF1 WHERE ((KUAF.Type = D1.DataID and KUAF.ID = KUAFChildren.ID and KUAFChildren.ChildID = KUAF1.ID) and d1.subtype = 202 and d1.parentid = 1456134) or ((KUAF.Type = D1.DataID and KUAF.ID = KUAFChildren.ID and KUAFChildren.ChildID = KUAF1.ID) and d1.subtype = 202 and d1.parentid = 1456134 and d1.dataid = (select d2.dataid from dtree d2 where d2.dataid = d2.parentid)) group BY D1.Name