How to get data source details from webi document ...

Former Member · ‎05-17-2016

I am writing this blog to give detail on how the data source information can be retrieved for a webi report through the SI_WEBI_DOC_PROPERTIES using BusinessObjects Enterprise Platform Java SDKs.

Using the query builder(AdminTools Application), and executing the below query
Select si_name,si_id,si_webi_doc_properties from ci_infoobjects where si_kind='webi' and si_instance=0, we get the list of top 1000(default) webi reports for the environment we are connecting to. (Note: For getting list greater than 1000, use the sql TOP function in the query.)

For the above query, the si_webi_doc_properties consists of information related to the dataprovider id, name and the datasource name among other information. The details in the si_webi_doc_properties is formatted as XML data.

The code below is developed taking a use case of reports developed on an OLAP conection(si_kind='CommonConnection'). Using the code below you get the list of common connections and the reports based on those connections. We then get the datasource name(bex query name) for all those reports. The code can also be used to get the details for universes as well by changing the queries used in it. The output would contain the CommonConnection Name, Report ID, Report Name, Report Folder Path, DP ID, DP Name & DS Name.

Notes:

Copy XercesImpl.jar from C:\Program Files (x86)\SAP BusinessObjects\tomcat\webapps\BOE\WEB-INF\eclipse\plugins\webpath.PlatformServices\web\WEB-INF\lib to your applications lib. XercesImpl is used for the DOM XML parser. For larger XML documents - you may prefer to use a SAX implementation instead.

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

https://blogs.sap.com/2013/06/03/scripts-and-samples/

Datasource name for webi reports on OLAP connection

<html>

<head>

<style>

.table_header td {

color: white;

background-color: black;

font-size: 10pt;

}

.table_header1 td {

font-size: 10pt;

}

</style>

</head>

<body>

<%@ page import="com.crystaldecisions.sdk.framework.*"%>

<%@ page import="com.crystaldecisions.sdk.occa.infostore.*"%>

<%@ page import="com.crystaldecisions.sdk.properties.*" %>

<%@ page import="com.crystaldecisions.sdk.plugin.desktop.folder.*" %>

<%@ page import="com.businessobjects.sdk.plugin.desktop.webi.*"%>

<%@ page import="java.io.*"%>

<%

// These imports are for the XML DOM Parser

%>

<%@ page import="javax.xml.parsers.DocumentBuilderFactory"%>

<%@ page import="javax.xml.parsers.DocumentBuilder"%>

<%@ page import="org.w3c.dom.*"%>

<%@ page import="org.w3c.dom.Node.*"%>

<%@ page import="org.w3c.dom.Element"%>

<%@ page import="com.sun.org.apache.xerces.internal.parsers.*"%>

<%@ page import="org.xml.sax.InputSource"%>

<%

// These imports are for transforming the DOM Parser back into a string

%>

<%@ page import="javax.xml.transform.Transformer"%>

<%@ page import="javax.xml.transform.TransformerFactory"%>

<%@ page import="javax.xml.transform.OutputKeys"%>

<%@ page import="javax.xml.transform.dom.DOMSource"%>

<%@ page import="javax.xml.transform.stream.StreamResult"%>

<%

// Generic Java Imports

%>

<%@ page import="java.io.*"%>

<%@ page import="java.net.*"%>

<%@ page import="java.util.Enumeration"%>

<%@ page import="java.util.*"%>

<%@ page import="java.text.*"%>

<td>CommonConnection</td></tr>

<%

//Enter Username

String username = "administrator";

//Enter User password

String password = "password";

//Enter CMS Name

String cmsname = "localhost:6400";

String authtype = "secEnterprise";

IEnterpriseSession oEnterpriseSession=null;

NodeList dpNodes = null;

Node nNode=null;

Element element=null;

try

{

oEnterpriseSession = CrystalEnterprise.getSessionMgr().logon(username, password, cmsname, authtype);

IInfoStore oInfoStore = (IInfoStore)oEnterpriseSession.getService("","InfoStore");

String query = "select si_id,si_name from ci_appobjects where si_kind='CommonConnection'";

IInfoObjects oInfoObjects = oInfoStore.query(query);

for(int i=0;i<oInfoObjects.size();i++)

{

IInfoObject oInfoObject = (IInfoObject) oInfoObjects.get(i);

out.println("<tr class=\"table_header1\"><td>"+oInfoObject.getTitle()+"</td>");

String webiDocQuery="select top 1000 si_name,si_id,si_parentid,si_webi_doc_properties from ci_appobjects,ci_infoobjects where PARENTS(\"si_name='CommonConnection-Document'\",\"si_name='" + oInfoObject.getTitle() + "'" + "\")";

IInfoObjects webiDocObjects= oInfoStore.query(webiDocQuery);

out.println("<td>");

%>

<td>Report ID</td><td>Report Name</td><td>Report Path</td>

</tr>

<%

for(int a=0;a<webiDocObjects.size() ;a++)

{

IInfoObject webiDocObject=(IInfoObject)webiDocObjects.get(a);

out.println("<tr class=\"table_header1\"><td>"+ webiDocObject.getID() + "</td><td>"+ webiDocObject.getTitle() + "</td>");

IProperties prop = webiDocObject.properties();

IProperty getProp = prop.getProperty("SI_PARENTID");

String FolderID = getProp.toString();

IInfoObjects folder = oInfoStore .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;

String finalFolderPath="";

if(iifolder.getPath()!= null)

{

String path[]=iifolder.getPath();

for(int fi=0;fi<path.length;fi++)

{

finalFolderPath = path[fi] + "/" + finalFolderPath;

}

finalFolderPath = finalFolderPath + iifolder.getTitle();

}

else

{

finalFolderPath=finalFolderPath+iifolder.getTitle();

}

out.println("<td>" + finalFolderPath + "</td>");

}

else if((ifolder.getKind().equals("FavoritesFolder")))

{

out.println("<td><b>FavoritesFolder</b> :: " + ifolder.getTitle() + "</td>");

}

else if((ifolder.getKind().equals("Inbox")))

{

out.println("<td><b>Inbox</b> :: " + ifolder.getTitle() + "</td>");

}

else if((ifolder.getKind().equals("ObjectPackage")))

{

//out.println("<td><b>ObjectPackage</b> :: " + ifolder.getTitle() + "</td>");

IProperties prop1 = ifolder.properties();

IProperty getProp1 = prop1.getProperty("SI_PARENTID");

String FolderID1 = getProp1.toString();

IInfoObjects folder1 = oInfoStore.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("<td>" + finalFolderPath1 + "</td>");

}

if(prop.getProperty("SI_WEBI_DOC_PROPERTIES") != null)

{

String xmlRecords=prop.getProperty("SI_WEBI_DOC_PROPERTIES").getValue().toString();

Document myDoc = convertStringToDom(xmlRecords);

dpNodes = myDoc.getElementsByTagName("WEBI_DP");

int count=0;

out.println("<td>");

%>

<tr>

<%

for (int n = 0; n < dpNodes.getLength(); n++)

{

nNode = dpNodes.item(n);

if (nNode.getNodeType() == Node.ELEMENT_NODE) {

element=(Element)nNode;

String nodeDPName = (String) element.getAttribute("DPNAME");

String nodeDPID = (String) element.getAttribute("DPID");

String nodeDSName = (String) element.getAttribute("DSNAME");

out.println("<tr class=\"table_header1\"><td>"+nodeDPName+"</td>");

out.println("<td>"+nodeDPID+"</td>");

out.println("<td>"+nodeDSName+"</td></tr>");

}

out.println("</table></td></tr>");

}

else

{

out.println("<td>");

%>

<tr>

<%

out.println("<tr class=\"table_header1\"><td>Not Found</td>");

out.println("<td>Not Found</td>");

out.println("<td>Not Found</td></tr>");

out.println("</table></td></tr>");

}
}

out.println("</table></td></tr>");
}

}

catch(Exception e)

{

out.println(e);

}

finally

{

oEnterpriseSession.logoff();

}

%>

<%!

public Document convertStringToDom(String domXMLSTring) throws Exception {

DOMParser parser = new DOMParser();

parser.parse(new InputSource(new java.io.StringReader(domXMLSTring)));

return (parser.getDocument());

}

%>

</table>

</body>

</html>

How to get data source details from webi document properties

Notes:

SAP PI for Beginners

ABAP 7.40 Quick Reference

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