Skip to Content

There have been many requirements about getting the full folder location of all the reports(crystal/webi/deski) in the enterprise system. The information can be figured out using the query builder by executing multiple queries but it becomes time consuming as well as difficult when this information has to be retrieved for a large number of reports.

Using Query Builder

Using the query builder(AdminTools Application), and execute the below query
SELECT SI_ID,SI_NAME,SI_PARENTID FROM CI_INFOOBJECTS WHERE SI_KIND IN(‘cryatalReport’,’Webi’,’Deski’) AND SI_INSTANCE=0, you get to list the id, name and parentId of all the crystal/webi/deski reports. The SI_PARENTID property bag contains the id of the object which is parent for a particular report. In basic scenarios, the parent of a report can be a folder, objectpackage, FavoritesFolder or an Inbox.

Again executing the below query in the query builder you can get the name of the parent.
SELECT SI_ID,SI_NAME,SI_PATH FROM CI_INFOOBJECTS WHERE SI_ID=<Parent-Id retrieved from the previous query>

If the above query returns a folder(SI_KIND=Folder), then the SI_PATH property bag contains the hierarchy of folders. i.e it contains the name of the folders this object belongs to. The SI_PATH property bag has a sub property bag “SI_NUM_FOLDERS” which contains a numeric value and it lets you know the number of folders this partcular folder has as its parents in hierarchy.

Consider a report is inside an objectPackage, then the parent of the report becomes an object package who inturn has a parent as a folder.
Thus using the query builder, this becomes a two step query.
First, to get the parentid of the report, then to get the name of the object package and get the parent id of the object package and then again execute a query to get the folder name and path.

Using BusinessObjects Enterprise SDKs

The below code lists folder path of all the reports(crystal/webi) in the enterprise system.

Note:

For other scripts and information on how to run these scripts see here:

Scripts and Samples

Get Folder Path

<%@ page import=”com.crystaldecisions.sdk.properties.*” %>
<%@ page import=”com.crystaldecisions.sdk.framework.*” %>
<%@ page import=”com.crystaldecisions.sdk.occa.infostore.*” %>
<%@ page import=”com.crystaldecisions.sdk.plugin.desktop.folder.*” %>
<%@ page import=”java.util.*” %>

<html>
<head></head>
<body>
<table border=”2″ cellpadding=”0″ style=”border-collapse: collapse” bordercolor=”#111111″ width=”100%” id=”AutoNumber1″>
<tr>
<th width=”30%”>Report Name</th>
<th width=”10%”>Report Id</th>
<th width=”40%”>Folder Path </th>
</tr>
<%
IEnterpriseSession es=null;
try
{
String CMS = “localhost:6400”; //CMS Name 
String UserID = “Administrator”; //Administrator User Account 
String Password = “”;   //Administrator Password

ISessionMgr sm = CrystalEnterprise.getSessionMgr();
es = sm.logon(UserID, Password, CMS,”secEnterprise”);

IInfoStore iStore = (IInfoStore)es.getService(“”,”InfoStore”);

String sq = “select si_id,si_name,si_parentid from ci_infoobjects where si_kind in (‘CrystalReport’,’Webi’) and si_instance=0”;

IInfoObjects iObjects = iStore.query(sq);
IInfoObject iObject = null;

for(int i=0;i<iObjects.size();i++)
{
  iObject = (IInfoObject)iObjects.get(i);
  out.print(“<tr><td>” + iObject.getTitle() + “</td><td>” + iObject.getID() + “</td>”);
  IProperties prop = iObject.properties();
  IProperty getProp = prop.getProperty(“SI_PARENTID”);
  String FolderID = getProp.toString();
  IInfoObjects folder = iStore .query(“select si_id,si_name,si_parentid,si_path from ci_infoobjects where si_id=” + FolderID);
  IInfoObject ifolder=(IInfoObject)folder.get(0);   
  if(ifolder.getKind().equals(“Folder”))
  {
   IFolder iifolder=(IFolder)ifolder;
   String finalFolderPath=””;
   if(iifolder.getPath()!= null)
   {
    String path[]=iifolder.getPath();
    for(int fi=0;fi<path.length;fi++)
    {
     finalFolderPath = path[fi] + “/” + finalFolderPath;
    }
    finalFolderPath = finalFolderPath + iifolder.getTitle();
   }
   else
   {
    finalFolderPath=finalFolderPath+iifolder.getTitle();
   }
   out.println(“<td>” + finalFolderPath + “</td></tr>”);
  }
  else if((ifolder.getKind().equals(“FavoritesFolder”)))
  {
   out.println(“<td><b>FavoritesFolder</b>  ::  ” + ifolder.getTitle() + “</td></tr>”);
  }
  else if((ifolder.getKind().equals(“Inbox”)))
  {
   out.println(“<td><b>Inbox</b>  ::  ” + ifolder.getTitle() + “</td></tr>”);
  }
  else if((ifolder.getKind().equals(“ObjectPackage”)))
  {
   //out.println(“<td><b>ObjectPackage</b>  ::  ” + ifolder.getTitle() + “</td></tr>”);
   IProperties prop1 = ifolder.properties();
  IProperty getProp1 = prop1.getProperty(“SI_PARENTID”);
  String FolderID1 = getProp1.toString();
  IInfoObjects folder1 = iStore .query(“select * from ci_infoobjects where si_id=” + FolderID1);
  IInfoObject ifolder1=(IInfoObject)folder1.get(0);   
  if(ifolder1.getKind().equals(“Folder”))
  {
   IFolder iifolder1=(IFolder)ifolder1;
   String finalFolderPath1=””;
   if(iifolder1.getPath()!= null)
   {
    String path[]=iifolder1.getPath();
    for(int j=0;j<path.length;j++)
    {
     finalFolderPath1= path[j] + “/” + finalFolderPath1;
    }
    finalFolderPath1 = finalFolderPath1 + iifolder1.getTitle()+”/”+ifolder.getTitle();
   }
   else
   {
    finalFolderPath1=finalFolderPath1+iifolder1.getTitle()+”/”+ifolder.getTitle();
   }
   out.println(“<td>” + finalFolderPath1 + “</td></tr>”);
  }
  
  }
 
 
}

}
catch(Exception e)
{
out.println(e);
}
finally
{
if(es !=null)
{
es.logoff();
}
}
%>

To report this post you need to login first.

43 Comments

You must be Logged on to comment or reply to a post.

  1. Erika Atencio

    Hi. I’m trying to execute this query with QUeryBuilder, in order to obtain the parent’s name, but I produces a query error. What’s wrong with my query?

    SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS,

    SI_PROCESSINFO.SI_WEBI_PROMPTS, SI_PROCESSINFO.SI_FILES,

    SI_PROCESSINFO.SI_PROMPTS  , (SELECT x.SI_NAME FROM CI_INFOOBJECTS x WHERE x.SI_ID= CI_INFOOBJECTS.SI_PARENTID) as t FROM CI_INFOOBJECTS

    WHERE  SI_KIND = ‘CrystalReport’

    (0) 
    1. Prithviraj Shekhawat Post author

      Hi Sohel,

      Thanks for your feedback.

      As you are aware by default any query running on the infostore returns 1000 results(provided we have not modified registry entries), be it from query builder or through sdk is the reason for this.

      You would need to change the below query above

      String sq = “select si_id,si_name,si_parentid from ci_infoobjects where si_kind in (‘CrystalReport’,’Webi’) and si_instance=0”;

      Use sql top fuction in the query.

      For example you need to get data about 5000 reports so the query would become

      String sq = “select top 5000 si_id,si_name,si_parentid from ci_infoobjects where si_kind in (‘CrystalReport’,’Webi’) and si_instance=0”;

      Thanks,

      Prithvi

      (0) 
  2. S man

    Hi experts,

    Could someone let me know to use above script.

    1. Do I need to copy script any location on BOE server

    2. How to use it

    Thanks in advance

    (0) 
  3. S man

    Thanks for reply

    I would like to add one more column to pull BEX query name in same report.

    Can you help me out to edit script.

    Thanks in advance

    (0) 
    1. Dell Stinnett-Christy

      That’s not a simple task and how you do it depends on the SI_KIND of the report and whether the BEx query is accessed through a universe.

      I highly suggest using QueryBuilder to look at all of the properties of a couple of reports of each kind to determine which property contains the name of the query.  There is a possibility that the name is not in the information in the InfoObject!  If it is there, you can then update the code to get the information from that property.  If it isn’t, how you get it will depend on which version of BO you’re using (Webi in 3.x is different from Webi in 4.x) may or may not be possible.

      -Dell

      (0) 
      1. S man

        Hi Dell,

        Thanks for reply.

        We get query name under SI_WEBI_DOC_PROPERTIES .

        I am trying to pull report name, location and query all together.

        Now through above sdk we are getting report name and location and by using SELECT SI_NAME, SI_PARENT_FOLDER, SI_WEBI_DOC_PROPERTIES FROM CI_INFOOBJECTS WHERE SI_KIND = ‘Webi’  we are getting report name and query. Its getting double work to me to club both reports into one report .

        So only I am trying to get all in one execution.

        We are on 4.0 sp4

        (0) 
        1. Dell Stinnett-Christy

          I’m not sure what you mean by “Its getting double work to me to club both reports into one report .”

          If the information is in SI_WEBI_DOC_PROPERTIES, you should be able to get the value by going to the .properties of the IInfoObject.  I would just add the code that gets that info to the existing code from above to display the value.

          -Dell

          (0) 
  4. gene w

    We have a lot of reports that are configured to save output to a network folder.  The file server we have been using is being replaced, so I need to get a list reports and the locations that the reports are set to save report output to.  Prefer to use the SDK to find these reports and programmatically update the output location to the new folder.  Seems like this is close to providing what I need, but I have not been able to find the right property.  Appreciate any help.

    (0) 
      1. gene w

        Yes – scheduled reports that save output to a network location.  Typically these are reports that are used for audits or maybe even as input to another application.

        (0) 
    1. Dell Stinnett-Christy

      To get to the file destination, do the following:

      1.  Get an IInfoObjects based on this query:  Select SI_ID, SI_NAME, SI_SCHEDULE_STATUS, SI_SCHEDULEINFO from CI_INFOOBJECTS where SI_INSTANCE = 1 and SI_SCHEDULE_STATUS in (8,9) order by SI_ID.

      NOTE:  SI_SCHEDULE_STATE 8 = Paused and 9 = Pending/Recurring

      2. For each IInfoObject in IInfoObjects, get the ISchedulingInfo.  From the ISchedulingInfo, get the IDestinations.

      3.  For each IDestination in the IDestinations, call getName() to get the destination type.  I

      4.  If the destination type is not “CrystalEnterprise.DiskUnmanaged”, skip the destination.  Otherwise get the IProperties of the IDestination.

      5.  Get the “SI_DEST_SCHEDULEOPTIONS” set of properties from the Destination’s IProperties.  If the resulting IProperties is not null, get the “SI_OUTPUT_FILES” set of properties.

      6.  If the SI_OUTPUT_FILES properties is not null, use its “SI_TOTAL” property to get the number of files listed.  The files in this properties set will be listed under properties named “1” through the number in SI_TOTAL.

      7.  You can then walk through the set of files looking for the specific path (NOTE:  the back-slashes in the path to the file have been replaced by forward slashes when the file name was stored in the database).  If you find the path, you can updated it and set the property to the new path.

      8.  Be sure to call “IInfoObject.save()” after you update the file name(s) for each schedule.

      -Dell

      (0) 
      1. gene w

        This looks like it is exactly what I need.  I will give it a try and let you know how it works for me.  Thank you for your time in helping with this. 😎

        (0) 
  5. gene w

    Many thanks to Dell for his *huge* help on this. Using his notes, I was able to put this c# method together and it works like a charm.

    query to get scheduled report objects to work on:

        “SELECT TOP 100000 * FROM ci_infoobjects WHERE SI_RUNNABLE_OBJECT = 1 AND SI_RECURRING = 1”;

    // method to update output file folder server name

    private void UpdateDestinationFolder()

    {

      InfoObjects myInfoObjects = _InfoStore.Query(BOQUERY_ScheduledReport.ToString());

      // in dev/test environment need the ability to run multiple times to validate with Business Analysts

      // the bAllowSwitchback flag allows quickly switching between oldservername and newservername and back

      const bool bAllowSwitchback = true;  // when true, reports that have been set to newservername will get switched back to oldservername

      const bool bSavechanges = true; // when true, changes are saved, when false changes are discarded

      var n = 0; // keep track of how many reports have been processed for debugging

      int fn = 0; // keep track of how many reports have been changed

      // string array to easily see the changes being made

      string[,] filenames = new string[200, 3];  // array to help with visualizing changes being made while debugging

      foreach (InfoObject myInfoObject in myInfoObjects)

      {

          String myReportName = myInfoObject.Title; // only need this for debugging

          //Loop through all scheduled reports and correct the SI_Destination

          //for those reports that are pointing to a folder on the <oldservername>

          SchedulingInfo mySchedInfo = myInfoObject.SchedulingInfo;

          try

          {

          if (

                 mySchedInfo.Properties[“SI_DESTINATIONS”].Properties[“1”].Properties[“SI_PROGID”].ToString()

                     .ToLower() == “crystalenterprise.diskunmanaged”)

          {

              var outputfilecount =

                  Convert.ToInt16(mySchedInfo.Properties[“SI_DESTINATIONS”].Properties[“1”].Properties[“SI_DEST_SCHEDULEOPTIONS”]

                      .Properties[“SI_OUTPUT_FILES”].Properties[1].Value);

              if (outputfilecount > 0)

              {

                  for (int i = 2; i <= outputfilecount+1; i++)

                  {

                      String outputfilename =

                          mySchedInfo.Properties[“SI_DESTINATIONS”].Properties[“1”].Properties[“SI_DEST_SCHEDULEOPTIONS”]

                              .Properties[“SI_OUTPUT_FILES”].Properties[i].Value.ToString().ToLower();

                      String newfilename=outputfilename;

                      if (outputfilename.Contains(“//oldservername/”))

                      {

                          newfilename = outputfilename.Replace(“//oldservername/”, “//newservername/”);

                      }

                      if (outputfilename.Contains(“//newservername/”) && bAllowSwitchback)

                      {

                          newfilename = outputfilename.Replace(“//newservername/”, “//oldservername/”);

                      }

                      if (outputfilename!=newfilename)

                      {

                          mySchedInfo.Properties[“SI_DESTINATIONS”].Properties[“1”].Properties[“SI_DEST_SCHEDULEOPTIONS”]

                              .Properties[“SI_OUTPUT_FILES”].Properties[i].Value = newfilename;

                          // the following helped with debugging – I can examine the filenames array to see the cummulative changes

                          filenames[fn, 0] = myReportName;

                          filenames[fn, 1] = outputfilename;

                          filenames[fn, 2] = newfilename;

                          fn = fn + 1;

                      }

                  }

              }

          }

        }

        catch(Exception ex)

        {

            if (ex.Message != “Item SI_PROGID was not found in the collection.”)

                throw ex;

        }

      n = n + 1;

      }

      //Commit the changes back

      for (int i = 0; i < 2000; i++)

      {

          if(filenames[i,0]!=null)

          Console.WriteLine(“Report {0} was {1} is now {2}”, filenames[i, 0], filenames[i, 1], filenames[i, 2]);

      }

      if (bSavechanges)

          _InfoStore.Commit(myInfoObjects);

    }

    (0) 
    1. Dell Stinnett-Christy

      The one change I would make would be to change this:

      InfoObjects myInfoObjects = _InfoStore.Query(BOQUERY_ScheduledReport.ToString());

      to this:

      using (InfoObjects myInfoObjects = _InfoStore.Query(BOQUERY_ScheduledReport.ToString())

      {

      <rest of code>

      }

      At a very low level, the .NET components rely on COM.  .NET does not memory manage COM objects very well.  So, you have to either explicitly call .Dispose() on many of the objects from the SDK or you have to put them in Using clauses or For loop so that they are automatically disposed.  If you don’t, you will have memory leaks.

      -Dell

      (0) 
  6. admin basis

    Hey guys, Im trying to run this script in my 4.0 SP8 system but getting the following exception. I have already copied all jar files to /web-inf/lib on my tomcat server. Any ideas what i could be doing wrong ?

    message

    description The server encountered an internal error () that prevented it from fulfilling this request.

    exception

    org.apache.jasper.JasperException: Unable to compile class for JSP:

    An error occurred at line: 33 in the jsp file: /dash.jsp

    IProperties cannot be resolved to a type

    30: {

    31:   iObject = (IInfoObject)iObjects.get(i);

    32:   out.print(“<tr><td>” + iObject.getTitle() + “</td><td>” + iObject.getID() + “</td>”);

    33:   IProperties prop = iObject.properties();

    34:   IProperty getProp = prop.getProperty(“SI_PARENTID”);

    35:   String FolderID = getProp.toString();

    36:   IInfoObjects folder = iStore .query(“select si_id,si_name,si_parentid,si_path from ci_infoobjects where si_id=” + FolderID);

    An error occurred at line: 34 in the jsp file: /dash.jsp

    IProperty cannot be resolved to a type

    31:   iObject = (IInfoObject)iObjects.get(i);

    32:   out.print(“<tr><td>” + iObject.getTitle() + “</td><td>” + iObject.getID() + “</td>”);

    33:   IProperties prop = iObject.properties();

    34:   IProperty getProp = prop.getProperty(“SI_PARENTID”);

    35:   String FolderID = getProp.toString();

    36:   IInfoObjects folder = iStore .query(“select si_id,si_name,si_parentid,si_path from ci_infoobjects where si_id=” + FolderID);

    37:   IInfoObject ifolder=(IInfoObject)folder.get(0);   

    An error occurred at line: 68 in the jsp file: /dash.jsp

    IProperties cannot be resolved to a type

    65:   else if((ifolder.getKind().equals(“ObjectPackage”)))

    66:   {

    67:    //out.println(“<td><b>ObjectPackage</b>  ::  ” + ifolder.getTitle() + “</td></tr>”);

    68:    IProperties prop1 = ifolder.properties();

    69:   IProperty getProp1 = prop1.getProperty(“SI_PARENTID”);

    70:   String FolderID1 = getProp1.toString();

    71:   IInfoObjects folder1 = iStore .query(“select * from ci_infoobjects where si_id=” + FolderID1);

    An error occurred at line: 69 in the jsp file: /dash.jsp

    IProperty cannot be resolved to a type

    66:   {

    67:    //out.println(“<td><b>ObjectPackage</b>  ::  ” + ifolder.getTitle() + “</td></tr>”);

    68:    IProperties prop1 = ifolder.properties();

    69:   IProperty getProp1 = prop1.getProperty(“SI_PARENTID”);

    70:   String FolderID1 = getProp1.toString();

    71:   IInfoObjects folder1 = iStore .query(“select * from ci_infoobjects where si_id=” + FolderID1);

    72:   IInfoObject ifolder1=(IInfoObject)folder1.get(0);   

    Stacktrace:

        org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:92)

        org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:330)

        org.apache.jasper.compiler.JDTCompiler.generateClass(JDTCompiler.java:439)

        org.apache.jasper.compiler.Compiler.compile(Compiler.java:356)

        org.apache.jasper.compiler.Compiler.compile(Compiler.java:334)

        org.apache.jasper.compiler.Compiler.compile(Compiler.java:321)

        org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:592)

        org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328)

        org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)

        org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)

        javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

    (0) 
        1. Prithviraj Shekhawat Post author

          Verify you have copied the below import statement in your code

          <%@ page import=”com.crystaldecisions.sdk.properties.*” %>


          If yes, re-build your application and test. And make sure you have your jars in the application’s lib rather than tomcat’s lib.

          (0) 
    1. Prithviraj Shekhawat Post author

      AdminTools is a web application which comes with BusinessObjects installation. Check whether you have the application deployed on the server(tomcat by default).

      Also check if you are hitting the right server.

      (0) 
  7. Mayank M

    Yes, I am able to access CMCAPP and INFOVIEW app. My website is hosted on IIS. . Can you please tell where to find this on IIS.


    Regards,

    Mayank

    (0) 
  8. green bear

    how do we get this result set converted to an excel format, please help me. reason I have too many reports and currently I need to add each record manually.

    (0) 
  9. Mirko Manicardi

    Thanks Prithviraj for your script, it’s very useful!!

    Can I ask you to improve this script adding some files info (for ex: file size, file path, file name from SI_FILES)

    Thank you so much in advance

    (0) 
  10. Sarah Guo

    Hi Prithviraj,

    When I schedule a webi report, and choose “Default Enterprise Location” as destination, does your solution work for this scenario?

    Thanks,

    Sarah

    (0) 
    1. Prithviraj Shekhawat Post author

      Hi Sarah,

      This works for reports in public folders and not for report instances.

      By few modifications this can be used to get the details for instances as well. I already have that code, I would need to check for it and will share.

      Thanks,

      Prithvi

      (0) 
  11. Marven Amomonpon

    Hi Prithvi,

    I got this code working.

    I want to have one more column SI_UPDATE_TS added in the output, could you please help me to achieve this.

    Regards,

    Marven

    (0) 
    1. Prithviraj Shekhawat Post author

      You can use the below line.

      String update_TS_Prop= prop.getProperty(“SI_UPDATE_TS“).getValue().toString();


      You can then print the update_TS_Prop with out.println() statement.


      Thanks,

      Prithvi

      (0) 
  12. Joel Gicquel

    Hi Prithviraj, I am trying to use the java code you provided to pull the required list of reports and folder structure but the link you provided for more information around this process seems to be broken.

    Can you provide me some more information on how to run the java code, so I can send on instructions to my IT department to run and get the required information I need.

    Many thanks in advance

    Joel

    (0) 
  13. Kedar Kale

    Hi Joel,

    If you are on BI 4.x please create a new web application as below so you can execute the code:

    – Go to Tomcat Installation Directory. (C:\Program Files (x86)\SAP BusinessObjects\Tomcat6)

    – Now go to webapps folder in tomcat directory (C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\Webapps).

    – Create a new application folder within webapps (ex. Test)

    – Create a new folder named ‘WEB-INF’ within ‘Test’ Folder. (C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\webapps\Test\WEB-INF)

    – Copy the ‘lib’ folder from the mentioned location: (C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java\lib) and paste it to (C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\webapps\Test\WEB-INF \lib)

    – Place your all jsp files at the following location (C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\webapps\Test\index.jsp)

    – Restart tomcat and run the code in browser (http:\\localhost:8080\Test\index.jsp).

    Above steps are for BI on windows if you are n Linux then you will get those jar files in parallel directory structure.

    Regards,

    Kedar Kale.

    (0) 

Leave a Reply