XIR2 Query Builder Admin Reports

[system]

[Is everyone doing this though the “Business Objects Business Intelligence platform - Query Builder” Its works, but there is no way I can adjust the formatting. Is there another way to run the SQL in like a webi doc?]

That’s the point - there is no way to format the output, it is not meant for large dataset returns and NO you cannot run the sql in any of the reporting tools.[/quote]

---

blair (BOB member since 2006-11-17)

[system]

[albertmoreno wrote: …but I’m not able to query those users that have secEnterprise disabled (SI_DISABLED=true)… ]

Unfortunately SI_DISABLED is a sub property of the ALIASES property of the user class so i cannot find a way to show only disabled users through a query, you’ll have t use the SDK. Good Luck.

---

mohammedq (BOB member since 2006-02-01)

[system]

Where there’s a Will, there’s a way. Open Excel and copy and paste the Query Builder results from IE. If you are careful with your Query Builder queries and don’t include any fields that vary in verticle size, you can manipulate the output in Excel using =if(A6=“SI_NAME”,B6,"") style formulas in subsequent columns. Once you get the results you want, copy the fomlulas down the rows. Finally, copy and Paste-Special by Values into the next set of columns and sort to group them. Keep in mind that Query Builder is limited to 1000 objects returned.

PCGEEKUS

---

pcgeekus (BOB member since 2003-10-16)

[system]

Hi

        Thanx a ton.

Harish

---

harishreddy (BOB member since 2006-05-11)

[SebastienGoiffon]

Hi all,

Is it possible using Query builder to get the list of all users from a specific group ?

Thanks in advance

Regards

[system]

Or U can build the Universe based on this tables
CMS_Aliases5
CMS_IdNumber5
CMS_InfoObjects5
CMS_RELATIONS5
CMC_VersionInfo

and build the reports in Required format…

any ideas is much apreciated on building the universe on Repository.
because we are planing to build the univese on Repository.

Thanks in advance,
Siva

---

cva (BOB member since 2006-05-20)

[SebastienGoiffon]

Hi Siva,

You can no longer query the reporsitory using standard SQL. You can do this only using sdk.

Regards
Sebastien

[system]

Hi,

Can we create a universe that connects these CMC tables such as CI_INFOOBJECTS and reports off it? Or, is there a way to create Webi reports from tables such as CI_INFOOBJECTS?

Thanks.

---

ywmui (BOB member since 2006-05-01)

[system]

@ywmui

Hi,

Can we create a universe that connects these CMC tables such as CI_INFOOBJECTS and reports off it? Or, is there a way to create Webi reports from tables such as CI_INFOOBJECTS?

Thanks.

Yes (but not easily). I have normalized and extracted the XI R2 metadata layer via a utility I wrote. Once you have the contents extracted, normalized, and loaded properly, creating an ad-hoc universe on top of it will allow much more flexibility in environment reporting, and allow you to report on just about anything you’d like.

---

crystal01 (BOB member since 2006-08-30)

[system]

Do you have a snippet to generate object references? I’m looking for a way to figure out which objects are used in which reports?

---

keithcrosslin (BOB member since 2007-09-14)

[system]

You will probably be best off getting this information by using the WID Scanner utility:

WID Scanner

version: BOXI rls2 platform: MS Windows JBO jbo_911@hotmail.com download grep @ http://sourceforge.net/project/showfiles.php?group_id=23617 Updated 8/14/2007 – Version 1.1: -fix export path, spaces were not allowed -added FullClient documents to documentlist (no object scan is done, other tools available) -added filesize in KB of the documents -Instances are (if wanted) excluded in a proper way WIDScanner_1_2.zip (43.0 KB) jbo (BOB member since 2005-07-31)

There is also a good Universe Documentor utility available in downloads if you are looking for a quick way to get objects by universe.

---

crystal01 (BOB member since 2006-08-30)

[system]

Is there any way to get a list of users by group…In other words, a list of users for a specific group?

Please advise…

Rita

---

sturgra (BOB member since 2006-11-08)

[system]

Users by Group ??? this info is available in th CMC.

---

blair (BOB member since 2006-11-17)

[system]

I understand that I can “See” that information in the CMC but in order to do a security audit, I need to be able to export a list by user group to send to data owners for review…Security Viewer only allows an export of all users but not users by group. I was hoping there was a way to query this somehow…

---

sturgra (BOB member since 2006-11-08)

[system]

You really have two options in order to get this information in a useable format:

1. Hack your way through with Query Builder running two queries, one to retrieve all users, and the other to retrieve all groups. Copy and paste into Excel, mess with formatting, and get creative to join them. The Group SI_ID will correspond with the User SI_USERGROUPS value(s).

2. Approach this programatically. Someone in BO tech support released a code snippet which gets you exactly what you’re looking for:

SAP Community Home

You could also look into 3rd party metadata tools if you want a more comprehensive solution. (we have one - contact me if interested)

Regards

Brian Yaremych
Analytics8

---

crystal01 (BOB member since 2006-08-30)

[system]

@pcgeekus

Where there’s a Will, there’s a way. Open Excel and copy and paste the Query Builder results from IE. If you are careful with your Query Builder queries and don’t include any fields that vary in verticle size, you can manipulate the output in Excel using =if(A6=“SI_NAME”,B6,"") style formulas in subsequent columns. Once you get the results you want, copy the fomlulas down the rows. Finally, copy and Paste-Special by Values into the next set of columns and sort to group them. Keep in mind that Query Builder is limited to 1000 objects returned.

PCGEEKUS

You can bypass the limit of 1000 objects by :
Select Top N from …
with N > 1000 so for example
select Top 5000 from

---

RikDeclercq (BOB member since 2006-09-28)

[system]

Below is the jsp code to get all users by groups in HTML format:

IInfoObject group = null;
IInfoObject user = null;
String usernames="";
try{

IInfoObjects groups = infoStore.query("Select SI_ID, SI_NAME From "
             + "CI_SYSTEMOBJECTS Where SI_PROGID='CrystalEnterprise.UserGroup'");

IInfoObjects groups1=null;
IInfoObject group1=null;
Object[] memberUsers;
IInfoObjects result=null;
IInfoObject iObject=null;
if (groups.size() == 0)
{
out.println(“No available groups”);
}
else
{
// Retrieve all of the user groups that were returned by the
// query.

    for (int i = 0; i < groups.size(); i++)
    {
	usernames="";
        group = (IInfoObject) groups.get(i);

if(!((group.getTitle()).equals(“Everyone”)))
{
groups1 = infoStore.query("Select TOP 1* From CI_SYSTEMOBJECTS "
+ “Where SI_ID=” + group.getID());
group1 = (IInfoObject) groups1.get(0);
memberUsers = ((IUserGroup)group1).getUsers().toArray();
for (int j = 0; j < memberUsers.length; j++)
{
// Retrieve each group from the infoStore.
result = infoStore.query ("SELECT SI_NAME FROM "
+ “CI_SYSTEMOBJECTS WHERE SI_ID=” + memberUsers[j]);
iObject = (IInfoObject) result.get(0);
usernames = usernames + iObject.getTitle()+ " / ";
}
%>
<%=group.getTitle()%>
<%=usernames%>

<% }} } } catch (SDKException e) { throw new Error ("Failed to retrieve the groups. Exception caught: " + e.getMessage()); }

Hope this helps.

---

Anand Gupta (BOB member since 2008-03-03)

[system]

In response to the posts in this thread asking about disabled users, we have a freeware tool available that might help (Account Enabler). Displays all users and their status and allows you to quickly switch between enabled and disabled state via a checkbox. Very simple, but it works and it’s free.

We also have a full client query builder and export tool available in the very near future.

JGreg
www.zoom-software.com

---

JGreg (BOB member since 2007-11-21)

[system]

In the download section there is a excel file which gives you all the information about groups and users. The file name is BOXI_UserListV3.xls.

you can find it here :

BOXI UserList & Group Extraction

Here is a little excel file with a bit of VBA code to extract from a CMS the complete list of user and the list of groups each user is member of. Quite useful when you need to check group membership. It’s now possible to edit the CMS data, simply by modify entries in the Excel File. You need then to export your modification back to the CMS. Modifiables column are : Login, FullName, Email, Disabled and Description. Platform: BO XI R2 / BOXI3.1 (it’s necessary to change the libs to fits XI3.1, s…

This excel writes all the groups which a user belongs in to one cell. So you can not filter by group. But it is very easy to change the code.

Here is my version of this xls file. The only difference is, it writes each group which a user belongs in to a new cell.

Feel free to try both files and change it to your own needs.
BOXI_UserListV3_1.xls (56.0 KB)

---

bozgul (BOB member since 2006-11-06)

[system]

Is there a setting in the register that can be manipulated to allow more than 1000 results?

Thanks
Bryan

---

bthomps01 (BOB member since 2007-01-09)