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:
http://scn.sap.com/people/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();
}
}
%>
|
Hi Prithvi,
I downloaed and added poi-3.5-FINAL.jar to <>BOInstallDir>\javasdk\lib and put this code in AdminTools folder however getting http 500 error when accessing this jsp.
Regards,
Lokesh
Hi Lokesh,
Placing the jar at the specified location will not make the code run. You require the jar in the applicatons context. Also after plaaving the jar in application/WEB-INF/lib, it requires a restrt of the application server. It is best to create a new WAR structure with all the required libraries.
If you want to place the file in AdminTools and cannot restart the server, you would need to remove the excel code from the above jsp, it will display the results in tabular format and then you would need to copy paste the results in an excel. Below is the code for that.
==========================
<%@ 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.*"%>
<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>
<%
String user = "username";
String password = "password";
String cmsName = "cmsname";
String cmsAuthType = "secEnterprise";
String starttime=null;
String endtime=null;
String typeCheck=null;
IEnterpriseSession es=null;
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>");
}
}
catch(SDKException e)
{
out.println(e.getMessage());
}
finally
{
if(es!=null)
{
es.logoff();
}
}
%>
=====================
Thanks,
Prithvi