on 09-11-2015 2:02 PM
Hi Experts
We have the following environment
BOXI 3.1
Tomcat
5000 Plus reports .
We want to retrieve SQL Of all the webi reports at one go
Do we have an SDK for this or any method through which this can be done
Any suggestions\ views highly appreciated
Refer to http://service.sap.com/sap/support/notes/1312043
and http://service.sap.com/sap/support/notes/1461974
Thanks,
Prithvi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You can change the query to
select si_id,si_name from ci_infoobjects where si_kind='webi' and si_instance =0
The above query would retrieve all the webi reports in your environment.
However, running the code for all the reports at once can have impact on the webi servers. Its best to split your query in batches. Or you can run the code by querying by report folders
something like
select si_id,si_name from ci_infoobjects where si_kind='webi' and si_parentid=<id of report folder>
Getting the Below error
Unable to compile class for JSP: An error occurred at line: 15 in the generated java file Only a type can be imported. com.businessobjects.sdk.plugin.desktop.webi.IWebi resolves to a package An error occurred at line: 16 in the generated java file Only a type can be imported. com.businessobjects.rebean.wi.ReportEngine resolves to a package An error occurred at line: 17 in the generated java file Only a type can be imported. com.businessobjects.rebean.wi.DocumentInstance resolves to a package An error occurred at line: 18 in the generated java file Only a type can be imported. com.businessobjects.rebean.wi.DataProvider resolves to a package An error occurred at line: 19 in the generated java file Only a type can be imported. com.businessobjects.rebean.wi.DataSource resolves to a package An error occurred at line: 27 in the jsp file: /SQL.jsp ReportEngine cannot be resolved to a type 24: 25: ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr(); 26: IEnterpriseSession enterpriseSession = sessionMgr.logon(username, password, cms, auth); 27: ReportEngine reportEngine=null; 28: reportEngine = (ReportEngine)enterpriseSession.getService("", "WebiReportEngine"); 29: //Grab the InfoStore from the httpsession 30: IInfoStore infoStore = (IInfoStore)enterpriseSession.getService("", "InfoStore"); An error occurred at line: 28 in the jsp file: /SQL.jsp ReportEngine cannot be resolved to a type 25: ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr(); 26: IEnterpriseSession enterpriseSession = sessionMgr.logon(username, password, cms, auth); 27: ReportEngine reportEngine=null; 28: reportEngine = (ReportEngine)enterpriseSession.getService("", "WebiReportEngine"); 29: //Grab the InfoStore from the httpsession 30: IInfoStore infoStore = (IInfoStore)enterpriseSession.getService("", "InfoStore"); 31: An error occurred at line: 35 in the jsp file: /SQL.jsp IWebi cannot be resolved to a type 32: //Query for the report object in the CMS. See the Developer Reference guide for more information the query language. 33: IInfoObjects oInfoObjects = (IInfoObjects)infoStore.query("SELECT TOP 1 * FROM CI_INFOOBJECTS WHERE SI_INSTANCE=0 AND SI_NAME='" + reportName + "'" ); 34: if (oInfoObjects.size() > 0) { 35: IWebi webiDoc = (IWebi)oInfoObjects.get(0); 36: reportID = webiDoc.getID(); 37: 38: //Create new document instance. An error occurred at line: 35 in the jsp file: /SQL.jsp IWebi cannot be resolved to a type 32: //Query for the report object in the CMS. See the Developer Reference guide for more information the query language. 33: IInfoObjects oInfoObjects = (IInfoObjects)infoStore.query("SELECT TOP 1 * FROM CI_INFOOBJECTS WHERE SI_INSTANCE=0 AND SI_NAME='" + reportName + "'" ); 34: if (oInfoObjects.size() > 0) { 35: IWebi webiDoc = (IWebi)oInfoObjects.get(0); 36: reportID = webiDoc.getID(); 37: 38: //Create new document instance. An error occurred at line: 39 in the jsp file: /SQL.jsp DocumentInstance cannot be resolved to a type 36: reportID = webiDoc.getID(); 37: 38: //Create new document instance. 39: DocumentInstance documentInstance = reportEngine.openDocument(reportID); 40: DataProvider dataProvider=documentInstance.getDataProviders().getItem(0); 41: 42: //get WebI SQL query string An error occurred at line: 40 in the jsp file: /SQL.jsp DataProvider cannot be resolved to a type 37: 38: //Create new document instance. 39: DocumentInstance documentInstance = reportEngine.openDocument(reportID); 40: DataProvider dataProvider=documentInstance.getDataProviders().getItem(0); 41: 42: //get WebI SQL query string 43: String sqlString=dataProvider.getQuery().getSQL(); Stacktrace:
Which Jar Files specifically should look For this code ?
I did check tomcat\webapps\dsdwbobje\web inf\lib there's lot Of Jar files there.
rebean.wi.jar is present at the above location
would request to confirm if i am looking in the appropriate location Plus which Jar files would be required for this
The jar rebean.wi.jar is correct however I am not sure whether you are checking it in the correct location.
Where have you kept your .jsp file?
Is it placed in dswsbobje webapp?
You should check the jars in the WEB-INF/lib of the application where you have placed your jsp file.
Try to place the file in AnalyticalReporting webapp and run using the below url
http://<hostname>:<port>/AnalyticalReporting/nameofjsp.jsp
Thanks,
Prithvi
Ok what i did Now is placed the .jsp file in the analytical reporting folder , Now I don't get an error but get a blank page , in one of the code the input parameters are given as report name and ID I am providing Report name and its ID but still it give Blank Page .
I am initially testing it to return 1 report first then will try for a greater number, but its giving me a blank page
Test with below code.
<%@ page import="com.crystaldecisions.sdk.framework.*" %>
<%@ page import="com.crystaldecisions.sdk.exception.SDKException" %>
<%@ page import="com.crystaldecisions.sdk.occa.infostore.*" %>
<%@ page import="com.businessobjects.rebean.wi.*" %>
<%
boolean loginSuccessful = false;
IEnterpriseSession oEnterpriseSession = null;
String username = "username";
String password = "password";
String cmsname = "cmsname";
String authenticationType = "secEnterprise";
try {
//Log in.
oEnterpriseSession = CrystalEnterprise.getSessionMgr().logon( username, password, cmsname, authenticationType);
if (oEnterpriseSession == null) {
out.print("<FONT COLOR=RED><B>Unable to login.</B></FONT>");
} else {
loginSuccessful = true;
}
} catch (SDKException sdkEx) {
out.print("<FONT COLOR=RED><B>ERROR ENCOUNTERED</B><BR>" + sdkEx + "</FONT>");
}
//----------------------------------------------------------------------------------------
if (loginSuccessful) {
IInfoObject oInfoObject = null;
String docname = "END_SQL Testin";
//Grab the InfoStore from the httpsession
IInfoStore oInfoStore = (IInfoStore) oEnterpriseSession.getService("", "InfoStore");
//Query for the report object in the CMS. See the Developer Reference guide for more information the query language.
String query = "SELECT TOP 1 * " +
"FROM CI_INFOOBJECTS " +
"WHERE SI_INSTANCE = 0 And SI_Kind = 'Webi' " +
"AND SI_NAME='" + docname + "'";
IInfoObjects oInfoObjects = (IInfoObjects) oInfoStore.query(query);
if (oInfoObjects.size() > 0) {
//Retrieve the latest instance of the report
oInfoObject = (IInfoObject) oInfoObjects.get(0);
// Initialize the Report Engine
ReportEngines oReportEngines = (ReportEngines) oEnterpriseSession.getService("ReportEngines");
ReportEngine oReportEngine = (ReportEngine) oReportEngines.getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE);
// Opening the document
DocumentInstance oDocumentInstance = oReportEngine.openDocument(oInfoObject.getID());
DataProvider oDataProvider = null;
SQLDataProvider oSQLDataProvider = null;
SQLContainer oSQLContainer_root = null;
SQLNode oSQLNode = null;
SQLSelectStatement oSQLSelectStatement = null;
String sqlStatement = null;
out.print("<TABLE BORDER=1>");
for (int i=0; i<oDocumentInstance.getDataProviders().getCount(); i++) {
oDataProvider = oDocumentInstance.getDataProviders().getItem(i);
out.print("<TR><TD COLSPAN=2 BGCOLOR=KHAKI>Data Provider Name: " + oDataProvider.getName() + "</TD></TR>");
if (oDataProvider instanceof SQLDataProvider) {
oSQLDataProvider = (SQLDataProvider) oDataProvider;
oSQLContainer_root = oSQLDataProvider.getSQLContainer();
if (oSQLContainer_root != null) {
for (int j=0; j<oSQLContainer_root.getChildCount(); j++) {
oSQLNode = (SQLNode) oSQLContainer_root.getChildAt(j);
oSQLSelectStatement = (SQLSelectStatement) oSQLNode;
sqlStatement = oSQLSelectStatement.getSQL();
out.print("<TR><TD>" + (j+1) + "</TD><TD>" + sqlStatement + "</TD></TR>");
}
}
} else {
out.print("<TR><TD COLSPAN=2>Data Provider is not a SQLDataProvider. SQL Statement can not be retrieved.</TD></TR>");
}
}
out.print("</TABLE>");
oDocumentInstance.closeDocument();
oReportEngine.close();
oReportEngines.close();
}
else
{
out.print("<TR><TD COLSPAN=2>Report with name '"+docname+"' Not found</TD></TR>");
}
oEnterpriseSession.logoff();
}
%>
that works , but still have to Put a single Webi report name as Input
"String docname = "END_SQL Testin";"
Requesting If you can update on how to go this for a Folder Or may be a full run.
Additionally found out that if the report name is greater then some Characters , It does not run and gives Page cant be found.
<html>
<body>
<%@ page import="com.crystaldecisions.sdk.framework.*,
com.crystaldecisions.sdk.occa.infostore.*,
com.businessobjects.rebean.wi.*,
java.io.*"
%>
<%
//Enter Username
String username = "username";
//Enter User password
String password = "password";
//Enter CMS Name
String cmsname = "cmsname";
String authtype = "secEnterprise";
//Enter the folder if for which you need to retrieve the webi reports objects
int report_folder_id=2750278;
IEnterpriseSession oEnterpriseSession=null;
ReportEngines engines=null;
ReportEngine widocRepEngine=null;
try
{
oEnterpriseSession = CrystalEnterprise.getSessionMgr().logon(username, password, cmsname, authtype);
engines = (ReportEngines) oEnterpriseSession.getService("ReportEngines");
widocRepEngine = (ReportEngine) engines.getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE);
getReportObjectsInFolder(oEnterpriseSession,widocRepEngine,report_folder_id,out);
}
catch(Exception e)
{
out.println(e);
}
finally
{
widocRepEngine.close();
engines.close();
oEnterpriseSession.logoff();
}
%>
</table>
</body>
</html>
<%!
private void getReportSQL(IEnterpriseSession oEnterpriseSession,ReportEngine widocRepEngine,int reportID, JspWriter out) throws Exception
{
// Opening the document
DocumentInstance oDocumentInstance = widocRepEngine.openDocument(reportID);
DataProvider oDataProvider = null;
SQLDataProvider oSQLDataProvider = null;
SQLContainer oSQLContainer_root = null;
SQLNode oSQLNode = null;
SQLSelectStatement oSQLSelectStatement = null;
String sqlStatement = null;
out.print("<TABLE BORDER=1>");
for (int i=0; i<oDocumentInstance.getDataProviders().getCount(); i++) {
oDataProvider = oDocumentInstance.getDataProviders().getItem(i);
out.print("<TR><TD COLSPAN=2 BGCOLOR=KHAKI>Data Provider Name: " + oDataProvider.getName() + "</TD></TR>");
if (oDataProvider instanceof SQLDataProvider) {
oSQLDataProvider = (SQLDataProvider) oDataProvider;
oSQLContainer_root = oSQLDataProvider.getSQLContainer();
if (oSQLContainer_root != null) {
for (int j=0; j<oSQLContainer_root.getChildCount(); j++) {
oSQLNode = (SQLNode) oSQLContainer_root.getChildAt(j);
oSQLSelectStatement = (SQLSelectStatement) oSQLNode;
sqlStatement = oSQLSelectStatement.getSQL();
out.print("<TR><TD>" + sqlStatement + "</TD></TR>");
}
}
} else {
out.print("<TR><TD COLSPAN=2>Data Provider is not a SQLDataProvider. SQL Statement can not be retrieved.</TD></TR>");
}
}
out.print("</TABLE>");
oDocumentInstance.closeDocument();
}
private void getReportObjectsInFolder(IEnterpriseSession oEnterpriseSession,ReportEngine widocRepEngine,int reportFolderID, JspWriter out) throws Exception
{
IInfoStore oInfoStore = (IInfoStore)oEnterpriseSession.getService("","InfoStore");
try
{
String query = "select si_id from ci_infoobjects where SI_PARENTID="+reportFolderID;
IInfoObjects oInfoObjects = oInfoStore.query(query);
for(int i=0;i<oInfoObjects.size();i++)
{
IInfoObject oInfoObject = (IInfoObject) oInfoObjects.get(i);
String objectKind1=oInfoObject.getKind();
if(objectKind1.equals("Folder"))
{
int folderID=oInfoObject.getID();
IInfoObjects boReportInfoObjects=oInfoStore.query("SELECT SI_ID FROM CI_INFOOBJECTS WHERE SI_PARENTID="+folderID);
for(int j=0;j<boReportInfoObjects.size();j++)
{
IInfoObject boReportInfoObject=(IInfoObject)boReportInfoObjects.get(j);
String objectKind=boReportInfoObject.getKind();
if(objectKind.equals("Folder"))
{
getReportObjectsInFolder(oEnterpriseSession,widocRepEngine,boReportInfoObject.getID(),out);
}
else if(objectKind.equals("Webi"))
{
out.println("<h3><b>"+boReportInfoObject.getTitle()+"</B></h3>");
getReportSQL(oEnterpriseSession,widocRepEngine,boReportInfoObject.getID(),out);
}
}
}
else if(objectKind1.equals("Webi"))
{
out.println("<h3><b>"+oInfoObject.getTitle()+"</B></h3>");
getReportSQL(oEnterpriseSession,widocRepEngine,oInfoObject.getID(),out);
}
}
}
catch(Exception exe)
{
out.println(exe);
}
}
%>
User | Count |
---|---|
71 | |
26 | |
10 | |
9 | |
7 | |
6 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.