BusinessObjects Board

XIR2 Query Builder Admin Reports

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 :roll_eyes:


cva :uk: (BOB member since 2006-05-20)

Hi Siva,

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

Regards
Sebastien

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)

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 :us: (BOB member since 2006-08-30)

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)

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

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 :us: (BOB member since 2006-08-30)

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)

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


blair :canada: (BOB member since 2006-11-17)

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)

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:

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 :us: (BOB member since 2006-08-30)

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


RikDeclercq :belgium: (BOB member since 2006-09-28)

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. :mrgreen:


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

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)

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 :

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)

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

Thanks
Bryan


bthomps01 :us: (BOB member since 2007-01-09)

Yes, you can either change the registry setting at:

HKEY_LOCAL_MACHINE -> SOFTWARE -> Business Objects -> Suite 11.5 -> CMS -> Instances -> , called “'InfoStoreDefaultTopNValue”

or you can just specify

“select TOP ##### (5000, 10000, etc) column1, column2, …”

in your query to retrieve more than 1000 results.


crystal01 :us: (BOB member since 2006-08-30)

worked perfect – Thanks

Bryan


bthomps01 :us: (BOB member since 2007-01-09)

reports by category

I’ve had a look to see if there’s a way of getting reports in a category through the Query Builder, and there doesn’t seem to be a way of doing this.

Example
What I’m expecting is to get reports by category but no luck …………

Report Name Category Name
ABC Report ----- X Universe
PQR Report ----- Y Universe

any help is much apreciated on this.

Thanks in advance,
Cva


cva :uk: (BOB member since 2006-05-20)

2 ways of going about this within QB: look at the si_documents field for a particular category object, this will hold the si_ids of reports in this category. OR you can look at the si_corporate_categories field for a given report object, this will hold the category si_id.

That said, if you want this information in a reportable format you’ll need to use the SDK or a 3rd party tool (system profiler, etc)…


crystal01 :us: (BOB member since 2006-08-30)

That 1000 limit in Query Builder can be changed.


Keith Crump (BOB member since 2002-08-15)