The purpose of the tool is to simplify building CMS metadata queries and provide possibly to export the result to Excel.
BusinessObjects Query Builder
With BusinessObjects Query Builder:
- you cannot actually build a query, you have to write the query manually. The step-by-step wizard is too simplistic.
- you have to remember the table names and the object kinds.
- you usually have to write several queries to get the required result. For example, you have to find a folder ID, before querying the list of objects in the folder.
- The simplest task such as a creation of list of reports might be tedious because of the structure of the result. You have to copy-paste each individual name in your list.
How to use
You can build queries for three tables:
- CI_INFOOBJECTS – information about all kinds of documents (Webi, Crystall Reports, Txt, Doc etc.)
- CI_APPOBJECTS – information about universes.
- CI_SYSTEMOBJECTS – information about servers, connections, users.
The tool allows to restrict query to specific object or specify parent folder.
You can view results in the BO Query Builder by clicking “Open in Query Builder”, or export the result to Excel file.
Export parameters
Containers – export containers on a separate worksheet. Container is a hierarchical property. E.g. property SI_FILES contains properties SI_NUM_FILES, SI_FILE1, SI_FILE2 etc.
Transposed – sometimes it is convenient to see the result in the transposed form, for example when you select one object.
Installation
The tool has been desupported. Check Biclever CMS Query Builder as alternative.
hi, this looks interesting but it did not work on my laptop with windows XP – SP2. is there any specific requirement to make this tool working?
LikeLike
The tool is complied for BO XI 3.1 SP3 FP3.2. It might not work for other versions. Do you wanna try to compile it for your system? I will send you source code.
LikeLike
Hi,
I luv this tool, it is running fine on BO 3.1, no issues… period.
Can you send the code or the complete solution with source code please ?
Thank you,
amir
LikeLike
Hi Amir, here: bukhantsov.org/tools/QueryBuilder_src.zip
LikeLike
I am also interested in this tool. It appears to be exactly what i am looking for1. I need more information on how to use this tool. Does it run standalone, or is added to an existing tool? How does it interface with the server?
thank so much!
Terrell Smith
LikeLike
> Does it run standalone?
Yes. Also it does not have installer, you just unpack the tool in a preferred location and create a shortcut.
> How does it interface with the server?
It connects to the server using BO API to query infostore objects.
If you have some suggestions about functionality – let me know: dmytro.bukhantsov at gmail.com
thx:)
LikeLike
Excellent, very helpful tool
LikeLike
Excellent tool mate !! thanks for sharing. Just wondering if this has got a dependency on .Net framework. I am getting “System.IO.FileNotFoundException: Could not load file or assembly ‘WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The system cannot find the file specified.
File name: ‘WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35′” error in one of the platforms which uses .NET framework 1.1
LikeLike
Yes, the tool requires .NET 3.5.
Let me know if this resolves the issue.
LikeLike
Great Tool. One issue is I am not able to see folders under root folder in our environment, not sure if there are any CMS setting I have to change for this tool to work. Works fine on test server, able to see all the folders/subfolders under root folder
Thanks
Sachin
LikeLike
Its because you don’t have rights to view the folders in the environment you are looikng in (viz. Prod environment). You have access in test hence you have no issues.
LikeLike
Do you have for version BOXI2 ?
LikeLike
No. The source code is available, you can try to compile for R2. Let me know if you need help with this.
LikeLike
Hi dmytro,
Great tool! I tried to use it on a BO BI 4.0 SP02 system, however, the output Excel file contains maximum 1000 rows although there are more files in the system.
Is there some kind of a limit on the maximum number of returned rows in the tool?
LikeLike
Thank you, Arseny
The default limit for a CMS query is 1000. You can change the limit using option TOP:
e.g. SELECT TOP 10000 * FROM ci_systemobjects
LikeLike
I would like to have the complie code for bi 4.0..
your help will be highly appreciated..
LikeLike
when i try i am getting the following error:
Could not load file or assembly ‘CrystalDecisions.Enterprise.Framework,
Version-12.0.1100.0, Culture-neutral,
PublicKeyToken-692fbea5521e1304’ or one of its dependencies. The
system cannot find the file specified.
appreciate.
wasy
LikeLike
Remove all references to the libraries and reattach them. They are in the folder:
[SAP BusinessObjects]\SAP BusinessObjects Enterprise XI 4.0\win64_x64\dotnet\iPoint
The libraries:
CrystalDecisions.Enterprise.Framework.dll
CrystalDecisions.Enterprise.InfoStore.dll
LikeLike
i am extremely sorry for the trouble, but, could you please tell me how to “Remove all references to the libraries and reattach them”, i tried but, probably not the way it is suppose to be.
i was able to find the files in the following location: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\dotnet\iPoint
really appreciate your help.
LikeLike
HI,
Really apreciate if you could help me figure it out how to troubleshoot and run the superbly awesome tool in my environment.
Local Machine: Win 7.
BI4.0: Win server 2008.
Gettinr error:
Could not load file or assembly ‘CrystalDecisions.Enterprise.Framework,
Version-12.0.1100.0, Culture-neutral,
PublicKeyToken-692fbea5521e1304′ or one of its dependencies. The
system cannot find the file specified.
As suggested earlier, it wold be great to walk me through troubleshooting the behavior.
Reallt really appreciate your response on this.
Thanks,
Wasy.
LikeLike
Hi Wasy
I have uploaded 4.0 version:
https://bukhantsov.org/tools/QueryBuilder4.zip
LikeLike
HI Dmytro,
Tool works like a charm :), i am still in process of fixing the dll’s, currently exporting it to excel works just fine, i still need to learn a little bit more about queriying also, need to go through user manual (if any, for this tool), other than that, i am glad to have such a nice tool handy.
Thank you so much and really really appreciate your efforts.
I guess, i will be asking couple more questions about the operational side of this tool and some queries as well in the near future.
Once again Thank you and Best regards,
Wasy.
LikeLike
Hello Dmytro, thank very much for this valuable tool, it’s a pleasure to manipulate Infostore data in Excel, thanks to you !
LikeLike
HI Dmytro,
Once again thank you so much for yoru efforts in providing such handy tools.
Need your help:
Is is possible to query and find a particular universe object used in any reports.
or
To query and extract all the objects used in a report.
It would be great to have their paths as well (folder location).
Your help in this matter will be highly appreciated.
Wasy
LikeLike
Hi Dmytro,
This is a great tool ! I am running it on BI 4.0 SP4 FP20 and it works fine, but
I noticed some of the folders under Root are not appearing. I am logging in with an administrator account so it is not a question of grants…
Any idea what it might be ?
Thank you,
Lorena
LikeLike
Hi Lorena
I checked the algorithm – looks correct..
Would it be possible for you to send me (dmytro.bukhantsov at gmail.com) the result of the following query:
(SI_NAME is optional)
If you know the name or cuid of the missing folder – that would be helpful.
Thank you
LikeLike
I am getting error when exporting to excel : Could not save file, file is used by another process.
Please help
LikeLike
The file should not be open in Excel, otherwise the program will not be able to write into it. Please close Excel and try again.
LikeLike
It is great tool, love it. However I run into the same error:Could not save file, file is used by another process. I don’t have Excel open, and double checked task mgr too. Not sure if this helps: I am using Excel 2010, Window 7 and BO XI3.1.
LikeLike
Hi Dmytro,
The tool worked very well on the first day. But now when i tried to extract the information into Excel, even though there is not Excel file opened it gives an error message “File is used by another process”.
What do you think could be a possible reason. I even restarted my PC and tried fresh but no luck !! Please help..
LikeLike
Encounter the same issue “File is used by another process” when Excel is not running in task manager.I am using Excel 2010, Window 7 and BO XI3.1.
Please suggest.
LikeLike
Is there a solution to this?
I have encountered the same issue.
“File is used by another process” when Excel is not running in task manager.I am using Excel 2010, BO XI3.1. SP3 on a Windows 2003 server.
In which location would the excel be saved?
Please advice.
LikeLike
Thanks a lot. its a good tool which saved me a lot of time. Good work
LikeLike
Is there a way to show all the reports that one user is running?
LikeLike
This information is not stored in CMS.
You can setup BO auditing, this will give you precise information who is running what.
LikeLike
Please let me know the code for getting folders, Objects, Objects properties like definition, sql etsc.. of a universe
Thanks
LikeLike
You can start from this post: https://bukhantsov.org/2011/08/getting-started-with-designer-sdk/
Note that this information is stored in universe files in BO file repository, not in CMS. So you cannot get this information using CMS queries.
LikeLike
Hi,
Thanks so much for the tool, I have been using it since quite a while and find it very useful. The only limitation i found out was that it doesnt capture the detailed or the Sub-Items of a query output… FOr example I’m creating list of failed reports with error message and Name… The code captures the SI_ID, SI_NAME however it fails to capture the error message that is the “SI_STATUSINFO.SI_SUBST_STRINGS.2”
I’m not a coder so i cannot modify your source code. If you could include the same in this query builder, it would be of great great help!!
Cheers!
Ankur
LikeLike
Hi,
Thanks so much for the tool it is very useful ( this BO ver: XI 3.1+SP3+FP3.4). Can i have a link for download the verison is works fine with Product Version: 12.3.0.601 (XI 3.1 SP3)?
Many Thanks for you.
Lee
LikeLike
The default connection is using port 8080 we use 9100. Is there a place where I can change this? Thanks a bunch for this product It appears to be super cool.
LikeLike
The tool should be recompiled to change the port…
LikeLike
Is there a way to query and return what tables/fields a report is using??
LikeLike
This is a quite tricky. I do not know which tool can do that.
It is easy to extract SQL queries from a report using BO SDK. But then it needs to be parsed to extract names of the tables and fields and this is not easy since a full SQL syntax analysis is required.
LikeLike
Check this out:
http://www.reportminer.com/products/rptminersuite.shtml
Extract generates configurable professional-quality report definition documentation (tables and columns used) for Crystal Report files. Extract analyzes each selected Crystal Report file and can generate documentation on the report definition in the following formats:
Microsoft Word Format
Microsoft Access DB Format
Text Format
LikeLike
Hi Dymytri
I’ve been using this great tool for some time but i’ve just seen the error
An Error occured in sending the commanf to the application.
Query is fine in Query builder, suspect its a security restrciyion in Excel preventing it being called from your app. Not sure where that is in Excel.
Anay help greatly appreciated.
Thanks
LikeLike
Do you see Excel file on disk? (Creation of the file should not require interaction with Excel application)
LikeLike
Super ! Good job!
LikeLike
One of the best tools I see so far in Business Object community
LikeLike
Hi,
When I am trying to connect, its throwing an error after etering the credentials.” Unable to logon. Could not connect to the server”.
Please help
LikeLike
What is the version of BO?
What authentication are you using?
LikeLike
Hi Dmytro..
For 3.1 SP5 patch 9
When I am trying to login to the query builder tool from the server itself. After entering the query and clicking on export to excel its giving a message that ” no application is associated with the specified file for this operation but when I am trying to open it from a diferent server its allowing to login and get the result of the previous server where its giving error.
For 4.0 SP4 Patch 6
on opening the exe its giving me error ” could not load the file or asembly crystaldecisions.enterprise.framework, Version = 14.0.2000.2, Culture = nuetral…
I have also installed clients for both the version on a single machine…
when I am trying to login by opening the exe, for both the versions its giving the error “Unable to logon. Could not connect to the server. its with enterprise id.
Please advise.
Regards
Masood
LikeLike
Hello !
Thanks for this SO USEFUL tool ;o)
but, I have an issue, using with BO XI R2 SP5… while trying to login I have the message box “could not login” and nothing more… My credential are OK and tested on the CMC webpage… thanks for your help !
Regards.
LikeLike
Hi !
additional info : I use LDAP identification… could it be a problem ?
Thanks a lot
Regards.
LikeLike
Am getting the same error (could not load the file or asembly crystaldecisions.enterprise.framework, Version = 14.0.2000.2 [etc]).
I have BI4 client tools running and the EnterpriseFramework.dll has version 14.0.5.882. But this can change as soon as we roll out a new servicepack. Can this not be made level unaware?
Also tried on a server with platform files (BI4 SP05) and on a BI4.1 server. Same result.
Shame I can not get this running. looks cool.
LikeLike
Thanks a lot for this great article, I wondering if you could help me.
I am looking for a query to get information of most accessed reports with report folder excluding shortcuts and reports instances.
Also a query to get information on who uses a particular report.
Thanks in advance for your effort.
LikeLike
Thanks a lot for this application , but when I try to extract to Excel file I got an error Windows message “Could not save Excel file” / “File is used by another process” is there any solution
LikeLike
Could you try to close all excel windows and maybe EXCEL processes?
LikeLike
I am also getting the same error “File is in used by another process”. And I have checked no excel sheet or excel process running on my machine. Please help. Thanks!!
LikeLike
I also get this strange message. I try this without success
1) Restart server
2) Install the tools to completly new PC
It like something in the CMS or BO server ?
Strange
LikeLike
Could not install querybuilder:
I am getting the error:
Could not load file or assembly ‘CrystalDecisions.Enterprise.Framework,Version=14.0.2000.0, Culture=Neutral, PublicKeyToken=692fbea5521e1304 or one of its dependencies. The system cannot find the file specified
Do I need the BI Client tools on the desktop that I want to install QueryBuilder
LikeLike
Yes. Because the tool requires SDK libraries that installed with the client tools.
LikeLike
Hi, I want to use the tool for BO XI R2. where to find this?
Praveen
LikeLike
I am attempting to isolate Crystal Report objects where the SI_LOGON_MODE 1. All my CR Objects imported had the setting where SI_LOGON_MODE = 1. On import some have been “flipped” to SI_LOGON_MODE = 0. I would like to quicly identify any others. When I run SELECT SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND = ‘CrystalReport’ I am able to find the SI_LOGON_MODE under Processing Info>SI_LOGON_INFO>SI_LOGON1. But, when I do any query with SI_LOGON_MODE in the WHERE clause I get zero (0) results. Even when I search SI_LOGON_MODE=1, where I know I should have hundreds of matches. I’m guessing my query structure is not correct becuase the SI_LOGON_MODE is sever “levels” deep in the Processing Info. Any suggestions? Thank you.
LikeLike
Hi Eric. Try something like:
LikeLike
Just what I needed. Much appreciation!
LikeLike
Ihave 12.1.0 version CMC. Windows server 2003 Enterprise X64 edition, SPack 2. I got the error error (could not load the file or asembly crystaldecisions.enterprise.framework, Version = 14.0.2000.2 ….
What Can I do to solve the problem?
LikeLike
Hi,
Firstly, great piece of code!! Congrats! 🙂
I have been using Query Builder only for a very short while. There are a few doubts I wanted to ask and you seem to be an expert on the subject.
1 • Where do I get what different column names in these tables mean, except for the obvious ones of course? (Sort of a tutorial)
2 • Is there absolutely no way to get which DB objects are used in a Crystal Report (I know this question has already been answered before, but just want to confirm)
3 • And finally, I work mostly with Business Views. How can I figure out which Business Element is mapped to which DB Object? I am only able to find which Data Foundation / Business View it is connected to.
I realize that this is a lot to ask, but I have been trying to figure this out myself but without any luck! Any help is greatly appreciated.
Thanks In Advance,
Antony
LikeLike
Really an Excellent Tool! Is it possible to get the folder and all the sub folder under it. Actually I am trying to get the list of report under a folder and it subfolder but this works only at one level. Is there anything I should change.
LikeLike
Great Job once again Dmytro Kudos!!!!!
Regards
LikeLike
Hi Dmytro,
Great Work.
Is it possible to automate the execution of Infostore query builder with CMS details user name,Pwd with query.
Say for example Schedule the Query result in excel format to some email account weekly once.
UK
LikeLike
You can use command line version of the tool to generate the Excel file
https://bukhantsov.org/2012/09/command-line-infostore-query-builder-with-export-to-excel/
And you can use some other software to send the mail e.g.
http://www.howtogeek.com/125045/how-to-easily-send-emails-from-the-windows-task-scheduler/
LikeLike
Thanks Dmytro for your response.
I will check it.
Thanks,
Kumar
LikeLike
A very good handy tool to query metadata results. Thank you for your solution for so many users like me. I am really curious to know if there is a way to query object SI_DESTINATIONS because it doesn’t return values when i mention this in select objects. I guess sub items of a query are not returned. Even then appreciate your solution and sharing with all of us.
LikeLike
Hi, I am trying to run the following query, however, the query only returns SI_DESCRIPTION, SI_NAME, SI_ENT_USERFULLNAME, and SI_LASTLOGONTIME. Several fields are missing in the results. Any suggestions?
SELECT SI_NAME, SI_ENT_USERFULLNAME, SI_DESCRIPTION, SI_EMAILADDRESS, SI_DISABLED, SI_LASTLOGONTIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’
Best Regards,
Ken
LikeLike
I am new to this product and learning as I go. This tool could help me significantly.
Is there a compiled version for 12.3.0.601?
Thank you!!
LikeLike
The one here should work. Otherwise you can try the command line one https://bukhantsov.org/2012/09/command-line-infostore-query-builder-with-export-to-excel/ You can point it to the libraries of your BO.
LikeLike
My cmc port is 8081,by default it take 8080 port and hence it is not working for me. Can you please suggest where to update the port number so as to get the desired information from cmc?
LikeLike
In using the product for 3.1, in using the query SELECT *
FROM ci_infoobjects, ci_appobjects, ci_systemobjects where si_recurring=1 and si_runnable_object=1 but the results for scheduling and processing do not show up. I am specifically looking for data from SI_SCHEDULEINFO.SI_DESTINATIONS area and SI_LOGON_INFO area
LikeLike
I do get results but just not the scheduling and processing section data such as SI_LOGON_INFO and SI_DESTINATIONS info
LikeLike
Hello Dmytro,
Thank you very much for this great tool!
I was using it some time, but from today I started to get following error message:
“Unable to connect to CMC server:6400. A wrong connection is made to @@server:6400(server:6400). Logon cannot continue. ”
version of BO is 4.1 SP2.
Thank you for response.
Butchi
LikeLike
Just found this and unzipped onto my machine. Thank you! So far so good on our BI4.0 SP6 installation. Thanks so much for this. So far I’ve ‘only’ used it to export a list of users with name, email and last logon time, to Excel. I say ‘only’ because… well you know the pain of doing this through Query Builder. I’ll be exploring further.
May you have 10,000 happy days for sharing.
LikeLike
I have tried to use it to export to Excel. It gives me a message that it could not save file – “File is used by another process.”
LikeLike
I loaded it onto my computer, as opposed to just running it from this site, and the error went away. This application works fine!
LikeLike
Hi,
On daily basis I monitor Web intelligence reports whose status is ‘success’ in CMC Instance manager and then I have to calculate the time taken by each report from a column giving information about report ‘refresh time’ and then I have to apply some maths in it manually(using calculator). As the number of reports are increasing day by day this task is becoming hectic for me, so i was thinking if I can view this data from Query builder but the data comes from any folder in Business Objects. And is there any way for exporting this data?
Any suggestion will be appreciated.
Thanks & Regards
Arpit
LikeLike