still waiting …BOB please help
nasir
botakhan 
(BOB member since 2005-08-18)
botakhan,
As far as I know it is not in the repository of XI R2.
Check
https://bobj-board.org/t/67992
Sorry,
Johan
JdVries 
(BOB member since 2006-02-06)
Hi there,
I just saw your post. Frankly telling you i cant help you in this at all as even i am trying out but i need your help.If you could tell him exactly in BO 6.5 how you can relate a report objects which is there in Corporate category with a universe.I mean to say as u said universename-columnname-report using it relation as i am unable to find any table in repository which contains the metadata of a report( i can only find the report name).
Please help me out in this as i am totally stucked 
-Arpan
arpan 
(BOB member since 2006-03-22)
The only way we found is to open the universe and analyze the sql to get the list of fields and tables used. We include it in Version Manager.
jp.golay 
(BOB member since 2002-06-17)
Hi there,
I wanted to know for BO 6.5. For BOXIr2 the tables are not present but for BO 6.5 we have some tables but i am not able to get the relation between them .TO be more specific i want to know how to retrieve the meta data for a report which is present in the repository(Document Category).There is a table called CONDATA which has something in excrypted form.What exaclty is it
-Arpan
arpan (BOB member since 2006-03-22)
Hi Arpan,
I am assuming that by report metadata you mean …Universe name , report name and objects used in the report …if this is what you need than you can get it from 6.5 repo
If you need to know that what object references which table.column in the underlying DB than its NOT possible through 6.5 repo.
I dont remember now exactly what tables give you the first…but I am sure you can get it in 6.5
Nasir
botakhan (BOB member since 2005-08-18)
Thanks Nasir for the information,
Can somebody help me know exactly which table has this information
-Arpan
arpan (BOB member since 2006-03-22)
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)
Sorry, I forgot to mention that this works only for XI R1 and XI R2
PJ
pj820 (BOB member since 2005-10-07)
Nice work, if I have well understood your code you are getting the list of universes and objects used by a webi report.
Do you think than it’s possible in java to go further to get the list of tables / fields used by a webi report object.
I did the same work in a Java web service called by a delphi program.
Then I opened the universe in designer from my Version Manager application and got the table / Fields analyzing the universe (No easy to get the list of fields used by an universe object as you can guess).
Do you know if it’s possible to open designer in Java and to do the same work I did in VM ?
jp.golay (BOB member since 2002-06-17)
It is not possible to open the universe in java. The designer SDK is a COM model and the webi SDK is an API model. But I went a little further to get the classes and hierarchies from the universe and was able to do that. There however is a limitation, you can extract information about those which are visible from the infoview pane only. If you have any objects or classes hidden from the designer, you cannot get anything about them. I worked on the Designer SDK to create programs that would update the index awareness and contexts. I first had to feed them into a database table and save them there. That way if anything is lost or a mass change needs to be done, it can be done with that program.
PJ
pj820 (BOB member since 2005-10-07)
Can you access the object SQL and the list of the tables used by the object?
jp.golay (BOB member since 2002-06-17)
Unfortunately No. I was able to get its name, what class it is part of and what description it has. All that you can see about an object from the InfoView. With the designer SDK, I was able to see the object SQL.
PJ
pj820 (BOB member since 2005-10-07)
Hi there,
Can somebody please tell me whether the same set of tables (Report metadata table are present in BO 6.5). I tried with all the tables available but couldnt get any information regarding the objects used by a report
-Arpan
arpan (BOB member since 2006-03-22)
Hi,
in the BO repository of BO 6.x there is no table that would contain the report metadata - the structure of the report, objects used, …
However, you may want to try this utility written in VBA that lists the objects used in the reports.
Marek Chladny 
(BOB member since 2003-11-27)
Marek
But this is only for .rep files , is there a similar utility for Webi Documents as well ?
Kind regards
Nasir
botakhan (BOB member since 2005-08-18)
Hi Nasir,
I am sorry but I am not aware of any similar utility for Webi documents 
Marek Chladny (BOB member since 2003-11-27)
Hi PJ,
I have been reading your post and its preety interesting as to bringing details using the INFOObjects class. I was wondering if there are any timeout issues encountered during your development, Also Since the infoobjects seems to be an abstract layer of the CMS repository, where there any issues trying to turn the records to rdbms type to make column relationships. IE Universe to which reports, Groups to which users of that sort.
thanks,
antzcp04
antzcp04 (BOB member since 2006-08-04)
hi pj820,
I am fairly new to BO, but we have got the same kind of requirement in our project i.e. metadata reporting (objects used in web I reports).
My question is related to code, it is in java and it work only when we are usnig WebSphere(Java) as application server. Is this correct Please confirm?
If above is correct please let me know the way I can do for my web I reports.
Thanks In Advance
Abhishek Tandon (BOB member since 2008-05-19)
We are using Version Manager from ebiexperts and the tool give us metadata of all type of reports Deski, Webi and Crystal. Even the universes are referenced.
The most impressive are that you can see the impact of a change in a table in the report’s variables, and the universe’s objects.
whairnud 
(BOB member since 2006-04-28)