Query Builder query

[Former Member]

Hello,

I need to get the below details using query builder

--> the list of users who belong to a specific group, do they belong to any other group. I tried the below query and its failing.. Any other way of achieving this?

Select     SI_ID, SI_NAME From     CI_SYSTEMOBJECTS WHERE     PARENTS("SI_NAME='UserGroup-User'",             "CHILDREN('SI_NAME=''UserGroup-User'' ',                       'SI_NAME=''Administrators'' ')")

Regards,

LAxminarayana

[CdnConnection]

    You can use sample JDK Queries from XI 3.1, it uses the same JAVA library code.

Ajay

[former_member194139]

One more quick Q I've is, I'm running the below Query and my results are limited to 1000. Where I can see 2704 user under this group.

Do we have any number of results restriction in Query Builder? Thanks!

SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS

WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='ADMINISTRATORS'")

[Former Member]

Hi Stephenes s,

Query Builder limits the result set to 1,000 by default. To exceed this limit, simply insert "TOP N" after SELECT.

For example:

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

[former_member194139]

Thank you !!

[former_member182521]

Hi Laxminarayana,

Kindly remove the Single quote in your Query and replace it manually with the one using Keyboard. Seems you are using a different Symbol

[Former Member]

I have tried all possibilities still  does not work. Did you get a chance to check this at ur end. I would appreciate if you share a working query

Thanks,

Laxmi

[former_member182521]

Can we try the below

SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS

WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='ADMINISTRATORS'")

For more Query builder queries have a look here.

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

Regards,

Mani

[former_member194139]

How do i get the results in a spreadsheet.

Thanks!

[Former Member]

You can copy all the results and paste in to excel. Or you can write a java code to export to excel.

[Former Member]

Hi Stephenes,

There is not any direct method to download Query Builder results into Excel or any other format. Query builder results are displayed in HTML format only.

However following is the method to convert those html format results into Excel file.

--Open Query Builder and run any query to fetch results from database.
--After executing the query the results will be displayed in a web page.
--Either we can copy all the results and paste it into the Excel file and save it.
--Or save the web page, now open Excel sheet and click on File > Open > open the web page which you saved before.
--Now save the file in Excel format.

Regards,
Nachiket