There is a nice tool in CMC that allows to check relations between objects. Something similar can be done in the query builder with some non documented features.
For instance, to find all reports that use a universe Finance, you can write:
SELECT * FROM ci_infoobjects, ci_appobjects, ci_systemobjects WHERE PARENTS("si_name='Webi-Universe'","si_name='Finance'")
PARENTS has two arguments. First is the relation type, second is the reference object.
If you want to find opposite – all universes used by a report, you can write
SELECT * FROM ci_infoobjects, ci_appobjects, ci_systemobjects WHERE CHILDREN("si_name='Webi-Universe'","si_name='Balance Sheet'")
What are the possible types of relations (first argument)? You can find the list querying CMS:
SELECT si_name FROM ci_systemobjects WHERE si_relation_table_name='RELATIONS'
Now you can also build such queries and export result to to Excel in InfoStore Query Builder:
Webi is developed by using the BICS connectivity and not with universe. In this scenario how to get the list of webi reports which are used by the particualr BICS connectivity(OLAP connection).
Well I do not have a system with BICS connection and corresponding reports so it is difficult for me to answer. You can try to display all properties of a Webi document and check if there is any property linking to connection.
SELECT * FROM ci_infoobjects WHERE si_kind=’Webi’ and si_id=
I am unable to execute and use this tool, it throw me Could not load file CrystalDecisions.Enterprise.Framework.
I am using BO XI R3.1 SP6
thanks in advance..
And You website is really awesome for BO solutions.
Do you have BO client tools on your PC?
I do have BO client TOOLS in my PC and i am using it
I forgot to say to thanks for your reply
Maybe this is because of version incompatibility. The one here was build for 3.1 SP3 FP3.2.
Well, I will try to look into this this week but I cannot promise anything.
Could you help me to create query to find all folders for UserGroup =’Administrators’?
FROM ci_infoobjects, ci_appobjects, ci_systemobjects
It took me awhile to find the solution but there is a two step one. First find the SI_ID of the group you wish to query…
SELECT SI_NAME, SI_ID FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USERGROUP’ AND SI_NAME=’User Group Name’
Let’s say the SI_ID for User Group Name is 15976
Run a query with 15976 in the IsAllowed parameters…
Select SI_ID, SI_NAME, SI_CUID, SI_KIND From CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS WHere SI_KIND=’Folder’ And IsAllowed(15976, 3)
IsAllowed(15976,3) – What does the 3 mean here? Is there any reason why u used 3?
Did you had a chance to build for BO XI R3.1 SP6?
I need to find all the reports under a folder hierarchy.
All the information is stored in CI_INFOOBJECTS
Any ideas if I can user children relationship on info objetcs.
Thanks in advance
you need to know id of the Folder then you can use SI_ANCESTOR
gives all Webi reports in the folder with id=4 and its subfolders
the si_ancestor condition I tried before but the query never comes back. I get a comment from CMS that CMS timedout after 9 mins.
Ok I got the query to work. si_ancestor by itself is very poor performer. If an indexed column is used along with it, like in my case i needed reports modified after 1st July 2013 then I used SI_UPDATE_TS >= ‘2013.07.01 00:00:00’ and query comes back in seconds.
Trick is to add an indexed column along with si_ancestor. List of indexed columns is as follows:
Link to get this list is http://devlibrary.businessobjects.com/BusinessObjectsXIR2SP2/en/en/BOE_SDK/boesdk_dotNet_doc/doc/boesdk_net_doc/html/SDKFundamentals143.html#1143954
Thank you for followup.
Strange.. Do you have that many old reports/instances? Did you add restrictions like SI_KIND=’Webi’ and SI_INSTANCE=0?
Yes I did have those conditions but query wont come back even if I went to the lowest level folder. We have around 12K documents in boe. May be I could have tried by increasing the timeout limit in config file but that would have needed a bounce.
Thanks a lot for all your help. This is a great site for sdk and I am planning to create some macros which will give this and related information and use them company wide.
I also got similar requirement, extract report list along with associated folder hierarchy. Can you post query which may help me.
How did you download this info to Excel as above list is required to me to compare against second new setup to ensure nothing is missed.
@Dmyto – It would be helpful if you can provide tool which supports BOXI 3.1 SP3
Appreciate your response.
with above provided tool, it gives “Transport error: communication failure (FWM 00001)”. I’m able login to the Business Objects- Query Builder.
Microsoft Excel 2003, XP, Client 3.1 SP3
Hi, I am very new to Business Objects and creating queries. I need help writing a query to see what crystal report ran on which server? We have two servers they are clustered.
My BO port is 8081. Can you please say where to update this information so as to get the data from CMC?
One simple question. Is it possible to retrieve SQL query(ies) content embedded into each data provider(s) from one given webi report by using query builder or not ?
If yes could you provide us a basic query builder query example for a given webi report please ?
If not, is it possible to retrieve it with the usage of a dedicated method from BusinessObjects Java SDK (i assume getDocumentDataProviders method inherited from interface com.businessobjects.sdk.plugin.desktop.webi.IWebiBase)
Could you confirm it ?
Many thanks for your feedback.