BusinessObjects Query builder queries – Part IV
Dear Folks,
Again this is the continuation of my previous blogs on Query builder.
BusinessObjects Query builder queries
BusinessObjects Query builder queries – Part II
BusinessObjects Query builder queries – Part III
Have listed few more interesting queries below
To list all the servers and their status with server category
SELECT SI_NAME, SI_SERVER_IS_ALIVE, SI_DISABLED, SI_SERVER_KIND,
SI_FRIENDLY_NAME, SI_EXPECTED_RUN_STATE FROM CI_SYSTEMOBJECTS
WHERE SI_KIND=’SERVER’
Below are the possible values for SI_EXPECTED_RUN_STATE object
-1 -> Server is currently in an invalid state due to a configuration error.
0 -> The expected state of the server is stopped.
1 -> The expected state of the server is running.
2 -> The expected state of the server is restarting.
3 -> The server is not being managed by the Server Intelligence Agent.
4 -> The expected state of the server is immediate shutdown.
To list all hosted services for each server
SELECT SI_ID, SI_NAME, SI_HOSTED_SERVICES FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’SERVER’
To list all the metrics description
SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’MetricDescriptions’
To list all the program objects without their instances
SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’PROGRAM’ AND SI_INSTANCE=0
To list all the personal and corporate category details of the report
SELECT SI_ID, SI_NAME, SI_PERSONAL_CATEGORIES, SI_CORPORATE_CATEGORIES
FROM CI_INFOOBJECTS WHERE SI_KIND=’WEBI’ AND SI_NAME = <Name of the Report>
Relationship queries
To list the Usergroups associated with a particular User
Select SI_ID, SI_NAME From CI_SYSTEMOBJECTS Where PARENTS(“SI_NAME=’UserGroup-User'”,”SI_NAME=’Administrator'”)
To list the Universes associated with a particular Data connection
Select SI_ID,SI_NAME,SI_KIND From CI_APPOBJECTS
where CHILDREN(“SI_NAME=’DataConnection-Universe'”,”SI_NAME=’efashion-webi'”)
The list of relationship objects and their relationship types are given below
Relationship Objects Relationship Types
Profile-Principal -> PARENT-CHILDREN
DataConnection-Universe -> CHILD-PARENT
Category-Document -> PARENT-CHILDREN
User-Inbox -> PARENT-CHILDREN
Webi-Universe -> PARENT-CHILDREN
User-Favorites -> PARENT-CHILDREN
UserGroup-User -> PARENT-CHILDREN
Universe(Core)-Universe -> CHILD-PARENTS
EnterpriseData-Flash -> CHILD-PARENTS
Universe-UserGroup -> PARENT-CHILDREN
CustomRole-Object -> CHILD-PARENTS
User-PersonalCategory -> PARENT-CHILDREN
Hope you find this Interesting. Thanks for reading.
Query Builder Blog series
Basics
BusinessObjects Query builder – Basics
BusinessObjects Query builder – Best practices & Usability
Sample Queries
BusinessObjects Query builder queries
BusinessObjects Query builder queries – Part II
BusinessObjects Query builder queries – Part III
BusinessObjects Query builder queries – Part IV
BusinessObjects Query builder – Exploring Visualization Objects
BusinessObjects Query builder – Exploring Monitoring Objects
BusinessObjects Query builder – Exploring Lumira & Design studio Objects
Use cases
BusinessObjects Environment assessment using Query builder
These have been invaluable. Thanks! I've been trying to piece together something like this for a few weeks now
Much appreciated Damone. Thanks!
Hi Mani,
Your series is Much Useful !!!
But still i would like to have some clarifications on the below queries.
I am doing some impact analysis and we would require to delete few objects from each universes but before doing that i would like to make sure those objects are not using in any of the REPORTS. So, I would take a report with informations Universe Name, Report Name, Object Name for each universes.
I beleive this can be done using QUERY BUILDER.
For Universe Name and Report Name i can use the below query from your series. BUT CAN YOU PLEASE HELP TO GET RESULTS FOR OBJECT NAMES FROM EACH UNIVERSES WHICH USED IN ALL WEBI REPORTS RELATED TO THAT UNIVERSE?
To Show count of reports per Universe
SELECT SI_NAME, SI_WEBI FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_WEBI.SI_TOTAL > 0
To retrieve all Web Intelligence reports connected to a Universe
SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
WHERE PARENTS("SI_NAME='WEBI-UNIVERSE'","SI_NAME =’EFASHION’")
Thanks,
Raj
Hi,
Yo may refer the thread Query Builder - Find WEBI reports that are using a table
Hope this assists.
Regards,
Sandeep
Mani,
Your query builder queries are very helpful. I have very unique requirement. We are on BOXi 3.1 SP7 we have crystal reports based on universe with prompts also coming from universe but same report has 2 or 3 prompts coming form Business View (due to cascading reason we used them from BV). I need to list these reports.
Your earliest help is appreciated.
Thanks
Great work!
p.s. is there a way to get list of reports assigned to specified group?
Mani,
Good series of blogs .... I was looking through them for an example on trying to list the Usergroups associated with a particular Folder.
If you have an exmple tha would be really helpful? Is it possible?
Mike
Hi
Great Work...Keep it up.....Thanks For Sharing....
I want to know the relationship which you have posted as follow
Universe-UserGroup -> PARENT-CHILDREN
Can you provide me Query for this relation...
i tried this query
Select SI_ID, SI_NAME From CI_APPOBJECTS,CI_SYSTEMOBJECTS Where
PARENTS("SI_NAME='Universe-UserGroup'","SI_NAME='sample_universe2'")
but its returning me Null;
i need to list the usergroup who all are having access to the universe.
Many Thanks,
Surendar.K
Hi
I need total number of objects stored in all user's personal folder.
Anybody provided query to get this
Thanks
Venkat
Hello,
I am new to Business Objects. Your Query Builder series is an amazing study material. I have learnt a lot from it.
However, there's one thing which is bugging me right now. I want to find out the number of users who have access to WEBI reports in CMC. I have tried with some queries in Query Builder, but all my efforts have gone in vain.
Could you please help me? Thanks in advance.
Regards.
Hi Max,
You will get minimal information from Query builder as far as security is being concerned. It atleast needs more than 2 queries to get the security information which is not possible in Querybuilder as it wont supports multiple table joins or nested queries. I would recommend you to go and try Security query. Please refer here.
BusinessObjects Administration - The less known Security Query
Hi Mani,
First of all thank you very much for being such a big contributor in this forum, great information and well delivered and explained with utmost simplicity
I was wondering if any of your blogs have already covered the list of values each field can represent and the meaning of each value ...for example ...in this blog itself you have shared the various states of a server
-1 -> Server is currently in an invalid state due to a configuration error.
0 -> The expected state of the server is stopped.
1 -> The expected state of the server is running.
2 -> The expected state of the server is restarting.
3 -> The server is not being managed by the Server Intelligence Agent.
4 -> The expected state of the server is immediate shutdown
Likewise is there any other blog or site I can refer to get the various types of scheduled reports, event based , non-event based, weekly, monthly etc..
This need not limit to the scheduled types alone, but can also address other areas as well.
Thanks in advance.
Parshu
Please refer all my blogs in Query builder series and the comments section in each blog as well. You will get almost all the related information there.
Hi Mani,
Could you help me to create a query for the unx universe?
You did have a good example for the unv universe, like:
SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
WHERE PARENTS("SI_NAME='WEBI-UNIVERSE'","SI_NAME =’EFASHION’")
If I have the 'efashion.unx', how do I retrieve all Web Intelligence reports connected to it?
Many thanks,
Johnny
Hi Johny,
I have something which may help you.
There is something called SI_FILES and SI_WEBI.
SI_FILES - It shows you all the objects which the object of interest has relationship with. This may include connections, reports, parent folders, universes etc.
SI_WEBI - This displays the number of WEBI reports that a universe has under it.
In your case, you are trying to view all the WEBI reports that a particular universe has under them. You may try the following query :
SELECT SI_NAME, SI_ID, SI_WEBI, SI_FILES
FROM CI_APPOBJECTS
WHERE SI_ID = ?????
The SI_ID in the where clause is the ID of the universe that you are interested in.
But, there is a drawback. You can't view the names of the WEBI files. The SI_FILES will return the ID of the WEBI files only. JOINS are not supported in query builder.
I hope this helps a bit, but, I recommend, you should wait for Mani's reply.
Thanks Max. It's very helpful for me.
Johnny,
Can we try this
SELECT SI_ID,SI_NAME, SI_SL_DOCUMENTS FROM CI_APPOBJECTS WHERE SI_KIND= 'DSL.Universe'
This should extract the list of reports for all the .unx universes.
I am yet to check for Relationship queries. Will keep you posted
Regards
Mani
Mani,
Thank you very much. With your query, I'm good at solving problems.
Best Regards,
Johnny
Hi Mani,
I am referring your blogs for QB and it is really very useful. We have couple of issues and trying to find some solutions. Please let me know if you have any idea.
1. Unfortunately Infra team migrated AD groups in the first phase migration.
2. Now we are migrating major applications which has 75K User id's (50K + is enterprise), in that we have 300 main groups and sub groups.
Now we need to assign this id's manually to each group which is really hetic process and time consuming. Else we have do delete the existing id's and remigrate which will not work because many of the users are already active.
Do you have any workaround idea for this?
Coming to the QB question i tried to extract all 300+ groups and users, i can extract users but not able to find group name. for example
user id
xxxx
but we are looking like below
user id user group name
xxxx groupname1
xxxx groupname2
xxxyz groupname1
We tried with ur Queries but it is giving user names only not giving corresponding user groups.
Can you help me with ur idea/query?
Hi Mani,
Is there a way to get list of dynamic and enterprise recipients on a publication/schedule using query builder
Hi Mani,
Could you please help me to create a query to identify list of all report Names and report path based on universe
Currently we are using bleow query for list of report names but report path is missing
SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
WHERE PARENTS("SI_NAME='WEBI-UNIVERSE'","SI_NAME=’Test’")
Thanks in advance,
Harsha
Hi Mani,
Is it possible to run a query by recipient for the reports housed in Crystal? I received a request to remove three associates from the distribution list, however was not advised for which reports.
If this is possible, please advice how it should be requested and i will do so.
Hi Mani
Where can I find the shared project and its elements in the CMC (business layer, data foundation, etc?
Thanks
Teresa
Mani,
Your query builder queries are very helpful. I have very unique requirement. We are on BOXi 3.1 SP7 we have crystal reports based on universe with prompts also coming from universe but same report has 2 or 3 prompts coming form Business View (due to cascading reason we used them from BV). I need to list these reports.
Your earliest help is appreciated.
Thanks
We are in a need of having the list of all users who are all having access to each folder under public folders in CMC.
for suppose, under public folders if there were two folders A and B.
we need a list of all the users along with their rights having access to A folder and similar having access to B folder.
Your help is appreciable.
Mani,
I'd need to run a query to select distinct SI_KIND types in all 3 tables.
The below executes but brings back 1000 rows of the same type, I need distinct.
We are sing BOXI3.1 SP7 FP1 and I created a excel vba that executes many queries but I can not find anything on the internet for distinct values for query builder.
Set Mis_Kind = IStore.Query( _
"SELECT distinct(SI_KIND) " & _
"FROM CI_INFOOBJECTS, CI_APPOBJECTS, CI_SYSTEMOBJECTS " & _
"WHERE SI_KIND>'' " & _
"ORDER BY SI_KIND")
Thanks
- Bob
Hi Mani
You have this code but it only reports old style UNv universes. What is the relationship type/object for UNX universes? Their type is DSL.UNIVERSE but using that does not work.
To list the Universes associated with a particular Data connection
Select SI_ID,SI_NAME,SI_KIND From CI_APPOBJECTS
where CHILDREN("SI_NAME='DataConnection-Universe'","SI_NAME='efashion-webi'")
Check his other blog for UNX universes.
BusinessObjects Query builder – What's New in BI 4.x
thanks, i found this in a link off that one
BusinessObjects Enterprise SDK - Relationship Queries
but neither gives the connection to universe link just the document to UNX link
BusinessObjects Enterprise SDK - Relationship Queries
'DOCUMENT-DSL.UNIVERSE'
I have just discovered you can use wild cards in the CHILDREN command
this works for my connection called JIRA
Select si_cuid , SI_ID, SI_NAME , si_kind
From CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
Where Children("SI_NAME like '%Universe%'","SI_NAME like '%jira%'")
Hi,
I am trying to find Universe Name (.unx format) and its Connection using Query Builder query; however no luck. 🙁
Your help on this will be highly appreciated.
Regards,
Vinesh
have you checked here BusinessObjects Query builder – What's New in BI 4.x
Suggest you to check all the related blogs and their comments section as well
Thanks Mani for your reply!
Below query is returning results for connection used in .unv universe
Select SI_ID,SI_NAME,SI_KIND From CI_APPOBJECTS where CHILDREN("SI_NAME='DataConnection-Universe'","SI_NAME='unv_conn_name'")
However, to get .unx universe details, below query is returning zero records..
Select SI_ID,SI_NAME,SI_KIND From CI_APPOBJECTS where children("SI_NAME like '%Connection%'", "SI_NAME='unx_conn_name'")
In children() function, provided connection extension ( .cns, .cnx ) for 2nd parameter but got no result. I am not sure on correct value to use for 1st parameter.
Regards,
Vinesh
Hi Team,
I need an query like group wise users, Can you help me please.
Groupname User ID
Administrator a1
a2
b1
XXgroup a1
b1
Like that.
I have an Excel macro, that is working only in 3x not in 4x, and also tell me any idea how to make work in 4x.
Thanks in advance
hi,
Is there any way we can find all the reports(crystal/webi) based on BW data via universe/Bex queries.
Any help on this would be highly appreciated
Thanks
Very nice article. Can you let me know if Query Builder support embedded query. For example, for this query, I'm not sure if there is syntax error or it's just not supported: Thanks!
Select si_name from ci_infoobjects where ci_parentid in
(select SI_ID from ci_infoobjects where SI_PARENT_FOLDER='6818' and SI_SPECIFIC_PROGID ='CrystalEnterprise.Folder')
sub queries are not supported, use only SDK
Hi Jawahar,
Thanks for the quick reply. Please allow me to consult couple of more questions since finally we find an expert 🙂 :
Is there a way to query all the SI_Path to //output folder? I mean, seems most return is about .wid files location on //input folder. But sometimes user mistakenly deletes a report from web-i client, which deletes the instance of report on //output folder, right? how can we recover it? If I can collect all reports' location on //output folder, later by recovering files from backup tape, I can easy copy it back to server, right? (though I know I can also open .wid file and then save it back to server, but wonder how to do it more efficiently in case there is massive file corruption issue across different folders).
Thanks again for your help.
Jian
It is not straight forward. You need to recover FRS and CMS DB, on different server (Sandbox server) and move the corrupted or deleted objects.
You can SDK to retrieve the actual folder path of the objects, but not by Query Builder. QB will give only FRS path.
Thanks, Jawahar. Since you've mentioned SDK a couple of times, I wonder if you can point me to a good link of how to set up SDK? I'm still pretty junior in this area 😳 . Meanwhile, can you also introduce some document that is related to file recovery topic?
Thanks very much!
Related to recovery - Please see below link.
Replicating the SAP BI 4.x Environment
Post and search your question on SDK forum.
Java SDK Application Development
Again, appreciate for your prompt reply and help!
Hello Manikandan,
Thank you very much for the insightful posts on QB. I'm trying to retrieve folder locations of the report and the email destination of the scheduled job. Can you provide a SQL statement for those items?
Hi,
I am using BO 4.1
Some one from the admin users group has changed the CMS port number. Can some one help me how to track the name of the user who has changed the port number.
Thanks,
Raj
I heard that a universe was going to be released to duplicate work of query builder so that reports could be run in Business Objects itself
is there any news on when that may come out?
regards
Andrew
I too eager to see a metadata universe Andrew Dale.
If I recollect correctly, Henry has given a hint that this metadata universe is in future plan and is going to available in near future.
+ Henry Banks Matthew Shaw
Here is what I could see in BI 4.2
Thanks
Mani
Been tryin to retrieve all the LOV's that belongs to each crystal report..Plzz help me out 🙁
It is a wonderful series of posts on various queries. Thank you so much.
I am trying to find something about promotion jobs, but did not find it.
May I know how I could query a list of promotion jobs?
Is promotion job considered an INFOOBJECT or SYSTEMOBJECT?
What is the SI_KIND representing promotion jobs?
Thanks a lot!!!
Mengyi
Try this
SELECT SI_KIND FROM CI_INFOOBJECTS WHERE SI_KIND ='LCMJOB'
Thanks
Mani
Fabulous!!! Thank you so much!
Hello,
I managed to get a list of promotion jobs with their status, but is there a way to go deeper into the details of each job, for example, what object(report/universe etc.) the job is promoting, job dependency...
I did not find these attributes in the property list of a job, but only some SI_FILES in the format of properties files, are those where such details are stored?
If so, how could I get details out of the properties files?
Thank you very much!
Hi All,
-----------------------
Script :
SELECT TOP 1000000 SI_NAME from ci_systemobjects where children("si_name = 'usergroup-user'", "si_name = 'xxxx Viewers'")
-----------------------------------------------------------
We are getting SI_Name and User ID data only with the mentioned script.
and we are not getting required data(Roles and status) for users based on the roles.
I need data with SI Name |User Id| Role | Status.
Do we get this kind of data with the mentioned script ?
Kindly check the attached image FYI.
Need data as shown in 1st table.
Hi ,
I need some help , I am trying to get users and groups assigned to them. Can you please let me know how can we achieve this. Below query in your blog gives user groups assigned to one particular user. there are 535 users and I want ti retrieve user groups assigned to all the users.
Select SI_ID, SI_NAME , SI_USERGROUPNAME From CI_SYSTEMOBJECTS Where PARENTS("SI_NAME='UserGroup-User'","SI_NAME='bbodapati'")
Please let me know if you have any suggestions.
Thanks,
BB
Hi All,
I need a help on extracting reports metadata information automatically.
I need to know which objects are used in which reports. The objects mean dimension/measures directly from the universe as well as calculated ones.
Any pointers would be highly appreciated.
Many thanks,
~Sudi
Hi, hope things are going alright.
Is there a way to extract WEBI report data provider path information using Query bulider? we have various WEBI reports which are running against excel file stored in Network shared drive. We would like to extract all WEBI reports using excel and where the excel is placed.
like this
Report , Report Location, Report data Provider type (excel, csv, etc..), Dataprovder path ( Network shared drive )
Thanks in advance.
Hi All,
we are using Window AD authentical, we have group hierarchy
Example :
Parent : User Group 1
Child : User Group 2
Child : User Group 3
I am trying to get the group hierarchy list from the parent node.
Please let me know, how to get the list from Query.
Regards,
Lateesh
CMS queries won't produce a hierarchical result, but you can get all descendants of a particular object. To get all descendant users and groups of User Group 1, use:
Although the name of the relationship is "usergroup-user", it will actually return descendant groups as well as users.
hi
does anyone know how one could request against repository data security profile informations
i encouter errors while trying to modify those DSP and i want to check in the repository
already find the field SI_SL_DSPSbut it only gives the object_id Nothing more
thank you
Did you checked here?
https://blogs.sap.com/2013/07/26/businessobjects-query-builder-whats-new-in-bi-40/ You may need to correlate the Security profile against usergroup/user SI_ID.
Thanks
Mani
I'll check it
Thank you Mani
Hi Mani,
Is there any way to get webi report names which are using custom SQL?
I am using BO 4.1 version
TIA.
Thanks,
Keshav
Hi Mani,
I would like to get the scheduled reports list depend on a particular event. How can we retrieve this info?
Thanks
Sandeep Chandran
hi, I hope I have repeated a previous question, great threads
I need a way using CMS querying to identify given a measure (or cuid) and universe to find where are the locations and title of all reports that use that measure
any clues?
Nicholas Wright
Hi, I want to query users of all groups. How can I do this?
I want result in below format.
User_id Group_name
Thanks and Regards,
Diwakar G
Hi Manikandan,
All queries are super helpful. Thank you very much for compiling such a useful list of queries. Is there a way to identify the Crystal Report with the command as the source?
Thanks,
Shawn
I'm getting error when posting:
Select SI_ID, SI_NAME From CI_SYSTEMOBJECTS Where PARENTS(“SI_NAME=’UserGroup-User'”,”SI_NAME=’Administrator'”)
error: There was an error retrieving data from the server: Not a valid query. (FWB 00025)
on BIP 4.2 SP7
any ideas
It works if you retype all the arguments after PARENT, when you copy/paste - I think double quotes get corrupted or something.
Select * From ci_infoobjects,ci_appobjects,ci_systemobjects Where PARENTS ("SI_NAME='UserGroup-User'","SI_NAME='Administrator'")
This one worked for me. Not sure if copy/paste borked it as well.