User Membership in Projects

Nara_Beybutova_(boozuser1_(Delete)_1545241)

We have a need to find out what projects particular user belongs to from time to time. We have came with sql query for that:col "Project ID" for 999999999col "Project" for a60col "Role" for a12select h.id "Project ID",k.name "Role",d.name "Project"from kuaf k, dtree d,(select id from kuafchildrenstart with childid=&IDconnect by prior id=childid) hwherek.id=h.idand d.dataid=k.typeand d.subtype=202order by d.name;Then it prompts me for user id and gives me list of user's project listed by project role.Could somebody help me out with this?thanks,Nara

Dave_Ebels_(jocoadmin_-_(deleted))

I use two reports for this, the first, called "User / Project membership - with guest" brings back a users project membership status including guest status. It is set up as an Auto LiveReport, it prompts for a user name, so in the 'Inputs' section I have type 'User' with a prompt that says 'select user'. In the Param %1: slot I have 'User Input 1'. Finally, in the Display columns area, I list 'Field(s)' ID, Group, Project, with column titles 'Project ID, Membership Status, and Project Name' respectively. Add the following SQL and run it. The return will be all the project memberships, including geust memberships, for the user you choose.SELECT G.ID,G.NAME "Group",DECODE(P.NAME, NULL, ' ',P.NAME) "Project" FROM KUAF G, DTree P WHERE(G.Type = 1 OR G.Type >= 2000) and g.name in ('Members','Coordinators','Guests') AND G.Deleted = 0 AND G.Type = P.DataID (+) AND G.ID IN ( SELECT ID FROM KUAFChildren START WITH ChildID = %1 CONNECT BY PRIOR ID = ChildID ) ORDER BY G.Name, P.NameThe next report I call "User / Project membership - no guest" and is set up exactly the same but leaves out the 'Guest' memberships. The SQL is ,,SELECT G.ID,G.NAME "Group",DECODE(P.NAME, NULL, ' ',P.NAME) "Project" FROM KUAF G, DTree P WHERE(G.Type = 1 OR G.Type >= 2000) and g.name in ('Members','Coordinators') AND G.Deleted = 0 AND G.Type = P.DataID (+) AND G.ID IN ( SELECT ID FROM KUAFChildren START WITH ChildID = %1 CONNECT BY PRIOR ID = ChildID ) ORDER BY G.Name, P.NameI hope this works well for you.Dave Ebels, Johnson Controls -- dave.j.ebels@jci.com