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

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.

  1. 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 < 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&taskEnv=xml&taskContentType=xml";
mstrURL += "&server=" + this.serverName;
mstrURL += "&project=" + this.projectName.replace(" ", "+");
mstrURL += "&userid=" + this.userName;
mstrURL += "&password=" + this.userPassword;
mstrURL += "&styleName=" + this.styleName;
mstrURL += "&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

  1. 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!

MicroStrategy SDK – How to setup your Eclipse Java development environment

When it comes to working with the MicroStrategy SDK there is a lot of resources out there, however, when it comes to getting started and setting up your devlopment environment, you may find that the instructions are often lacking and disjointed.

I find there are primarily two different buckets developers fall into when it comes to SDK development. First, there are those that want to modify the existing MicroStrategy web application and second, there are those that want to incorporate MicroStrategy into their own existing custom web applications. The main focus of this post is the latter.

When setting up my development environment using Eclipse I ran into several issues that had me pounding my fists against the desk. I scoured the internet only to find that the documentation was either limited or dated. It seems like I was fighting through hurdle after hurdle until finally I was able to get everything working.

I decided to create this post to help others that would like to get started with MicroStrategy SDK development.

So let’s jump right in and get started.

Step 1 – Install Java SDK 8 from here (Make sure you install JDK and not just JRE)

Step 2 – Download and extract the Eclipse Java IDE from here. I’m currently working with MicroStrategy 10.2 and use Eclipse Mars.

Step 3 – Open Eclipse and click Help->Install new software…

In the “work with” drop down choose “http://download.eclipse.org/releases/mars” and check the following packages to install:

  1. Eclipse Java Web Developer Tools
  2. JST Server Adapters
  3. JST Server Adapters Extensions
  4. Eclipse Java EE Developer Tools
  5. WST Server Adapters

Step 4 – Download and install Apache Tomcat from here (I use Tomcat version 7). We are installing Tomcat to your development environment but if you have experience developing Java web applications you may want to skip this step however, this is for beginners so I assume nothing.

After Tomcat is installed go to the install directory for example “C:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0\conf” and edit “tomcat-users.xml” file and add users and roles. See the example screenshot below.

Step 5 – Configure the Tomcat run time with Eclipse

  1. In Eclipse click Window-Preferences->Server->Runtime Environment
  2. Click Add->Click Apache Tomcat v7.0->Click Next
  3. Click Browse… and choose C:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0
  4. Choose java8 (JDK) in the JRE dropdown
  5. Click Finish then OK

Step 6 – Now for the fun part where we create a new web project and integrate with MicroStrategy SDK.

  1. In Eclipse Click File->New->Project->Web->Dynamic Web Project->Click Next
  2. Enter project name and choose runtime module version 2.5. See screenshot below for an example. (Note: Target runtime should default to Apache Tomcat v7.0)

3. Click next, then next again, then finish

4. Go to Project->Properties->Choose “Java Build Path” from left tree view

5. Click Libraries tab and click Add external jars

6. Add MicroStrategy JAR files. These files can be obtained from an existing MicroStrategy installation. In Windows they can be found in “C:\Program Files (x86)\Common Files\MicroStrategy” for example. NOTE: Make sure you ONLY add the files that are named like Web*.jar and the file JavaWebAPI.jar

7. Click Apply and OK

8. After you created the project next go to Project->Properties->Deployment Assembly and add the MicroStrategy jar files. NOTE: THIS IS REQUIRED OR THE DEPLOYED APP WILL FAIL TO FIND THE MICROSTRATEGY JAR FILES AND THE APP WILL NOT WORK!

Congratulations! You now have a MicroStrategy development environment and are ready to begin developing custom Java applications that integrate with MicroStrategy.

In Part 2 of this post I show you how to connect to MicroStrategy, run a report, retrieve the results as XML, parse the results and finally display the report results to a user from a custom Java web application. For that post I will of course be using a development environment that was setup with these very same instructions.