Not able to convert universe(unv to unx) for reports with prompts

[Former Member]

Hi,

I am trying to convert the universe of reports but not able to convert for reports with prompts. It gives success message but doesnt convert.

it is working for reports with no prompts. Below i am pasting the code & xml it generates for reference.

initially i am passing report id, report name and hasPrompts(Y/N) in excel file.

it will pick the code based on Y/N value of hasPrompts(Y/N).

Environment - BO 4.1 sp4

<%@page import="java.util.Calendar"%>
<%@page import="java.util.Iterator"%>
<%@page import="com.wb.utils.ExcelUtil"%>
<%@page import="org.apache.xerces.parsers.DOMParser"%>
<%// Created by Shawn Penner 2013 %>

<% // These imports are for the Apache HttpComponents %>
<%@ page import="org.apache.http.client.ResponseHandler"%>
<%@ page import="org.apache.http.client.HttpClient"%>
<%@ page import="org.apache.http.client.methods.HttpGet"%>
<%@ page import="org.apache.http.client.methods.HttpPost"%>
<%@ page import="org.apache.http.impl.client.BasicResponseHandler"%>
<%@ page import="org.apache.http.impl.client.DefaultHttpClient"%>
<%@ page import="org.apache.http.entity.StringEntity"%>
<%@ page import="org.apache.http.client.HttpResponseException"%>
<%@ page import="org.apache.http.HttpResponse"%>
<%@ page import="org.apache.http.HttpEntity"%>
<%@ page import="org.apache.http.client.methods.HttpPut"%>
<%@ page import="org.apache.http.util.EntityUtils"%>

<% // 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"%>

<% // Imports for URL Encoding %>
<%@ page import="org.apache.http.client.entity.UrlEncodedFormEntity"%>
<%@ page import="org.apache.http.client.utils.URLEncodedUtils"%>
<%@ page import="org.apache.http.message.BasicNameValuePair"%>
<%@ page import="org.apache.http.NameValuePair"%>

<% // Fiddler Trace Params %>
<%@ page import="org.apache.http.HttpHost"%>
<%@ page import="org.apache.http.conn.params.*"%>

<% // Generic Java Imports %>
<%@ page import="java.io.*"%>
<%@ page import="java.util.List"%>
<%@ page import="java.util.ArrayList"%>
<div align="left" style="background: white; height: 80px">
<img src="images/images.jpg" height="70" width="150"></img>
<div align="right" style="margin-top: -60px; margin-right: 20px">
  <a href="home.jsp" style="color: black" align="right">Home</a>
</div>
</div>

<img src="images/line.jpg" height="50" width="1300"
style="margin-top: -20px"></img>
<%
// Enterprise Authentication Credentials

String boUsername = "<username>

";
String boPassword = "<password>";
String boAuthType = "secEnterprise";

// Enable Fiddler Trace.  This causes the requests to go through a proxy on port 8888 which fiddler listens on
boolean enableFiddler = false;

// Sample Variables
String reportID = "";
String sourceUniverseID = ""; // Only change dataproviders based off of this universe
String destUniverseID = "";  // Change those dataproviders to use this Universe

// Restful URL's
final String baseURL = "http://<servername>:6405/biprws";
final String logonURL = baseURL + "/logon/long";
final String logoffURL = baseURL + "/logoff";
final String baseWebiURL = baseURL + "/raylight/v1/documents";
String logonToken = "";
try {
String xmlString = "";
String documentID = "";

ArrayList<List<String>> al = null;
String fileName = request.getParameter("path");
String sourceID = request.getParameter("source");
String destID = request.getParameter("dest");
String prompts = request.getParameter("prompts");

// -----------------------
// Logon to Enterprise
// -----------------------

//out.println("Start Time--->" + Calendar.getInstance().getTime());
// First check to see if we have a logonToken stored in session.  If so, use it.
if (session.getAttribute("logonToken") != null) {
  out.println("LogonToken found </br>");
  logonToken = (String)session.getAttribute("logonToken");
} else {
  // No logontoken detected - so create one
  out.println("No LogonToken found - Creating one </br>");
  xmlString = "<attrs><attr name=\"userName\" type=\"string\" >" + boUsername + "</attr><attr name=\"password\" type=\"string\" >" + boPassword + "</attr><attr name=\"auth\" type=\"string\" possibilities=\"secEnterprise,secLDAP,secWinAD,secSAPR3\">" + boAuthType + "</attr></attrs>";
  String logonXML = restPost(enableFiddler, logonURL, xmlString, "","","","");
  System.out.println("LogonXML" + logonXML);
  // The quotes are added because the webi URL require quotes around the token
  logonToken = "\"" + getLogonTokenFromXML(logonXML) + "\"";
  // Now that we have a logonToken - it must be included in the header for a future RestFul calls
  session.setAttribute("logonToken", logonToken);
}

sourceUniverseID = sourceID;
destUniverseID = destID;  

al = ExcelUtil.readExcelForReportIDs(fileName);
if( !(al.isEmpty()) || al != null || al.size() > 0) {
Iterator<List<String>> it = al.listIterator();
while(it.hasNext()){
List<String> alReport = it.next();
if(!(alReport.isEmpty()) || alReport != null){
reportID = alReport.get(0);

// ---------------------------------------------------
// Retrieve the dataprovider collection for the report
// ---------------------------------------------------

out.println("<b>Converting report " + reportID + "(" + alReport.get(1) + ") for a dataprovider based off of universe with ID " + sourceUniverseID + "</b></br>");
// First retrieve the webi report dataproviders so we can iterate through them
xmlString = restGet(enableFiddler, baseWebiURL + "/" + reportID + "/dataproviders", "X-SAP-LogonToken",logonToken, "","");

System.out.println("Data Providers xmlString -->" +xmlString );

// Now iterate through the result
Document myDoc = convertStringToDom(xmlString);

// Now Loop through all the dataprovider nodes that were returned
// It is assumed that each dataprovider node will only have one ID tag
NodeList dpNodes = myDoc.getElementsByTagName("id");

for (int j = 0; j < dpNodes.getLength(); j++) {
  Element dpElement = (Element) dpNodes.item(j);
 
  // Get the string value
  String nodeID = (String)dpElement.getTextContent();
  System.out.println("nodeID" + nodeID);
 
// out.println("Looking at DataProvider: " + nodeID + "</br>");
// ------------------------------------------------------
// Verify what universe that dataprovider is based off of
// ------------------------------------------------------

  // Now we need to check what universe this dataprovider is based off of.  To do that, we need to do a second query
  String xmlString2 = restGet(enableFiddler, baseWebiURL + "/" + reportID + "/dataproviders/" + nodeID, "X-SAP-LogonToken",logonToken, "","");
  System.out.println("Getting Data Providers Universe-->" + xmlString2);
  // Now iterate through the result
  Document myDoc2 = convertStringToDom(xmlString2);

  // There should only be 1 result with the tag dataSourceID
  NodeList UnivNodes = myDoc2.getElementsByTagName("dataSourceId");
  System.out.println("Universe Node" + UnivNodes);
  Element univNode = (Element)UnivNodes.item(0);
 
  // Retrieve the value of the node
  String univValue = (String)univNode.getTextContent();
 
//  out.println("Universe for " + nodeID + " is " + univValue + "</br>");
  // Now compare the universe value to verify if we should change this dataprovider
  if (sourceUniverseID.equals(univValue)) {
 
  if(alReport.get(2).equalsIgnoreCase("N")){
  //out.println("Source Universe to look for is: " + sourceUniverseID + " - match found - changing universe </br>");
 
  // Confirmed - now change universe

// ------------------------------------------------------
// Get the default mappings for changing to a different universe
// ------------------------------------------------------

  // First get the default mappings
  String changeUnivUrl = baseWebiURL + "/" + reportID + "/dataproviders/mappings?originDataproviderIds=" + nodeID +"&targetDatasourceId=" + destUniverseID;
  System.out.println("Change Universe URL" + changeUnivUrl);
String xmlString3 = restGet(enableFiddler, changeUnivUrl, "X-SAP-LogonToken",logonToken, "","");
System.out.println("Change Universe URL XML string" + xmlString3);

// -----------------
// Commit Changes
// -----------------

  // Now do the actual change
  String xmlString4 = restPost(enableFiddler, changeUnivUrl, xmlString3, "X-SAP-LogonToken",logonToken,"","");
  System.out.println("xmlString4"+xmlString4);
  //out.println("Result of call: " + xmlString4 + "</br>");
  // There is no need to save the report after making this call - it is automatically saved.
  out.println("<table border = '1' width='800px'><tr><td>Report:</td><td>"+ alReport.get(1) +"</td></tr>" +
    "<tr><td>Report ID:</td><td>"+reportID+"</td></tr>" +
    "<tr><td>Description:</td><td>"+xmlString4+"</td></tr></table><table><tr><td></td></tr></table>");
  }
 
  else{
    // out.println("Source Universe to look for is: " + sourceUniverseID + " - match found - changing universe </br>");
  // Confirmed - now change universe
    // ------------------------------------------------------
  // Get the default mappings for changing to a different universe
  // ------------------------------------------------------

  //Get the parameters

  String getParamsXML =  baseWebiURL + "/" + reportID + "/parameters";
    System.out.println("get paramater url" + getParamsXML);
  String paramXML = restGet(enableFiddler, getParamsXML, "X-SAP-LogonToken",logonToken, "","");
  System.out.println("Parameters-->" +paramXML + "</br>");

    // First get the default mappings
    String changeUnivUrl = baseWebiURL + "/" + reportID + "/dataproviders/mappings?originDataproviderIds=" + nodeID +"&targetDatasourceId=" + destUniverseID;
    System.out.println("changeUnivURl" + changeUnivUrl); 
    String xmlString3 = restGet(enableFiddler, changeUnivUrl, "X-SAP-LogonToken",logonToken, "","");
    System.out.println("Change Universe Url xmlString3" + xmlString3);
   
    //xmlString3 = restGet(enableFiddler, changeUnivUrl, "X-SAP-LogonToken",logonToken, "",""); //added by rajat
  // out.println("Default Mappings-->" + xmlString3 + "</br>" );
 
  // -----------------
  // Commit Changes
  // -----------------
  //Declare Mapping that includes params
  String xmlString6 = restPost(enableFiddler, changeUnivUrl, xmlString3, "X-SAP-LogonToken",logonToken,"","");
 
    System.out.println("Mapping for those include Params Result of Post: " + xmlString6 + "</br>");
 
    //String paramUrl = baseWebiURL + "/" + reportID + "/parameters"; //added by rajat
    //String refreshUrl = restPut(enableFiddler, paramUrl, xmlString6 , "X-SAP-LogonToken",logonToken, "",""); //added by rajat
    //System.out.println("refresh report URL" + refreshUrl); //added by rajat
   
    // Now do the actual change
    String xmlString4 = restPost(enableFiddler, changeUnivUrl, xmlString6, "X-SAP-LogonToken",logonToken,"","");
    System.out.println("Actual Changes: result of Last Post: " + xmlString4 + "</br>");
   
      out.println("<table border = '1' width='800px'><tr><td>Report:</td><td>"+ alReport.get(1) +"</td></tr>" +
    "<tr><td>Report ID:</td><td>"+reportID+"</td></tr>" +
    "<tr><td>Description:</td><td>"+xmlString4+"</td></tr></table><table><tr><td></td></tr></table>");
    // There is no need to save the report after making this call - it is automatically saved.
   
  }
 
 
 
  //Code added to save it as a refreshed report- Gagan
  //Get the parameters
  String paramUrl = baseWebiURL + "/" + reportID + "/parameters";
  System.out.println("refresh report param URL" + paramUrl);
  String xmlString6 = restGet(enableFiddler, paramUrl, "X-SAP-LogonToken",logonToken, "","");
  System.out.println("refreshed report xmlString6" + xmlString6);

  //Refresh the parameters
  String refreshUrl = restPut(enableFiddler, paramUrl, xmlString6 , "X-SAP-LogonToken",logonToken, "","");
  System.out.println("refresh report URL" + refreshUrl);
  //Save the report

  String commitUrl = baseWebiURL + "/" + reportID;
  System.out.println("commitUrl" + commitUrl);
  String xmlString5 = restPut(enableFiddler, commitUrl,"", "X-SAP-LogonToken",logonToken, "","");
 
  System.out.println("Result of Commit call: " + xmlString5 + "</br>");
  //Code end for refresh - Gagan

 
  }
}

}

}

}

//out.println("End Time--->" + Calendar.getInstance().getTime());

} catch (IOException eIO) {
    out.println("IO Exception: " + eIO);
} catch (Exception ex) {
out.println("Exception: " + ex);
}
//Now log off
String xmlString = restPost(enableFiddler, logoffURL, "", "X-SAP-LogonToken",logonToken,"","");
session.removeAttribute("logonToken");
out.println("Logged off - done");
%>

<%!
// This is for debugging purposes.  The xmp tag tells the browser you want to see the XML on screen
public void printXML(JspWriter out, String msg) throws Exception {
out.println("<xmp>" + msg.replaceAll("><", "></xmp><xmp><") + "</xmp>");
}

public Document convertStringToDom(String domXMLSTring) throws Exception {
DOMParser parser = new DOMParser();
parser.parse(new InputSource(new java.io.StringReader(domXMLSTring)));
return (parser.getDocument());
}

public String convertDomToString(Document doc) throws Exception {
// Now convert the document back to a string
TransformerFactory tf = TransformerFactory.newInstance();
Transformer transformer = tf.newTransformer();
transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "no");
StringWriter writer = new StringWriter();
transformer.transform(new DOMSource(doc), new StreamResult(writer));
String output = writer.getBuffer().toString().replaceAll("\n|\r", "");
return(output);
}

public String buildEncodedURLString(String basePage, String url, String urlTitle) throws Exception {
String returnURL = "";

if (url.equals("")) {
  returnURL = urlTitle;
} else {
  List< NameValuePair > qparams = new ArrayList< NameValuePair >();
  qparams.add( new BasicNameValuePair( "queryURL", url ) );
  returnURL = "<a href=\"" + basePage + "?" + URLEncodedUtils.format(qparams, "UTF-8") + "\">" + urlTitle + "</a>";
}
return(returnURL);
}

public String getLogonTokenFromXML(String xmlString) throws Exception {
Document doc = convertStringToDom(xmlString);
NodeList nodes = doc.getElementsByTagName("attr");
for (int i = 0; i < nodes.getLength(); i++) {
  Element element = (Element) nodes.item(i);
 
  // Is this the correct XML token
  if (element.getAttribute("name").equals("logonToken")) {
  return(element.getTextContent());
  }
}
return("");
}

public static String restGet(Boolean enableFiddler, String urlStr, String param1Name, String param1Value, String param2Name, String param2Value ) throws Exception {
HttpClient httpclient = new DefaultHttpClient();

System.out.println("enableFiddler" + enableFiddler);
System.out.println("urlStr" + urlStr);
System.out.println("param1Name" + param1Name);
System.out.println("param1Value" + param1Value);
System.out.println("param2Name" +  param2Name);
System.out.println("param2Value" +  param2Value);
try {
  if (enableFiddler) {
  HttpHost proxy = new HttpHost("127.0.0.1", 8888, "http");
  httpclient.getParams().setParameter(ConnRoutePNames.DEFAULT_PROXY, proxy);
  }
  HttpGet httpget = new HttpGet(urlStr);
 
  httpget.addHeader("Accept", "application/xml");
  httpget.addHeader("Content-Type", "application/xml");
 
  if (!param1Name.equals("")) {
  httpget.addHeader(param1Name, param1Value);
  }
  if (!param2Name.equals("")) {
  httpget.addHeader(param2Name, param2Value);
  }
 
  HttpResponse response = httpclient.execute(httpget);
  System.out.println("httpget" + httpget);
  System.out.println("Response :" + response);
  int statusCode = response.getStatusLine().getStatusCode();
  HttpEntity entity = response.getEntity();
  String responseBody = (String)EntityUtils.toString(entity);
  return(responseBody);
}
catch(Exception e)
{
e.printStackTrace();
return "error";

}

finally {
  // When HttpClient instance is no longer needed,
        // shut down the connection manager to ensure
        // immediate deallocation of all system resources
        httpclient.getConnectionManager().shutdown();
}
}

// Allow for two parameters to be passed to the post request along with the XML string.
// The most common one will be the "X-SAP-LogonToken" parameter
public static String restPost(Boolean enableFiddler, String urlStr, String XMLString, String param1Name, String param1Value, String param2Name, String param2Value ) throws Exception {

System.out.println("enableFiddler" + enableFiddler);
System.out.println("urlStr" + urlStr);
System.out.println("XMLString" + XMLString);
System.out.println("param1Name" + param1Name);
System.out.println("param1Value" + param1Value);
System.out.println("param2Name" +  param2Name);
System.out.println("param2Value" +  param2Value);
HttpClient httpclient = new DefaultHttpClient();
try {
 
  if (enableFiddler) {
  HttpHost proxy = new HttpHost("127.0.0.1", 8888, "http");
  httpclient.getParams().setParameter(ConnRoutePNames.DEFAULT_PROXY, proxy);
  }

  HttpPost httpPost = new HttpPost(urlStr);
  httpPost.addHeader("Accept", "application/xml");
  httpPost.addHeader("Content-Type", "application/xml");
 
  if (!param1Name.equals("")) {
  httpPost.addHeader(param1Name, param1Value);
  }
  if (!param2Name.equals("")) {
  httpPost.addHeader(param2Name, param2Value);
  }
 
  httpPost.setEntity(new StringEntity(XMLString));
  System.out.println("XML String: "+ XMLString);
  System.out.println("http Post: "+ httpPost);
  HttpResponse response = httpclient.execute(httpPost);
  System.out.println("Response: "+ response);
  int statusCode = response.getStatusLine().getStatusCode();
  HttpEntity entity = response.getEntity();
  String responseBody = (String)EntityUtils.toString(entity);
  return(responseBody);
}

catch(Exception e)
{
e.printStackTrace();
return "error";

}

finally {
  // When HttpClient instance is no longer needed,
        // shut down the connection manager to ensure
        // immediate deallocation of all system resources
        httpclient.getConnectionManager().shutdown();
}
}

// Allow for two parameters to be passed to the post request along with the XML string.
// The most common one will be the "X-SAP-LogonToken" parameter
public static String restPut(Boolean enableFiddler, String urlStr, String XMLString, String param1Name, String param1Value, String param2Name, String param2Value ) throws Exception {

HttpClient httpclient = new DefaultHttpClient();
try {
 
  if (enableFiddler) {
  HttpHost proxy = new HttpHost("127.0.0.1", 8888, "http");
  httpclient.getParams().setParameter(ConnRoutePNames.DEFAULT_PROXY, proxy);
  }

  HttpPut httpPut = new HttpPut(urlStr);
  httpPut.addHeader("Accept", "application/xml");
  httpPut.addHeader("Content-Type", "application/xml");
 
  if (!param1Name.equals("")) {
  httpPut.addHeader(param1Name, param1Value);
  }
  if (!param2Name.equals("")) {
  httpPut.addHeader(param2Name, param2Value);
  }
 
  httpPut.setEntity(new StringEntity(XMLString));
 
  HttpResponse response = httpclient.execute(httpPut);
  int statusCode = response.getStatusLine().getStatusCode();
  HttpEntity entity = response.getEntity();
  String responseBody = (String)EntityUtils.toString(entity);
  return(responseBody);
} finally {
  // When HttpClient instance is no longer needed,
        // shut down the connection manager to ensure
        // immediate deallocation of all system resources
        httpclient.getConnectionManager().shutdown();
}
}
%>

i am attaching the xml it generates as

word documents.

UnvtoUnxXmlString.txt.zip

[former_member197386 · Accepted solution]

Hello,

If the document have prompts, you have to provide prompt answers in addition to mappings when you performed the change source.

Regards,

Anthony

[Former Member]

Hi Anthony,

Could you please tell me how to provide prompt answers?

do we need to pass it through xml?

Thanks and Regards,

Rajat Yadav

[former_member197386]

Hello Rajat,

As this thread in marked as "Answered", could you please open a new thread if you have another question about change source?

Regards,

Anthony

[Former Member]

Sure. I will post new discussion.