List of users grouped by group

Does anyone have an example live report where users are grouped by the user group they belong to?ThanksAlan

Find more posts tagged with

Comments

Hi Dean,We use this basic livereport for this purpose.SELECT A.Name as "Group", (Select Z.lastname from KUAF Z where z.id = B.childID) as "Name",(Select Z.firstname from KUAF Z where z.id = B.childID) as "Firstname",(Select Z.name from KUAF Z where z.id = B.childID) as "User" FROM KUAF A, KUAFChildren B Where A.id <> 1001 AND A.id = B.ID AND A.Type = 1 Order by 1,2Have a good DayHugo

eLink User

Message from Mohsin Jessa via eLinkHi Hugo:May I suggest an alternate to your query?select a.name as "Group", z.lastname as "Name", z.firstname as "Firstname", z.name as "user"from kuaf a, kuaf z, kuafchildren bwhere a.id <> 1001and a.id = b.idand a.type = 1and z.id=b.childidorder by 1,2;Please try this out and let me know if this sql is more or less efficient interms of oracle resources.In my test cases the above query consumed about 1800 logical reads comparedto 203,000 for the original version when the d/b was set for Cost BasedOptimizer mode and there were stats on the tables and indexes. Quite asignificant reduction.However when the d/b was in Rule Based Mode then the above query was atleast 3 times more efficient (about 207,000 logical i/o's compared to 61,000for the modified version).My test case returns over 34,406 records.Of course all the above is valid only for Oracle d/b.Hope this helps.Mohsin JessaOracle Technical Specialist.Customer Support - Escalation Group(519)888-7111 ext 2416-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Thursday, June 19, 2003 11:20 AMTo: eLink RecipientSubject: List of users grouped by groupList of users grouped by groupPosted by Frechette, Hugo on 06/19/2003 11:11 AMHi Dean,We use this basic livereport for this purpose.SELECT A.Name as "Group", (Select Z.lastname from KUAF Z where z.id =B.childID) as "Name",(Select Z.firstname from KUAF Z where z.id = B.childID)as "Firstname",(Select Z.name from KUAF Z where z.id = B.childID) as "User"FROM KUAF A, KUAFChildren B Where A.id <> 1001 AND A.id = B.ID AND A.Type =1 Order by 1,2Have a good DayHugo[To reply to this thread, use your normal e-mail reply function.]============================================================Topic: List of users grouped by grouphttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3085952&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Hugo_Fréchette

Thanks for the optimization. I changed my code.I don't have any tool to test the consumed logical reads on the DB. I use LiveLink to develop my Livereports. Your code is a quite good reduction with the data you show me. Thanks for the great info.Hugo

eLink User

Message from Mohsin Jessa via eLinkHi Hugo:You are most welcome.Mohsin JessaOracle Technical Specialist.Customer Support - Escalation Group(519)888-7111 ext 2416-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Thursday, June 19, 2003 4:11 PMTo: eLink RecipientSubject: List of users grouped by groupList of users grouped by groupPosted by Frechette, Hugo on 06/19/2003 04:03 PMThanks for the optimization. I changed my code.I don't have any tool to test the consumed logical reads on the DB. I useLiveLink to develop my Livereports.Your code is a quite good reduction with the data you show me.Thanks for the great info.Hugo[To reply to this thread, use your normal e-mail reply function.]============================================================Topic: List of users grouped by grouphttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3085952&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe