Using Webi 4.1 SDK to get Report SQL

When SAP introduced BusinessObjects 4.0, clients who were invested in the Webi REBEAN SDK got worried. The Webi SDK had been mostly deprecated and it was not clear what is going to be the direction for those seeking programmatic ways to interact with webi reports. Well, in 4.1, the Webi SDK has made a big come back, and with a modern, web friendly architecture. Webi reports can now be modified, explored and edited programmatically via RESTFUL calls. In a prior post, I wrote about the ability to logon to the BO enterprise platform via REST web services, and building on this example, I took the 4.1 SP2 SDK for a spin. The use case I wanted to address was the extraction of the SQL query from a webi report. This is a use case I run into often, especially when working on dashboards projects where database developers who have no idea (or interest) in webi are looking to get their hands on the SQL queries being used to generate dashboard screens (via BI services). To accommodate this, I was looking for a way to expose the query from the webi data provider programmatically, via a simple hyperlink in the dashboard.

In this example, the service is implemented as a .jsp file on the BO tomcat server. As explained in the first post, we have to work around the domain-of-origin issue, and a java proxy seemed to be the way to go here, though other approaches are feasible.

The actual code to retrieve the sql query of the webi report is below:

<%@include file="getlogin.jsp" %>
The getlogin.jsp file basically uses the logon code from the first post and saves the generated logon token to the session. That way, the logon token can be used for subsequent operations easily…
<%@ page import="
org.apache.http.HttpEntity,
org.apache.http.HttpResponse,
org.apache.http.client.methods.HttpPost,
org.apache.http.client.methods.HttpGet,
org.apache.http.impl.client.DefaultHttpClient,
org.apache.http.util.EntityUtils,
org.apache.http.entity.StringEntity,
org.apache.http.client.HttpClient,
org.apache.http.protocol.BasicHttpContext,
org.apache.http.HttpStatus,
java.io.*,
java.net.*,
org.apache.http.Header,
org.apache.http.impl.client.BasicResponseHandler,
org.json.simple.*,
org.json.simple.parser.*"
%>
<%@page contentType="text/json"%>
<%@page trimDirectiveWhitespaces="true" %>
<%
String reportId = request.getParameter("id");// allow passing a report id as a query string from the dashboard
if (reportId==null) reportId = "7749"; //catchall in case no id is passed
StringBuffer sbf = new StringBuffer();
 HttpGet httpRequest = new HttpGet("http://localhost:6405/biprws/raylight/v1/documents/"+reportId+"/dataproviders/DP0");//in this example, there is one data provider for the report, if there are multiple, you will need to explore them and find their names in order to query each one separately
 httpRequest.setHeader("Content-Type","application/json");
 httpRequest.setHeader("Accept","application/json");
 String logonToken = "";
 logonToken = (String) session.getAttribute("logonToken");
 httpRequest.setHeader("X-SAP-LogonToken",logonToken);
HttpClient httpclient = new DefaultHttpClient();
 HttpResponse httpResponse = httpclient.execute(httpRequest);
if (httpResponse.getStatusLine().getStatusCode() == HttpStatus.SC_OK && httpResponse.getEntity() != null) {
 HttpEntity ent = httpResponse.getEntity();
 BufferedReader in = new BufferedReader(new InputStreamReader(ent.getContent()));
 String inputLine;
 while ( (inputLine = in.readLine()) != null) sbf.append(inputLine);
 in.close();
 EntityUtils.consume(ent);
 } else {
 out.println("error: "+ httpResponse.getStatusLine().getStatusCode());
 Header[] headers = httpResponse.getAllHeaders();
 for (Header header : headers) {
 out.println("Key : " + header.getName() 
 + " ,Value : " + header.getValue());
 }
 HttpEntity entity = httpResponse.getEntity();
 String responseString = EntityUtils.toString(entity, "UTF-8");
 out.println(responseString);
}
 %>
<%
String jsonStr = sbf.toString();
Object obj=JSONValue.parse(jsonStr);
JSONObject array = (JSONObject)obj;
JSONObject obj2=(JSONObject)array.get("dataprovider");
out.println(obj2.get("query")); 
%>