microstrategy return xml Archives

In the previous MicroStrategy SDK post I showed you how to setup your Eclipse Java development environment, create a new Dynamic Web Project and add the MicroStrategy JAR files that are required to communicate with the MicroStrategy Intelligence Server.

In this post I will show you how to use the MicroStrategy URL API to execute a report, retrieve the results as XML and display the results to the user from a custom Java web application. So let’s get started.

Step 1 – Create a new Dynamic Web Project by following the instructions from step 6 in the previous post.

Step 2 – Project Setup: Create a new package, servlet class, helper class & default JSP page

a. Expand the newly created project in the Project Explorer then expand Java Resources and right click the src folder and choose New -> Package. Give the package a name like com.mstrcustom.pkg and click Finish. A package is simply like a container.

b. Right click the newly created package and choose New -> Servlet. Enter a name for the new servlet class like MSServlet in the field labeled Class name and click Next and Next again on the following screen. On the next screen make sure doGet and doPost methods are checked, then click Finish. The result will be an empty servlet class that should look similar to the below screenshot.

c. Right click the newly created package and choose New -> Class. Enter a name for the class such as MSHelper and click Finish. This class will contain all of the helper methods we will create to connect to MicroStrategy, execute reports and return the results as XML.

d. Right click the WebContent folder and choose New -> JSP File. Name this file index.jsp. Note: There is a reason we are choosing this file name. In a nutshell, if a user navigates to our site but does not specify a specific page in the URL. For example, if a user enters www.AnySite.com, the server knows to look for default pages with a certain name like index.jsp and will display this page by default. This way the user does not have to enter the full URL www.AnySite.com/index.jsp. These default page names are contained in the WEB-INF/web.xml file and this is how the server knows to use this page by default if the user does not specify a particular page.

Step 3 – Add code to the helper class.

Add the following import statements at the top of the helper class:

import com.microstrategy.web.objects.*;
import java.io.*;
import java.net.*;
import javax.xml.parsers.*;
import javax.xml.transform.*;
import org.xml.sax.InputSource;
import org.w3c.dom.*;
import org.w3c.dom.CharacterData;

2. Add the following code to your MSHelper class so it looks similar to below. In this step we have simply added some string properties and a default constructor method that will set the default MicroStrategy connection properties when the class is instantiated.

public class MSHelper {
String serverName;
String projectName;
String userName;
String userPassword;
String baseURL;
String sessionState;
String styleName;
String currentReportURL;
 
public MSHelper()
{
// Default MicroStrategy Connection Info
serverName = "";
projectName = "";
userName = "";
userPassword = "";
baseURL = "http:///MicroStrategy/asp/taskAdmin.aspx?"; // ASP Version of Mstr Web
styleName = "CustomXMLReportStyle"; // This is a Mstr plugin
 
}
}

3. Add the below method GetContentXML to the MSHelper class. The function accepts the final API URL as a parameter and will return an XML document object.

private Document GetContentXML(String sUrl1) throws Exception
{
URL uURL;
StringBuffer sResult;
sResult = new StringBuffer("");
 
// Get XML report results from MicroStrategy
try {
uURL = new URL(sUrl1);
BufferedReader oReader = new BufferedReader(new InputStreamReader(uURL.openStream()));
String sInputLine = null;
 
while((sInputLine = oReader.readLine()) != null)
{
// Skip task response start/end tags
if(!sInputLine.contains("taskResponse"))
{
System.out.println("XML Line: " + sInputLine); // DEBUG
sResult.append(sInputLine);
}
}
 
oReader.close();
} catch (MalformedURLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
 
// Process XML document
String strXML = sResult.toString();
Document returnDoc = null;
try {
DocumentBuilderFactory dbf =
DocumentBuilderFactory.newInstance();
DocumentBuilder db = dbf.newDocumentBuilder();
InputSource is = new InputSource();
is.setCharacterStream(new StringReader(strXML));
System.out.println("Got char stream (is)"); // DEBUG
System.out.println("strXML: " + strXML); // DEBUG
 
Document doc = db.parse(is);
System.out.println("Got parsed document (doc)"); // DEBUG
 
NodeList nodes = doc.getElementsByTagName("mstr-report");
System.out.println("Got mstr-report elements"); // DEBUG
 
// Iterate the report
for (int i = 0; i &lt; nodes.getLength(); i++) {
Element element = (Element) nodes.item(i);
 
NodeList name = element.getElementsByTagName("report-name");
Element line = (Element) name.item(0);
System.out.println("Report Name: " + getCharacterDataFromElement(line));
 
NodeList title = element.getElementsByTagName("report-id");
line = (Element) title.item(0);
System.out.println("Report ID: " + getCharacterDataFromElement(line));
}
returnDoc = doc;
}
catch(Exception e){
e.printStackTrace();
}
return returnDoc;
}

4. Create the below function getCharacterDataFromElement. This function accepts an XML element as a parameter and returns the tags contents as a string. This is basically just a helper method.

private static String getCharacterDataFromElement(Element e) {
Node child = e.getFirstChild();
if (child instanceof CharacterData) {
CharacterData cd = (CharacterData) child;
return cd.getData();
}
return "?";
}

5. Create the below method GetReportXMLDocument. This is the main function that will be called by our servlet to execute the report and return an XML document object. The method takes the MicroStrategy report id as a parameter.

public Document GetReportXMLDocument(String reportID) throws TransformerException
{
// Construct report URL
String mstrURL = this.baseURL;
mstrURL += "taskId=reportDataService&amp;taskEnv=xml&amp;taskContentType=xml";
mstrURL += "&amp;server=" + this.serverName;
mstrURL += "&amp;project=" + this.projectName.replace(" ", "+");
mstrURL += "&amp;userid=" + this.userName;
mstrURL += "&amp;password=" + this.userPassword;
mstrURL += "&amp;styleName=" + this.styleName;
mstrURL += "&amp;reportID=" + reportID;
 
// Save report URL to property
this.currentReportURL = mstrURL;
 
// Execute report and get results as XML document
Document reportXMLDoc = null;
try {
// Get URL as string
URL uMstrURL = new URL(mstrURL);
String sMstrURL = uMstrURL.toString();
 
// Get XML document
reportXMLDoc = this.GetContentXML(sMstrURL.toString());
} catch (MalformedURLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
 
return reportXMLDoc;
}

Step 4 – Add code to the servlet class

Add the following import statements to the top of the new servlet class.

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import org.w3c.dom.*;
import org.w3c.dom.CharacterData;

2. Add the same function getCharacterDataFromElement.

private static String getCharacterDataFromElement(Element e) {
Node child = e.getFirstChild();
if (child instanceof CharacterData) {
CharacterData cd = (CharacterData) child;
return cd.getData();
}
return "?";
}

3. Add the below function outputReportHTML. This function will accept the XML document object as a parameter and display the results in the browser as an HTML table. (I apologize the below code is an image and cannot be cut and paste but the HTML tags in the code were causing issues with the post.)
outputReportHTML Function

4. Add the below code to the servlet’s doGet method. This is the method that will be triggered by the users browser. The function uses our helper class to retrieve the report XML and then parses the XML output and displays in the browser as HTML output.

Step 6 – Add HTML code to the index.jsp page

Finally, modify the file index.jsp like the below screenshot. All we are basically adding is a header and a form with an input box for the report id and a submit button to run the report. Make sure the action attribute in the form tag contains the name of the servlet class you created in step 2. I also linked to a simple style sheet in my example below but you do not need to do this, so you can omit the link tag in the head section from your jsp page.

Step 7 – Install the MicroStrategy Custom XML plugin

a. Install the plugin located in your MicroStrategy install directory at

C:\Program Files\MicroStrategy\SDK\CustomizationPlugins\AdvancedScenarios\RetrievingReportXMLCustomFormat

b. To install this plugin extract the contents to the “Web ASPx\plugins” folder of your MicroStrategy web install which by default is located at C:\Program Files (x86)\MicroStrategy\Web ASPx\plugins

c. You may need to restart your web server for the changes to take effect. This plugin will enable us to retrieve the report results as XML.

Step 8 – Run the project

a. Run the project by right clicking the root project folder and choosing Run As -> Run on Server.
b. You may have to choose your local Tomcat v7 server as in the below screenshot but it should be selected by default. Now click Finish.

c. If the application asks you to reset your Tomcat server choose Yes.
d. Next the application should build and eventually display the contents of our index.jsp page similar to below.

e. Next enter the report id of a MicroStrategy report and click Run Report. (Note: Make sure the report id is for a grid report and not a document/dashboard) Finally, you should see results similar to below. Congratulations you have just integrated the MicroStrategy SDK into a custom Java web application!

Tag: microstrategy return xml

MicroStrategy SDK Part 2 – How to execute a report and return the results as XML using URL API