Hi all,
I just ran into this thread. Looks like somebody needs exactly what I needed 6 months ago. I actually was able to look for reports containing a particular object. There are 2 main tables in the Business Objects repository which hold these information. The first table is the CI_APPOBJECTS. This holds the information about all the universes in the repository. The second is the CI_INFOOBJECTS. Here you can get the information about all the folders and documents. If you restrict the SI_KIND to ‘Webi’, you can see all the webi documents in the repository. I actually wrote a Java code to collect all this information as to what all reports exist in the repository, their location and the objects in the reports and fed them into tables of an oracle database. I then created a universe off it and used that to create reports as to which reports use a particular object. I had to do this because of the massive number of reports we had. Here is the code:
import com.businessobjects.rebean.wi.*;
import com.crystaldecisions.enterprise.ocaframework.idl.OCA.OCAi.InfoStore;
import com.crystaldecisions.sdk.occa.infostore.*;
import com.crystaldecisions.sdk.properties.IProperties;
import com.crystaldecisions.sdk.properties.internal.SDKPropertyBag;
import com.crystaldecisions.sdk.framework.*;
import com.crystaldecisions.sdk.exception.SDKException;
import java.sql.*;
import java.util.*;
import java.util.Map.Entry;
public class Pavan {
public static void main(String[] args) {
System.out.println("START");
try {
// Initiating the logon to the CMS
IEnterpriseSession enterpriseSession = CrystalEnterprise.getSessionMgr().logon( "josyulp", "123456", "Whale", "secEnterprise");
IInfoStore infoStore = (IInfoStore)enterpriseSession.getService("", "InfoStore");
ReportEngine webiRepEngine = (ReportEngine)
enterpriseSession.getService("","WebiReportEngine");
Pavan p=new Pavan();
String result=p.getList(infoStore,webiRepEngine);
System.out.println(result);
enterpriseSession.logoff();
System.out.println("END");
} catch (Exception e) {
e.printStackTrace();
}
}
//You can ignore this and go to the other functions as this connects to the oracle database using a JDBC driver.
private String getList(IInfoStore oInfoStore, ReportEngine webiEngine)
{
String Connectionstatus = null;
String logout = "Failed";
String disconnect = "Failed";
String HTMLObjectList = "";
String Update = "";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (Exception exc) {return ("Unable to initialize JDBC DRIVER");}
Connection con = oracleconnect(); if (con != null) {Connectionstatus = "Success";}
else { Connectionstatus = "Failed";}
String dimensionfeed = dimension(oInfoStore, webiEngine, con);
//String stmt = dropandaddtbls(con);
//Update = oraclefactfeed(webiEngine, con);
try{
con.close();
disconnect = "Success";
} catch (Exception exep) {exep.printStackTrace();}
return ( HTMLObjectList );
}
private String dimension(IInfoStore oInfoStore, ReportEngine webiEngine, Connection con)
{ int count = 0;
String droptable = "DROP TABLE REPORT_DEPENDENCY_DIM";
String createtable = "CREATE TABLE REPORT_DEPENDENCY_DIM (DOC_ID INTEGER, "+
"DOC_NAME VARCHAR(100), OWNER_NAME VARCHAR(32), "+
"PATH VARCHAR(1000),FOLDER_ID INTEGER, UNIVERSE_NAME VARCHAR(30),"+
" UNIVERSE_ID INTEGER )";
try {
Statement stmts = con.createStatement();
stmts.executeUpdate(droptable);
stmts.executeUpdate(createtable);
System.out.println("Success in dropping and recreating dimension table");
stmts.close();} catch (SQLException xyz) {xyz.printStackTrace();}
try
{
String query = "SELECT TOP 10000 SI_ID, SI_NAME, SI_UNIVERSE, SI_OWNER, SI_PARENTID "+ "" +
//"SI_CREATION_TIME, SI_UPDATE_TS " +
"FROM CI_INFOOBJECTS " +
"WHERE " +
" SI_KIND IN ('Webi') ";
IInfoObjects oInfoObjects = oInfoStore.query(query);
if (oInfoObjects.isEmpty())
{
return ("");
}
for (int x = 0; x < oInfoObjects.size(); x++)
{
try {
IInfoObject oInfoObject = (IInfoObject)oInfoObjects.get(x);
int id = oInfoObject.getID();
String title = oInfoObject.getTitle();
String owner = (String)oInfoObject.properties().getProperty("SI_OWNER").getValue();
int folderid = ((Integer)oInfoObject.properties().getProperty("SI_PARENTID").getValue()).intValue();
String folder ="";
String foldername = folder(folderid, oInfoStore, folder);
IInfoObject o=oInfoObject;
String universeName=" None ";
int universeID = 0;
String dummy = null;
IProperties ps=o.properties();
Integer ids[]=o.propertyIDs();
for (int p=0;p<ids.length;p++)
{
com.crystaldecisions.sdk.properties.IProperty ip=ps.getProperty(ids[p]);
if (ip.isContainer())
{
Object container=ip.getValue();
if (container instanceof SDKPropertyBag)
{
SDKPropertyBag bag=(SDKPropertyBag)container;
//System.out.println(""+bag);
Iterator bagIt=bag.entrySet().iterator();
while (bagIt.hasNext())
{
Entry e=(Entry)bagIt.next();
Integer one=new Integer(1);
if (one.equals(e.getKey()))
{
dummy = ""+e.getValue();
universeName = getuniversename(dummy, oInfoStore);
universeID = Integer.parseInt(dummy);
}//end if
}//end while
}//end if
}//end if
}//end for
String Status = oracledatafeed(id, title, owner, foldername, folderid, universeName, universeID, con);
count++;
}catch (Exception c) //close try
{
c.printStackTrace();
try {System.out.println(c);}catch(Exception e){}
}
}//close for
}catch (SDKException sdkEx) //close try
{
sdkEx.printStackTrace();
return(sdkEx.getMessage());
}
System.out.println("Dimension table successfully loaded with "+count+" rows");
return (""+count);
}
private Connection oracleconnect()
{ Statement stmt = null;
Connection con = null;
try {
con = DriverManager.getConnection ("jdbc:oracle:thin:@magnum.nfs.nwie.net:1521:nfdwdevl","dcdalpha", "shane_hay");
stmt = con.createStatement();
} catch (SQLException e) {e.printStackTrace();}
return (con);
}
private String dropandaddtbls(Connection con)
{
String stmt = "Success";
String dropfact = "DROP TABLE REPORT_DEPENDENCY_FACT";
String dropprop = "DROP TABLE REPORT_PROPERTIES";
String createprop = "CREATE TABLE REPORT_PROPERTIES (DOC_ID INTEGER, LAST_SAVE VARCHAR(100), OWNER VARCHAR(32), "+
"LAST_REFRESH_DATE VARCHAR(100), MODIFICATION_DATE VARCHAR(100), CREATION_DATE VARCHAR(100), "+
"REFRESH_ON_OPEN VARCHAR(20), SIZE_REPORT INTEGER, REFRESH_DURATION INTEGER)";
String createfact = "CREATE TABLE REPORT_DEPENDENCY_FACT (DOC_ID INTEGER, OBJECT_ID VARCHAR(100), OBJECT_NAME VARCHAR(50))";
try {
Statement stmts = con.createStatement();
stmts.executeUpdate(dropfact);
stmts.executeUpdate(dropprop);
stmts.executeUpdate(createfact);
stmts.executeUpdate(createprop);
System.out.println("Success in dropping and recreating dimension and fact tables");
stmts.close();} catch (SQLException xyz) {xyz.printStackTrace();}
return (stmt);
}
private String getuniversename(String Universe, IInfoStore U)
{
String universename="";
try {
int UniverseID = Integer.parseInt(Universe);
String querys = "SELECT TOP 10000 SI_ID, SI_NAME FROM CI_APPOBJECTS WHERE SI_ID ="+Universe;
IInfoObjects oInfoObj = U.query(querys);
IInfoObject abc = (IInfoObject)oInfoObj.get(0);
universename = abc.getTitle();
} catch (Exception e) {e.printStackTrace();}
return ( universename );
}
private String oracledatafeed(int ids, String titles, String owners, String foldernames,int folderid, String universeNames, int universeIDs, Connection con)
{
try{
String insertintodim1 = "INSERT INTO REPORT_DEPENDENCY_DIM "+
"VALUES (?, ?, ?, ?, ?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(insertintodim1);
pstmt.clearParameters();
pstmt.setInt(1,ids);
pstmt.setString(2,titles);
pstmt.setString(3,owners);
pstmt.setString(4,foldernames);
pstmt.setInt(5,folderid);
pstmt.setString(6,universeNames);
pstmt.setInt(7,universeIDs);
//pstmt.setString(8,create);
//pstmt.setString(9,update);
int total=pstmt.executeUpdate();
if (total!=1)System.out.println("error!");
//stmt.executeUpdate(insertintodim);
//stmt.close();
pstmt.close();
} catch (Exception exp){exp.printStackTrace();}
return ("Data Loading Sucessful");
}
private String oraclefactfeed(ReportEngine report, Connection con)
{
// String query1 = "DELETE FROM ONLY (REPORT_DEPENDENCY_FACT) WHERE DOC_ID NOT IN (SELECT DOC_ID FROM REPORT_DEPENDENCY_DIM) ";
// String query2 = "DELETE FROM ONLY (REPORT_PROPERTIES) WHERE DOC_ID NOT IN (SELECT DOC_ID FROM REPORT_DEPENDENCY_DIM) ";
String query = "SELECT DOC_ID FROM REPORT_DEPENDENCY_DIM WHERE DOC_ID NOT IN (SELECT DOC_ID FROM REPORT_PROPERTIES)";
String HTML = " ";
int c = 1;
try {
System.out.println("Getting dimension and fact tables into Sync....");
Statement sts = con.createStatement();
//int z = sts.executeUpdate(query1);
//int zy = sts.executeUpdate(query2);
System.out.println("Currently loading into fact table");
Statement st = con.createStatement();
PreparedStatement ps=con.prepareStatement("INSERT INTO REPORT_DEPENDENCY_FACT VALUES (?,?,?)");
PreparedStatement proper=con.prepareStatement("INSERT INTO REPORT_PROPERTIES VALUES (?,?,?,?,?,?,?,?,?)");
ResultSet result = st.executeQuery(query);
while (result.next()) {
try {
int Doc = result.getInt("DOC_ID");
System.out.println("Processing Document: "+Doc);
DocumentInstance rep = report.openDocument(Doc);
Properties prop = rep.getProperties();
String refreshduration = rep.getProperties().getProperty("lastrefreshduration");
int documentsize1 = 0;
int refreshduration1 = 0;
if (refreshduration!= null)
{refreshduration1 = Integer.parseInt(refreshduration);
}
Object lastsave = rep.getProperties().getProperty("lastsavedby");
String lastsave1 = " "+lastsave;
Object lastrefresh = rep.getProperties().getProperty("lastrefreshdate");
String lastrefresh1 = " "+lastrefresh;
Object modification = rep.getProperties().getProperty("modificationdate");
String modification1 = " "+modification;
Object creator = rep.getProperties().getProperty("createdby");
String creator1 = " "+creator;
Object creation = rep.getProperties().getProperty("creationdate");
String creation1 = " "+creation;
Object refreshuponopen = rep.getProperties().getProperty("refreshonopen");
String refreshuponopen1 = " "+refreshuponopen;
String documentsize = rep.getProperties().getProperty("documentsize");
if (documentsize != null)
{ documentsize1 = Integer.parseInt(documentsize);
}
proper.setInt(1, Doc);
proper.setString(2, lastsave1);
proper.setString(3, creator1);
proper.setString(4, lastrefresh1);
proper.setString(5, modification1);
proper.setString(6, creation1);
proper.setString(7, refreshuponopen1);
proper.setInt(8, documentsize1);
proper.setInt(9, refreshduration1);
proper.executeUpdate();
//DataProviders myDataProvider = rep.getDataProviders();
//DataProvider myD = myDataProvider.getItem(0);
if (rep != null) {
//myD.generateQuery();
ReportDictionary integer = rep.getDictionary();
int childcount = integer.getChildCount();
for (int i = 0; i < childcount; i++) {
String ObjectID = integer.getChildAt(i).getID();
ReportExpression ObjectName = integer.getChild(ObjectID);
ps.setInt(1, Doc);
ps.setString(2, ObjectID);
ps.setString(3, ObjectName.toString());
c++;
int res = ps.executeUpdate();
if (res != 1)
System.out.println("error 1");
}
} else {
System.out.println(Doc);
}
report.close();
} catch (Exception e) {
System.out.println("=================="+e);
//e.printStackTrace();
}
}
st.close();
ps.close();
} catch (SQLException xyz) {xyz.printStackTrace();return (xyz.getMessage());}
return (HTML);
}
private String closeconnections(Connection connect, Statement state)
{try{
connect.close();
state.close();
} catch (Exception exep) {exep.printStackTrace();}
return ("Connection closed");
}
private String folder(int folderid, IInfoStore oInfoStore, String foldername)
{
if (folderid!=0)
{
String folderquery = "SELECT SI_NAME, SI_PARENTID" +
" FROM CI_INFOOBJECTS " +
"WHERE " +
" SI_ID="+folderid;
try {
IInfoObjects oFolder = oInfoStore.query(folderquery);
IInfoObject Folder = (IInfoObject)oFolder.get(0);
String foldernames = (String)Folder.properties().getProperty("SI_NAME").getValue();
foldername = foldernames+" / "+foldername;
int id = ((Integer)Folder.properties().getProperty("SI_PARENTID").getValue()).intValue();
foldername = folder(id, oInfoStore, foldername);
} catch (SDKException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
else {foldername = "Root / "+foldername;}
return (foldername);
}
}
//I was not having time to seperate it exclusively for others needs so as you can see the oracle connection is active and is //webbed a lot to use it in feeding the tables that I created.
It took me about 20 days of exclusive work on this to get this far. I am not that good at java so there can be somethings which might be a little rubbish. Please ignore them or correct them. This is working fine for my needs. I even had to know when the reports were last modified and also as to who created them and other properties of the reports too. I was actully getting a huge chunk of data here. Use parts of it as needed. There are around 50 jar files from business objects which you might find in the business objects directory. The JDBC connection needs the odbc-jdbc.jar which can be downloaded from the oracle website.
Good Luck customising this code for your needs.
PJ
pj820 (BOB member since 2005-10-07)