Query Builder - Get list of users in a group

security CMC
1

Have a requirement to provide a list of users within specific groups. I am trying to accomplish this via Query Builder in XIR3.1

If I do this:

select *
from ci_systemobjects
where si_kind = ‘usergroup’ and
si_name = ‘[group name here]’

This gives me a list of users in the group I select, BUT they are the object IDs, not the user name.

On the flip side, I’ve thought of querying users (select * from ci_systemobjects where si_kind = ‘user’) and returning only users in certain groups, but I cannot figure out how to do that. The user data returned shows the groups they belong to under the “usergroups” field, (its actually the group IDs) but I cannot figure out how to limit on this field in the where clause.

Any ideas?

CSGDrew :us: (BOB member since 2008-02-12)

2

You can use a realtionship query to bypass the IDs and get right to the names:

select * from ci_systemobjects where children("si_name = 'usergroup-user'", "si_name = '<GROUP NAME HERE>'")

Edit

I hit “submit” instead of “preview”. Anyway, this will return all users in whichever group you specify.

BoB LoblaW :us: (BOB member since 2007-10-23)

3

That was beautiful. Thanks a million.

I was trying to play around with “relationship” queries in the CMC but couldnt get anywhere… it wouldnt even let me create one.

Just curious, how did you learn that syntax? I feel like this is something I should know as a BO admin, but I somehow missed the train on it…

CSGDrew :us: (BOB member since 2008-02-12)

4

I learned of the syntax from Ted Ueda’s excellent post on his SAP blog:

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/11238

Relationship queries (and path queries) are perhaps the biggest gain in productivity I’ve experienced in my time with SDK development. No more boilerplate code to invent your own subquery logic. I see it as a glaring omission that they are not mentioned in the (enterprise) SDK development guide.

BoB LoblaW :us: (BOB member since 2007-10-23)

5

New link to Ted’s post is here
https://blogs.sap.com/2008/10/19/businessobjects-enterprise-sdk-relationship-queries/#

Cheers,
Chris

ChristianKey :uk: (BOB member since 2008-09-19)