LR with projects, where user has rights as coordinator
Hello together,1) I need a livereport for my users, that lists only those projects where the user has role coordinator. (so I cannot use the easy setting "filter permissions", because there the user sees also those where he is entered as guest or member.My problem is, that the user can be entered in the project via his name or via a group (which can enter more groups, so it can be very recursive).I tried a lot, perhaps I'm totally sqlconfused ;-( and don't see an easy solution, athough it sounds very easy...2) Another problem (not as urgent as the first one) First I have written a report, which list me coordinators of all projects under a folder with their emailadress. We would like to contact them and ask them to do something with their projects, and there I also get a solution for the users which are entered via a name or via a group, but not if they are entered in a "sub"-group and this group is entered as coord. Then the mailadress of my report is empty. :-(My Livereport:SELECT dTree.Name as "Workroom", dtree.Dataid, KUAF.Name as "Coordinator", DECODE(kuaf.type, 0, 'User', 1,'Group', 'Other') as "Entered as",DECODE (kuaf.type,0, kuaf.name, 1, k2.name, '?') as "Who",/* if User, print mailadress of user. If group print mailaddresses of containing users (Works only for first group but not for groups inside groups :-( */DECODE (kuaf.type,0, kuaf.mailaddress, 1, k2.mailaddress, '?') as "Mailaddress",DECODE(kuaf.userprivileges || k2.userprivileges, 2062, 'no Login', 2126, 'no Login', kuaf.userprivileges || k2.userprivileges) as "userprivileges" FROM DTree LEFT JOIN KUAFChildren kc1 ON kc1.ID = DTree.DataID + 1 LEFT JOIN KUAF ON KUAF.ID = kc1.ChildIDleft join Kuafchildren kc2 on kc2.id=kuaf.idleft join kuaf k2 on k2.id=kc2.childid/* Left join : also Projects with no coord. will be found */ WHERE (dTree.SUBTYPE = 202 AND dtree.dataid IN (select dtree.DataID from DTree start with dtree.DataID = '12345' /* 12345=dataid of special folder*/ connect by prior DataID = ParentID))ORDER BY "Workroom", "Coordinator", "Mailaddress"If anybody has a helpful hint how to do, it would be great!!!(I had the idea, that the personal.myprojects lists projects with their role, probably this sql could be reduced to show only those with role coordinator, but I don't know how the sql is..)
