InfoStore Query Builder (with export to Excel)

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.


The tool has been desupported. Check Biclever CMS Query Builder as alternative.

See also

InfoStore Query Builder to Check Relations

84 thoughts on “InfoStore Query Builder (with export to Excel)

  1. jayantsb

    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?


    1. dmytro Post author

      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.


  2. amir


    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,


  3. terrellwsmith0747

    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


    1. dmytro Post author

      > 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


  4. Anoop

    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=, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The system cannot find the file specified.
    File name: ‘WindowsBase, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35′” error in one of the platforms which uses .NET framework 1.1


  5. SAAM

    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



    1. Digvijoyee

      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.


    1. dmytro Post author

      No. The source code is available, you can try to compile for R2. Let me know if you need help with this.


  6. Arseny

    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?


    1. dmytro Post author

      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


    1. wasy

      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.



      1. dmytro Post author

        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:


      2. wasy

        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.


  7. wasy


    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.



  8. wasy

    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,


  9. PGour

    Hello Dmytro, thank very much for this valuable tool, it’s a pleasure to manipulate Infostore data in Excel, thanks to you !


  10. wasy

    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.


    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.



  11. Lorena

    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,



    1. dmytro Post author

      Hi Lorena
      I checked the algorithm – looks correct..

      Would it be possible for you to send me (dmytro.bukhantsov at the result of the following query:

      from CI_INFOOBJECTS where SI_KIND like '%Folder'

      (SI_NAME is optional)
      If you know the name or cuid of the missing folder – that would be helpful.

      Thank you


    1. dmytro Post author

      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.


      1. Jenny

        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.


      2. tilak

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


      3. Maggie

        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.


      4. FX

        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.


    1. dmytro Post author

      This information is not stored in CMS.

      You can setup BO auditing, this will give you precise information who is running what.


  12. Jay

    Please let me know the code for getting folders, Objects, Objects properties like definition, sql etsc.. of a universe



  13. Ankur


    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!!



  14. Lee


    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: (XI 3.1 SP3)?

    Many Thanks for you.



  15. Kim

    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.


    1. dmytro Post author

      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.


      1. Jachin

        Check this out:

        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


  16. Darren

    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.



    1. dmytro Post author

      Do you see Excel file on disk? (Creation of the file should not require interaction with Excel application)


  17. Masood


    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


      1. Masood

        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.



  18. DBourlet

    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 !


  19. Berend

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


  20. Faisal

    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.


  21. Haytham

    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


      1. Himanshu

        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!!


    1. patrick delage

      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 ?



  22. BrigParadise

    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


  23. Eric

    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.


    1. dmytro Post author

      Hi Eric. Try something like:

      select * from CI_INFOOBJECTS 


  24. Alberto Rico

    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?


  25. Antony Pallupetta


    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,


  26. Venkkav

    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.


  27. Kumar

    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.



  28. Erika

    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.


  29. Kenneth

    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?


    Best Regards,


  30. Michelle

    I am new to this product and learning as I go. This tool could help me significantly.

    Is there a compiled version for

    Thank you!!


  31. Shweta

    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?


  32. Dan Coffman

    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


    1. Dan Coffman

      I do get results but just not the scheduling and processing section data such as SI_LOGON_INFO and SI_DESTINATIONS info


  33. Butchi

    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.


  34. Hic Schaff

    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.


  35. Robert

    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.”


    1. Robert

      I loaded it onto my computer, as opposed to just running it from this site, and the error went away. This application works fine!


  36. Arpit


    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


Comments are closed.