Getting the lists of all web intelligence reports with ‘refresh on open’ option checked and their folder locations in the enterprise using Java SDK

<%@ page import=”com.crystaldecisions.sdk.framework.*” %>
<%@ page import=”com.crystaldecisions.sdk.occa.infostore.*” %>
<%@ page import=”com.crystaldecisions.sdk.plugin.CeKind” %>
<%@ page import=”com.businessobjects.rebean.wi.*” %>
<%@ page import=”java.util.Properties” %>
<%@ page import=”com.crystaldecisions.sdk.properties.*” %>
<%@ page import=”com.crystaldecisions.sdk.plugin.desktop.folder.*” %>
<%@ page import=”java.util.*” %>
<%@ page import =”org.apache.poi.hssf.usermodel.HSSFSheet”%>
<%@ page import =”org.apache.poi.hssf.usermodel.HSSFWorkbook”%>
<%@ page import =”org.apache.poi.hssf.usermodel.HSSFCell”%>
<%@ page import =”org.apache.poi.hssf.usermodel.HSSFRow”%>
<%@ page import =”java.io.*”%>
<%@ page import =”org.apache.poi.hssf.usermodel.HSSFPrintSetup”%>

<%
/************************** RETRIEVING PARAMETERS **************************/

// Retrieve the logon information
String username = “<User_Name>”;
String password = “<User_Password>”;
String cmsName  = “<CMS_NAME>”;
String authType = “<Auth_Type>”;
DocumentInstance boDocumentInstance=null;
ReportEngine boReportEngine=null;
IInfoStore boInfoStore=null;
IInfoObjects boInfoObjects=null;
String reportOwner=null;

  HSSFWorkbook wb = new HSSFWorkbook();
  HSSFSheet sheet = wb.createSheet(“new sheet”);
  HSSFRow rowhead = sheet.createRow((short)0);
  rowhead.createCell((short) 0).setCellValue(“Report ID”);
  rowhead.createCell((short) 1).setCellValue(“Report Name”);
  rowhead.createCell((short) 2).setCellValue(“Report Owner”);
  rowhead.createCell((short) 3).setCellValue(“Folder Location”);
  int index = 1;

try

{
 
// Retrieve the name of the Web Intelligence document to be used in the sample
String webiDocName = null;

// Retrieve the refresh flag
String refreshOnOpen = “false”;
String Chk=”true”;

/************************** LOGON TO THE ENTERPRISE **************************/

// Logon to the enterprise
IEnterpriseSession boEnterpriseSession = CrystalEnterprise.getSessionMgr().logon( username, password, cmsName, authType);

/************************** RETRIEVE INFOOBJECT FOR THE WEBI DOCUMENT **************************/

// Retrieve the IInfoStore object
boInfoStore =(IInfoStore) boEnterpriseSession.getService(“InfoStore”);

// Build query to retrieve the InfoObjects for Web Intelligence document
String query = “SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND='” + CeKind.WEBI + “‘ AND  SI_INSTANCE=0”;

// Execute the query
boInfoObjects = (IInfoObjects) boInfoStore.query(query);
   
for(int i =0; i<boInfoObjects.getResultSize();i++)
  
    {
     
  // Retrieve the first InfoObject instance of the Web Intelligence document
  IInfoObject boInfoObject = (IInfoObject) boInfoObjects.get(i);

  int reportID=boInfoObject.getID();
  String reportName=boInfoObject.getTitle();
  String finalFolderPath=””;

// Retrieve the Report Engines
ReportEngines boReportEngines = (ReportEngines) boEnterpriseSession.getService(“ReportEngines”);;

// Retrieve the Report Engine for Web Intelligence documents
    boReportEngine = boReportEngines.getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE);

// Retrieve the document instance for the Web Intelligence document
boDocumentInstance = boReportEngine.openDocument(boInfoObject.getID());

 
// Retrieve the properties of the document
Properties properties = boDocumentInstance.getProperties();

if(properties.getProperty(PropertiesType.REFRESH_ON_OPEN).equals(Chk))
{
 
HSSFRow row = sheet.createRow((short)index);
out.println(“<BR>====================================================================<BR>”);
out.println(“<BR>Report ID :”+reportID);
out.println(“<BR>Report Name :”+reportName);
row.createCell((short) 0).setCellValue(reportID);
row.createCell((short) 1).setCellValue(reportName);

IProperties instanceOwnerProperties=(IProperties)boInfoObject.properties();
IProperty reportOwnerProperty=instanceOwnerProperties.getProperty(“SI_OWNER”);

  if(reportOwnerProperty != null)
   {
   reportOwner=reportOwnerProperty.getValue().toString();
   out.println(“<BR>Report Owner :”+reportOwner);
   }

row.createCell((short) 2).setCellValue(reportOwner);

IProperties prop = boInfoObject.properties();
IProperty getProp = prop.getProperty(“SI_PARENTID”);
   
String FolderID = getProp.toString();
IInfoObjects folder = boInfoStore.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;

   if(iifolder.getPath()!= null)
    {
    String path[]=iifolder.getPath();
     for(int fi=0;fi<path.length;fi++)
      {
       finalFolderPath = path[fi] + “/” + finalFolderPath;
      }
     finalFolderPath = finalFolderPath + iifolder.getTitle();
     row.createCell((short) 3).setCellValue(finalFolderPath);
    }
   else
    {
    finalFolderPath=finalFolderPath+iifolder.getTitle();
    }
  
   out.println(“<BR> Folder Path : ” + finalFolderPath);
   row.createCell((short) 3).setCellValue(finalFolderPath);
   }
  else if((ifolder.getKind().equals(“FavoritesFolder”)))
   {
   String fFolder=”FavoritesFolder  :  ” + ifolder.getTitle();
   out.println(“<BR> Folder Path:   FavoritesFolder</b>  ::  ” + ifolder.getTitle());
   row.createCell((short) 3).setCellValue(fFolder);
   }
  else if((ifolder.getKind().equals(“Inbox”)))
   {
   out.println(“<BR> <b>Inbox</b>  ::  ” + ifolder.getTitle());
   String inbox=”Inbox : ” + ifolder.getTitle();
   row.createCell((short) 3).setCellValue(inbox);
   }
  else if((ifolder.getKind().equals(“ObjectPackage”)))
   {
   out.println(“<BR> <b>ObjectPackage</b>  ::  ” + ifolder.getTitle());
  
   IProperties prop1 = ifolder.properties();
   IProperty getProp1 = prop1.getProperty(“SI_PARENTID”);
   String FolderID1 = getProp1.toString();
   IInfoObjects folder1 = boInfoStore .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(“<BR> Report Folder :  “+ finalFolderPath1 + “<br />”);
    String oPackage=” ObjectPackage : ” + ifolder.getTitle();
    row.createCell((short) 3).setCellValue(oPackage);
    }
  
   }
   index++;
  
}

boDocumentInstance.closeDocument();
boDocumentInstance = null;
boReportEngine.close();
boReportEngine = null;
             
}
out.println(“<BR><BR>Process Complete.”);

/************************** CLEAN UP **************************/
boEnterpriseSession.logoff();
boEnterpriseSession = null;
}

catch(Exception e)
  {
   out.println(e.getMessage());
  }
finally
  {
  FileOutputStream fileOut = new FileOutputStream(“c:\\ReportDetails.xls”);
  wb.write(fileOut);
  fileOut.close();
  out.println(“<BR> Excel file created successfully”);
 
  }
%>