LiveReport to get info on groups

Hi All,I need a Livereport that can give me all the groups along with the members names in it.I use the query - select %1, %2, id, name from kuaf where deleted = 0 and type = 1, to get all the group names. What should be added to it to also get membernames in a particular group? Also, is it possible to export the groups and its members data in xml format?Awaiting a reply.Thanks,Atiya.

Find more posts tagged with

Comments

Tim_Hunter

Getting group member names is actually pretty easyyou just need to reference kuafchildren which holds a parent/child relationshipSee the following query for an example: SELECT k.ID AS GROUPID , k.NAME AS GROUPNAME , k.TYPE AS GROUPTYPE , c.ID AS USERID , c.LASTNAME AS USERLASTNAME, c.NAME AS USEREMAIL , kc.id FROM kuafchildren kc , kuaf k , kuaf c WHERE kc.ID = k.IDAND c.ID = kc.CHILDIDAND c.id = %1ORDER BY c.ID, k.IDNow if you want to get members of a group in a group (in a group, in a group, etc) it is more difficult as you need to use a heirarchical query (such as oracles connect by)

Atiya_Sultana

Hi Timothy,Thanks for passing the query. It does give the group name and member names but not in a hierarchy. And how to create a LiveReport using your query - I mean what parameters do i have to specify for this?Many Thanks,Atiya.

Tim_Hunter

To create a LiveReport using the query you just need to paste the code provided and prompt for a user or group name for %1Are you using Oracle? If so you can use the following instead of the query above for a hierarchical query (decends into subgroups). Be aware this will be slower for many subgroups.select level as depth,kuc.id,kuc.childid,g.id groupid,g.name groupnamefrom kuafchildren kuc,kuaf gwhere kuc.id = g.idstart with kuc.childid = %1connect by prior kuc.id = kuc.childidorder siblings by childid