Extract list of reports using java program objects

[Former Member]

In this blog, I will be demonstrating a program object to export the list of reports organized in folders in a Business Objects environment.

Prerequisites:

1. BusinessObjects Enterprise System
2. BusinessObjects Enterprise Java SDKs

Creation of a Java Program Object:

• For creating a java program object, you need to write a java class which has to implement the interface IProgramBase.
• Once you create the java file, you would then need to create a jar file for the java class by setting all the required jars in your classpath.
• Below is a sample java file to export the list of reports along with folder path in a BusinessObjects environment

import com.crystaldecisions.sdk.plugin.desktop.program.*;

import com.crystaldecisions.sdk.framework.*;

import com.crystaldecisions.sdk.occa.infostore.*;

import com.crystaldecisions.sdk.exception.*;

import java.io.FileWriter;

import java.io.*;

import com.crystaldecisions.sdk.occa.report.lib.PropertyBag;

import com.crystaldecisions.sdk.properties.IProperties;

import com.crystaldecisions.sdk.properties.IProperty;

import com.crystaldecisions.sdk.plugin.desktop.folder.*;

import java.util.*;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFPrintSetup;

public class ListReportsInFolder implements IProgramBase

{

public void run(IEnterpriseSession enterprisesession,IInfoStore iStore,String str[]) throws SDKException

{

  System.out.println("Connected to " + enterprisesession.getCMSName() + "CMS");

    System.out.println("Using the credentials of " + enterprisesession.getUserInfo().getUserName() );

  String reportOwner=null;

  String reportCreationTime=null;

  String finalFolderPath=null;

  XSSFWorkbook wb = new XSSFWorkbook();

  XSSFSheet sheet = wb.createSheet("new sheet");

  XSSFRow rowhead = sheet.createRow((short)0);

  rowhead.createCell((short) 0).setCellValue("Report Name");

  rowhead.createCell((short) 1).setCellValue("Report ID");

  rowhead.createCell((short) 2).setCellValue("FolderPath");

  rowhead.createCell((short) 3).setCellValue("Owner");

  rowhead.createCell((short) 4).setCellValue("Creation Time");

  int index = 1;

  try

  {

  iStore = (IInfoStore) enterprisesession.getService("", "InfoStore");

  if(str.length>0)

  {

  System.out.println("The runtime parameters for this app were:");

  BufferedReader br=new BufferedReader(new FileReader(str[0]));

  String parentId;

  while((parentId=br.readLine()) != null)

  {

  IInfoObjects infoobjects = iStore .query("SELECT * from CI_INFOOBJECTS WHERE  si_instance=0 and si_kind in('crystalreport','webi') and si_parentid="+parentId+ " order by si_creation_time");         

  for(int i=0;i<infoobjects.size();i++)

  {

  IInfoObject infoobject=(IInfoObject) infoobjects.get(i);

  ISchedulingInfo schedInfo=infoobject.getSchedulingInfo();

  int reportID=infoobject.getID();

  String reportName=infoobject.getTitle();

  System.out.println("Report Id: "+reportID);

  System.out.println("Report Name: "+reportName);

  IProperties reportProperties=(IProperties)infoobject.properties();

  IProperty reportProperty=reportProperties.getProperty("SI_OWNER");

  if(reportProperty != null)

  {

  reportOwner=reportProperty.getValue().toString();

  System.out.println("Report Owner: "+reportOwner);

  }

  IProperty reportProperty1=reportProperties.getProperty("SI_CREATION_TIME");

  if(reportProperty1 != null)

  {

  reportCreationTime=reportProperty1.getValue().toString();

  System.out.println("Report Creation Time: "+reportCreationTime);

  }

  int id=infoobject.getParentID();

  IInfoObjects infoobjects1 = iStore .query("SELECT * from CI_INFOOBJECTS WHERE  si_id="+id);

  IInfoObject infoobject1=(IInfoObject)infoobjects1.get(0);

  int parentid=infoobject1.getID();

  if(infoobject1.getKind().equals("Folder"))

  {

    finalFolderPath="/";

   IFolder iifolder=(IFolder)infoobject1;

   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();

   }

   System.out.println("Report Folder Path: "+finalFolderPath);

  }

                

  XSSFRow row = sheet.createRow((short)index);

  row.createCell((short) 0).setCellValue(reportName);

  row.createCell((short) 1).setCellValue(reportID);

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

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

  row.createCell((short) 4).setCellValue(reportCreationTime);

                index++;

  }

  }

  br.close();

  Date date=new Date();

  String date1= date.toString();

  String date2=date1.replaceAll("\\W", "_");

  FileOutputStream fileOut = new FileOutputStream(str[1] +"_"+date2 +".xlsx" );

  wb.write(fileOut);

  fileOut.close();

  System.out.println("Excel File Generated at "+str[1]);

  }

  }

      catch(Exception e)

  {

  System.out.println(e.getMessage());

  }

}

}

• Copy the above code into a text file and save it as ListReportsInFolder.java. Please make sure that your java class name and file name should exactly be the same (case sensitive).
• Compile the above java class by having all the jars required to run the file in the classpath. You can also use eclipse IDE to compile and create jar file. The list of jars can be found from the developers guide available at below links:
  • For BI 4.0:

               http://help.sap.com/businessobject/product_guides/boexir4/en/xi4_boejava_dg_en.zip

• For XI 3.1

               http://help.sap.com/businessobject/product_guides/boexir31/en/boesdk_java_dg_12_en.zip

• Refer to the section 'JAR files needed for deployment of Business Objects Software' from the above guides to get the list of jar files.
• You would specifically need the jar files listed under 'BusinessObjects Enterprise Java SDK'
• Once you have compiled the code, create a jar file for your compiled .class file.

Publishing the Java Program Object to the Enterprise System:

1. In the CMC, go to Folders and create new folder "Objects" or navigate to an existing folder where you want to publish the program object
2. Select the folder "Objects" and click on Manage | Add | Program File
3. Choose as Program Type Java and add ListReports.jar
4. Right Click on ListReports within your Objects folder and choose Properties | Default Settings | Program Parameters
5. Specify as "Class to run:" ListReportsInFolder.
6. The arguments must be specified within double quotes & separated with a space so that program object can identify the multiple arguments. In the Arguments Section, pass the below mentioned arguments:
   1. Path to the text file containing SI_ID of the BO Folders: This program object requires a text file containing SI_ID of the folder specified on each line for which all the reports will be listed. It will take SI_ID of each folder as input from the text file and will extract the relevant report details. For Example:“D:\Input\arguments.txt”
   2. Path along with the file name where excel file would be generated after successful execution of the utility. For Example: “D:\Output\Report_List”
7. In the class path, specify the path along with the name of the third party external jar files required by program to export the results to an excel file
8. Test "Run Now" and schedule the Program Object.

You would then have an excel file generated at the above specified location with the details of reports in a Business Objects environment.