Create a table with users and group users

[#1]

Hello people,

I'm working with sap bo 4.1 SP5 IDT and I'm trying to develop a multi-source universe populated with 2 tables:

1 table will come from an Excel and it has the column USER_GROUP and some other attributes and measures

The other table, and the one I have problems to develop, should be an autogenerated table with 2 columns, USER and USER_GROUP. I've been trying to create this kind of table with the variable @BOUSER, but I can't find the variable for the USER_GROUP.

I've been searching to create a List of Values table populated with a Query that returns both the USER and USER_GROUP from some table from the CMS repository, but 1) I cannot find the correct query and 2) I've read that CMS tables are encrypted, so I don't know where to look now

Anyone knows where can I find or how to create a simple table with USERS and GROUP_USERS from the BI Platform?

Thanks all in advance.

[Former Member]

Just visit the following link :

http://scn.sap.com/community/bi-platform/blog/2012/10/11/businessobjects-query-builder-queries

there you will find the required info as below:

User/UserGroups queries

To find the number of users in a group

SELECT SI_NAME,SI_GROUP_MEMBERS FROM CI_SYSTEMOBJECTS

WHERE SI_KIND = 'USERGROUP' AND SI_NAME='ADMINISTRATORS' 

To extract all the users from specific user group

SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS
WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='ADMINISTRATORS'")

[Former Member]

Thank you swapnil, I've playing with the query builder but I cannot manage to list the users and their user_group in only one query. I can list both the users and the user_groups but I don't know how to join them.

By the way, how can I implement the Query Builder on the Universe?