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.
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.*"%> <TABLE id="htmlTable" BORDER="1" cellpadding="0" cellspacing="0" width="100%" class="table" align="center"> <tr class="table_header"> <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>"); %> <table BORDER="1" cellpadding="0" cellspacing="0" width="100%" class="table" align="center"> <tr class="table_header"> <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>"); %> <table BORDER="1" cellpadding="0" cellspacing="0" width="100%" class="table" align="center"> <tr class="table_header"> <td>DP Name</td><td>DP ID</td><td>DS Name</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>"); %> <table BORDER="1" cellpadding="0" cellspacing="0" width="100%" class="table" align="center"> <tr class="table_header"> <td>DP Name</td><td>DP ID</td><td>DS Name</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> |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
5 | |
5 | |
4 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 |