Get Failed Instance Information-Export To Excel

Former Member · ‎12-19-2014

This document provides a way get the failed instance information using the BusinessObjects Enterprise Platform Java SDKs and export to Excel. It is a continuation of the document http://scn.sap.com/docs/DOC-57558.

Have seen requirements where people have requested for the same information in an excel and I am adding the excel export options to the existing documents.

For more scripts and information on how to run these scripts refer to the blog avaiable here:

shawn.penner/blog/2013/06/04/scripts-and-samples

Below is the Java Server Pages (JSP) sample

Notes:

You would need to change the userName, password, cmsName to the values specific to your enterprise server in the provided sample code. Also would need to provide the location where you need the excel to be exported.
You would reuire the poi-3.5-FINAL.jar file in your applications lib along with BusinessObjects jar files.
The part of exporting the results to an excel file does not come under the support of BusinessObjects as you would be using third party jars with BusinessObjects jar files to have the results exported to excel.

Failed Instance Info

<%@ page import = "com.crystaldecisions.sdk.framework.*"%>
<%@ page import = "com.crystaldecisions.sdk.occa.infostore.*"%>
<%@ page import = "com.crystaldecisions.sdk.exception.*"%>
<%@ page import = "com.crystaldecisions.sdk.properties.*"%>
<%@ page import = "java.util.*"%>
<%@ page import = "java.io.*"%>
<%@ 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 = "org.apache.poi.hssf.usermodel.HSSFPrintSetup"%>
<html>
<head></head>
<body>
<table border="2" cellpadding="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
<tr>
<th width="5%">Instance Id</th>
<th width="25%">Instance Name</th>
<th width="15%">Schedule Type</th>
<th width="12%">Start Time</th>
<th width="20%">End Time </th>
<th width="20%">Error Message</th>
</tr>
<%

   HSSFWorkbook wb = new HSSFWorkbook();
   HSSFSheet sheet = wb.createSheet("new sheet");
   HSSFRow rowhead = sheet.createRow((short)0);
   rowhead.createCell((short) 0).setCellValue("Instance ID");
   rowhead.createCell((short) 1).setCellValue("Instance Name");
   rowhead.createCell((short) 2).setCellValue("Schedule Type");
   rowhead.createCell((short) 3).setCellValue("Start Time");
   rowhead.createCell((short) 4).setCellValue("End Time");
   rowhead.createCell((short) 5).setCellValue("Error Message");

   String user = "username";
   String password = "password";
   String cmsName = "cmsname";
   String cmsAuthType = "secEnterprise";
   String starttime=null;
   String endtime=null;
   String typeCheck=null;

   IEnterpriseSession es=null;
   int index=1;

   try
   {

     es = CrystalEnterprise.getSessionMgr().logon( user, password, cmsName, cmsAuthType);
     IInfoStore iStore = (IInfoStore) es.getService("", "InfoStore");
     IInfoObjects infoobjects = iStore .query("SELECT * from CI_INFOOBJECTS WHERE si_parentid=2664098 and si_schedule_status=3");

     for(int i=0;i<infoobjects.size();i++)
      {
       IInfoObject infoobject=(IInfoObject) infoobjects.get(i);
       ISchedulingInfo schedInfo=infoobject.getSchedulingInfo();
       int instanceID=infoobject.getID();
       String instanceName=infoobject.getTitle();
       String errorMsg=schedInfo.getErrorMessage();
       out.println("<tr><td>"+instanceID+"</td>");
       out.println("<td>"+instanceName+"</td>");
       int type=schedInfo.getType();


       switch(type)
        {
         case CeScheduleType.ONCE:
         typeCheck="ONCE";
         out.println("<td>"+typeCheck+"</td>");
         break;

         case CeScheduleType.HOURLY: //Job has failed.
         typeCheck="HOURLY";
         out.println("<td>"+typeCheck+"</td>");
         break;

         case CeScheduleType.DAILY: //Job is paused.
         typeCheck="DAILY";
         out.println("<td>"+typeCheck+"</td>");
         break;

         case CeScheduleType.WEEKLY: //Job is pending.
         typeCheck="WEEKLY";
         out.println("<td>"+typeCheck+"</td>");
         break;

         case CeScheduleType.MONTHLY: //Job is running.
         typeCheck="MONTHLY";
         out.println("<td>"+typeCheck+"</td>");
         break;

         case CeScheduleType.NTH_DAY:
         typeCheck="NTH_DAY";
         out.println("<td>"+typeCheck+"</td>");
         break;

         case CeScheduleType.FIRST_MONDAY: //Job has failed.
         typeCheck="FIRST_MONDAY";
       out.println("<td>"+typeCheck+"</td>");
       break;

       case CeScheduleType.LAST_DAY: //Job is paused.
       typeCheck="LAST_DAY";
       out.println("<td>"+typeCheck+"</td>");
       break;

       case CeScheduleType.CALENDAR: //Job is pending.
       typeCheck="CALENDAR";
       out.println("<td>"+typeCheck+"</td>");
       break;

       case CeScheduleType.CALENDAR_TEMPLATE: //Job is running.
        typeCheck="CALENDAR_TEMPLATE";
       out.println("<td>"+typeCheck+"</td>");
        break;
        }


       IProperties instanceProperties=(IProperties)infoobject.properties();
       IProperty instanceProperty=instanceProperties.getProperty("SI_STARTTIME");
       if(instanceProperty != null)
        {
         starttime=instanceProperty.getValue().toString();
         out.println("<td>"+starttime+"</td>");
        }
       IProperties instanceProperties1=(IProperties)infoobject.properties();
       IProperty instanceProperty1=instanceProperties1.getProperty("SI_ENDTIME");
       if(instanceProperty1 != null)
        {
         endtime=instanceProperty1.getValue().toString();
         out.println("<td>"+endtime+"</td>");
        }
       out.println("<td>"+errorMsg+"</td></tr>");

       HSSFRow row = sheet.createRow((short)index);
       row.createCell((short) 0).setCellValue(instanceID);
       row.createCell((short) 1).setCellValue(instanceName);
       row.createCell((short) 2).setCellValue(typeCheck);
       row.createCell((short) 3).setCellValue(starttime);
       row.createCell((short) 4).setCellValue(endtime);
       row.createCell((short) 5).setCellValue(errorMsg);

       index++;


      }
    }
    catch(SDKException e)
    {
     out.println(e.getMessage());
    }
    finally
    {
     FileOutputStream fileOut = new FileOutputStream("C://Users//881947//FailedInstanceDetails.xls");//Specify the path for the excel to be generated.
     wb.write(fileOut);
     fileOut.close();

     if(es!=null)
     {
       es.logoff();
     }
    }
%>

Get Failed Instance Information-Export To Excel

SAP PI for Beginners

ABAP 7.40 Quick Reference

Fiori: technical installation and configuration of one app from A - Z