cancel
Showing results for 
Search instead for 
Did you mean: 

How to retrive Sql Of Webi reports In One go

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos
Former Member
0 Kudos

Thanks Prithvi,

For the quick response however this Link ask me to sign in , I don,t have credentials for this ,

Can you copy the content here If possible

Former Member
0 Kudos

Attached is a text file with the code.

You would need to change the query if you want the code to get the sql for multiple reports. Its better to get the 5000 reports in a batches so that resources on the server are not affected.

Former Member
0 Kudos

Prithvi,

I was able to get the code.

However the code depicts that  i have to enter single report id at one time .

Looking for something that will give me information at  one go.

Apologies but  i dont have much exp in SDK , looking for some assistance

Former Member
0 Kudos

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>

Former Member
0 Kudos

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:

Former Member
0 Kudos

Check if you have the relevant report engine jars in your application's lib directory.

If you have all the jars in your lib, restart the application sever and check.

List of jars can be found from developers guide for BusinessObjects SDK's available at help.sap.com

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

I have just checked the code at my end and it works fine.

You would need to provide just the report title and not the ID. If you get a blank page execute the same query in query builder and check the results.

Former Member
0 Kudos

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();

}

%>

Former Member
0 Kudos

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.

Former Member
0 Kudos

Any suggestions On this.

Former Member
0 Kudos

You can use the attached code to get the details folder wise.

Former Member
0 Kudos

can you copy Paste ? I think the attachments do Not come Over

Former Member
0 Kudos

<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);

}

}

%>

Former Member
0 Kudos

Thanks man , I will try that

Former Member
0 Kudos

works Like Magic thanks

Just to add the query does not further Reports and Sql information If it encounter any of the report error in between for any report ( like universe Not found .. 30270 error etc )

Former Member
0 Kudos

Yes, you are right it stops once you get an exception. For that you can write an exception handler and and catch the exception as ask it to continue the execution.

Thanks,

Prithvi

Answers (0)