Getting the lists of all web intelligence reports ...

Former Member · ‎05-21-2014

There have been many requirements about getting the list and full folder location of all the web intelligence reports with refresh on open option checked 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 executing the below query:

For checking the webi report is saved with refresh on open option or not, we run following query:

SELECT SI_WEBI_DOC_PROPERTIES FROM CI_INFOOBJECTS WHERE SI_KIND='WEBI' AND SI_INSTANCE=0

In above query result we check if the "is_refresh_on_open" is set to true or false.

SELECT SI_ID,SI_NAME,SI_PARENTID FROM CI_INFOOBJECTS WHERE SI_KIND ='Webi' AND SI_INSTANCE=0, you get to list the id, name and parentId of all the webi 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 multi-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 web intelligence reports saved with refresh on open optioned checked and their folder path in the enterprise system.

Note:

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

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

Sample code to achieve above

<%@ 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(" ==================================================================== ");
out.println(" Report ID :"+reportID);
out.println(" 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(" 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(" Folder Path : " + finalFolderPath);
 row.createCell((short) 3).setCellValue(finalFolderPath);
 }
else if((ifolder.getKind().equals("FavoritesFolder")))
 {
 String fFolder="FavoritesFolder : " + ifolder.getTitle();
 out.println(" Folder Path: FavoritesFolder :: " + ifolder.getTitle());
 row.createCell((short) 3).setCellValue(fFolder);
 }
else if((ifolder.getKind().equals("Inbox")))
 {
 out.println(" Inbox :: " + ifolder.getTitle());
 String inbox="Inbox : " + ifolder.getTitle();
 row.createCell((short) 3).setCellValue(inbox);
 }
else if((ifolder.getKind().equals("ObjectPackage")))
 {
 out.println(" ObjectPackage :: " + 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(" Report Folder : "+ finalFolderPath1 + " ");
 String oPackage=" ObjectPackage : " + ifolder.getTitle();
 row.createCell((short) 3).setCellValue(oPackage);
 }

 }
 index++;

}

boDocumentInstance.closeDocument();
boDocumentInstance = null;
boReportEngine.close();
boReportEngine = null;

}
out.println(" 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(" Excel file created successfully");

}
%>

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

Get Your SAP HANA Idea Incubator Badge Today!

SCN Mission - SAP HANA Quiz Challenge is now retired

Share your #HANAStory and Win