MicroStrategy Architect Tutorial – Crash Course

MicroStrategy Crash Course in Architect!

This tutorial covers the essentials of MicroStrategy Architect. In this video I cover lookup tables, fact tables, attributes, relationships & more.

Please hit the LIKE button if you find the content helpful and SUBSCRIBE to help out the new channel and stay up to date with new MicroStrategy and BI related content!

MicroStrategy Level Metrics Tutorial with Examples

Finally, a tutorial with examples explaining level metrics!

After watching this video you will have a complete in depth understanding of the tricky subject.

Please don’t forget to LIKE the video if you find it helpful and SUBSCRIBE to my channel to master MicroStrategy as I’m always adding all types of new BI related content!

 

Filter Report with SQL Query

Filter Reports using SQL with ApplyComparison and ApplySimple

No doubt MicroStrategy has powerful filtering capabilities but if you are familiar with SQL, somtimes you might find that you feel a bit constrained in certain situations and thinking you could do things easier with a simple query and there may come a time where you need to filter a report based on the results of a SQL query.

For example, suppose you have a monthly sales report querying data that is stored only at the month level. Now, let’s also assume that you would like the report filter to dynamically determine the current month based on yesterday’s date (today – 1 day). This is common in retail because you are typically always looking at the previous days sales. To keep things simple, let’s assume you have a table named “LU_DAY” with the columns Date, Month, Quarter and Year.

If you wanted to return the current month based on yesterday the SQL query would look something like this “SELECT DISTINCT MONTH_ID FROM LU_DAY WHERE DAY_DATE = Date-1“.

Now, let’s take a look at how we can put this all together in a report using the MicroStrategy Tutorial Project.

Step 1 – Create a new report and add the attribute month and the revenue metric as seen in the below screenshot.

ApplyComparision Report

Step 2 – Next add a filter and choose the option “Add an Advanced qualification” as seen in the below screenshot.

Step 3 – This is where the magic happens. Now, we need to use the ApplyComparison function to compare two values in our filter. In this case we will be comparing the Month attribute to the results of our SQL query that will return the current MONTH_ID. Take a look at the resulting function in the below screenshot and then we will walk through each parameter.

ApplyComparison Function

First Parameter “#0 IN (#1)” – This tells the ApplyComparison function how to compare the other values being passed as parameters. The #0 and #1 are place holders for the other parameters being passed to the function. So in this case you are telling the function to only return results where the value mapped to #0 is found within the value mapped to #1. It is also worth pointing out that since our query will only return one value we could have also entered “#0 = #1” but hopefully you get the idea.

ApplyComparison First Parameter

Second Parameter [Month]@ID – Here we are simply passing in the Month attribute from our report. This value will get mapped to #0 in the first parameter.

ApplyComparison Second Parameter

Third Parameter ApplySimple(“SELECT DISTINCT MONTH_ID FROM LU_DAY WHERE DAY_DATE = Today()-1”,0) – In this parameter we are nesting another MicroStrategy function ApplySimple which allows us to use SQL pass-through to query the database directly from within our report. The ApplySimple function is a powerful function that can be used in many other places including metrics.

ApplyComparison Third Parameter

In closing, I do understand that in the tutorial project this was unnecessary because sales is stored at the day level so you could have simply used a normal filter but the purpose of this article is to show how you can incorporate SQL in your filters because there are instances where it is necessary. For example, as I mentioned in the beginning of the article, if the data you are reporting on is ONLY stored at a particular level like month, however, you need to filter the report based on data stored at a lower level like day, this might be an option to consider.

 

New MicroStrategy Plugin to Return Report Results as XML or JSON (works with Page-By)

If you have used the MicroStrategy Task API’s reportDataService task, then you may be aware it has a limitation and will not work on reports that have a page-by. In this article I’m going to introduce you to a new plugin I have created using the MicroStrategy SDK named GetReportXMLorJSON.

The plugin zip file can be downloaded from my GitHub repository here. I’m guessing most of you reading this article probably already know how to install plugins but for those that do not, installing the plugin is pretty straight forward. To install the plugin, extract the contents of the zip file and copy the folder to the “plugins” folder located in your MicroStrategy web installation directory. For example, if you are using Tomcat, you would go to your MicroStrategy web server and copy the contents of the zip file to C:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0\webapps\MicroStrategy\plugins. Next, you will need to restart your Tomcat or IIS web server for the changes to take effect.

Once you have the plugin installed, you can test the plugin by using the MicroStrategy TaskAdmin. The TaskAdmin is a nice tool for building and testing SDK URL’s. The URL for the Task Admin is http://localhost:8080/MicroStrategy/servlet/taskAdmin?pg=builder# (make sure you replace “localhost” with the host name or IP address of your web server. Next, click the tab labeled “Builder”. In the drop down box labeled “Task ID”, you should see the new plugin named “getReportXMLorJSON”. For this test we will have the new task return XML. Now, in the task envelope and task content type drop downs choose XML. Your screen should look similar to the below screen shot.

Next, we are going to enter the parameters. The first four parameters, ServerName, UserName, Password and Project are simply used to establish a connection to the project on your Intelligence Server. Next, you will need to enter the ReportID of the report you would like to execute. This can be obtained by viewing the properties of the report. The next parameter must be one of two values, either “xml” or “json” (without quotes). This will determine the format of the report results that will be returned by the task. Only enter a value for port if you know you are using something other than the default port. Finally, you have the option of expanding the page-by if the report has one. This is the key difference between this task and other out-of-the-box tasks that come with MicroStrategy as none of the out-of-the-box tasks that return XML or JSON can support reports with a page-by. I have confirmed this with MicroStrategy support. Lastly, enter values for prompts if required. Below is an example screen shot.

Once you are finished entering the parameters, click the button “Update URL”. This will build the appropriate URL in the below text box. This URL will be used to make the appropriate call to Intelligence Server. To execute the task, click the button labeled “Invoke URL”. This will use the URL to login to Intelligence Server, execute the report and return the results in the format we have specified. If all goes well, you should get XML results similar to the below screen shot with a status code of 200.

MicroStrategy Report Export Utility

Well, as the saying goes “necessity is the mother of invention” and in this post I’m going to introduce you to a tool I developed out of necessity that will enable developers to easily add MicroStrategy report export functionality to their custom applications or scripts. Some of you might be thinking why do I need this if I’m using Narrowcast or Distribution Services? Well, let me explain the benefits of this utility.

  1. As I said, this will enable developers to easily add report export functionality to their custom applications/jobs from any machine.
  2. MicroStrategy tools are NOT required to be installed on the machine where you are performing the export.
  3. Gives developers the power to export MicroStrategy reports using virtually any programming language or developer tool that can call and pass parameters to an executable. (I will demonstrate this using System Manager later)
  4. Can be used in shops that are running older versions of MicroStrategy or that lack certain licenses.
  5. Can be used in situations where Narrowcast or Distribution Services are not options. For example, let’s say you work for a retailer with several hundred stores. Now, suppose you have a requirement that requires you to automatically export (to a folder) daily sales reports in excel for each store. In addition, since stores open and close regularly, the process will need to be dynamic and only export reports for stores that are open for business. How can you accomplish this with Narrowcast? You obviously can’t create a separate Narrowcast job for each store because that won’t be dynamic, and you can’t export one report with all stores because they need to be in separate excel files as store managers should not be able to view other stores sales. To make matters worse, let’s suppose the business also wants each report to have multiple tabs with daily, week to date and month to date sales.
  6. Works with prompted and non-prompted grid reports.
  7. Quickly add custom MicroStrategy export to excel functionality without having to know/learn SDK

Let’s get started and take a look at how the utility works. The utility is an executable that takes up to 4 parameters. The parameters are as follows:

Parameter 1: Output Type – This is the type of file you want to export. “xls” = excel 97-2003 format, “xlsx” = excel 2007-2010 format, “pdf” = PDF file and “txt” = text file

Parameter 2: Output File – This is the full file path and name of the new file to be created. For example, “c:\temp\SalesReport.xlsx”

Parameter 3: Report ID – The MicroStrategy report id of the report to export.

Parameter 4 (Optional): Element Prompt Answers – This is used to pass parameters to prompted reports. Now, if you are familiar with SDK, I think you will find that passing parameters to this utility is a bit easier. The format is simple, it is basically just the attribute id followed by colon (:) then each value separated by a comma (,). If you have multiple prompts then separate them with a semi colon (;). For example if you have a report with two attribute element prompts and you want to enter 3 values in the first prompt and two values in the second prompt you would pass the following parameter string:  AttributeID1:Value1,Value2,Value3;AttributeID2:Value1,Value2

The application files can be downloaded from GitHub here. I recommend downloading the zip file “MstrReportExport_v1.0.zip” and extract the contents. You should now have 5 files exactly like the below screenshot.

 

The only two files you are concerned with are Config.config and MstrReportExport.exe. Config.config is the configuration file where you will enter the configuration parameters that will allow the application to connect to your MicroStrategy implementation. This is a simple plain text XML file. Now, open the file in a text editor like notepad and fill in the appropriate parameters. Below is a screenshot of the files contents. It is pretty self explanatory and I have commented each value in the file but let’s review in detail anyway. The first parameters is WebServerType and this simply expects the value “asp” or “tomcat” depending on whether your web server is setup using IIS or Tomcat. Next is WebServer which is simply the host name or IP address of your MicroStrategy web server. Next is the host name or IP address of your Intelligence Server. The next parameter is Project which is the name of the MicroStrategy project that contains the reports you will be exporting. Lastly, enter the MicroStrategy logon credentials UserId & Password. The last optional parameter BaseURL is unnecessary and I recommend leaving it blank.

Now that we have set our configuration values we are ready to begin using the utility. As I said in the beginning of this post, I was tasked with exporting a particular sales report to excel automatically every day for each store. With the consideration that the list of stores can change any given day. How did I do it? Well, enter System Manager. I’m going to explain the System Manager workflow only briefly because I already wrote another post covering System Manager in more detail, including a post on how to automate it. In a nutshell I created a System Manager workflow with a SQL Task that queried the data warehouse for a valid list of stores, I then output those results to a text file. After obtaining the list of valid stores I then iterate through the list, each time I execute the MstrReportExport.exe application and pass the appropriate parameters using the System Manager “Execute Application” task. Below is a screenshot of the workflow with comments explaining the purpose of each task. After the workflow was created I simply scheduled it to run every day using Windows Task Scheduler. See my other post if you need help scheduling a workflow with Task Scheduler.

Now, as you can see we have solved the problem of exporting the reports automatically every day with a dynamic set of parameters. But what about the multiple tabs in each file? Well, simple, inside the report I used a custom group where each section had a filter for daily, week to date, month to date, etc. I then added that custom group to the page-by and viola! That’s right, the application can also handle reports with page by’s. Some of you more experienced SDK developers may have encountered issues working with reports that have a page-by at some point. Below is a screenshot of a sample report that was output by the MstrReportExport utility (with the data masked for privacy reasons of course).

I thought it is also important to mention that this solution is functioning in a live production environment.

As you can see in my particular case I chose to integrate the utility with System Manager but sky is the limit and you can integrate the utility with whatever technology you like. The only requirement is that you have the ability to call an executable and pass parameters but let’s face it I don’t think that will be a limiting factor as just about any programming language or toolset has methods for executing external applications.

If I get positive feedback I will put out additional releases with added functionality like the ability to handle value prompts, documents and export to XML to name a few.

UPDATE: If you need to export multiple separate files using documents and as long as you do not have prompts and are on a version >= 9.3, look into using the burst feature in Distribution Services. It can accomplish the same thing and is built into MicroStrategy. Thanks to my friend Ameya for writing me regarding this feature and explaining how to accomplish my particular use case with burst. This is a much better option in my opinion.

Please leave comments! I’m really looking forward to reading them.

Automate System Manager Workflows

So you have created and tested your new MicroStrategy System Manager workflow and are thinking.. now what? I mean obviously running a workflow manually for testing purposes is fine but for production, you are probably expecting there to be a special System Manager scheduler where you can now schedule this workflow to run automatically. Right? Wrong.

MicroStrategy System Manager is a great tool but unfortunately there is no built in scheduler. However, luckily we have our trusty Windows Task Scheduler to help us schedule our new workflow. The process is pretty straight forward. Let’s say we have created a System Manager workflow named “My_Workflow.smw” in the folder C:\Workflows. The steps to automate this workflow are as follows:

1. First, we will create a batch file that will call the command to execute the workflow. On the same machine as the workflow, open a text editor like notepad and add the following text:
MASysMgr.exe -w C:\Workflows\My_Workflow.smw “UserName=Administrator” “Password=”

2. Be sure to replace the workflow path and username/password with your appropriate values. Save the file as “My_Workflow.bat” or another name of your choosing. The file should look similar to below.

Task Batch File Command

3. Go to Control Panel->Administrative Tools->Task Scheduler

4. Click “Create Basic Task…” from the menu on the right.

create basic task

5. Give the task a name and click next.

6. Choose the frequency. For this example I will choose Daily.

7. Enter the task start time. I will enter 9:00AM.

8. Choose the option “Start a program”.

9. Browse to the batch file we created in the first step. Also, I will usually populate the optional “Start in” text box with the same directory that my batch file is located. See my example below. My batch file is in the G:\temp folder so I enter that in the “Start in (optional)” field.

10. Click next and then Finish. You should now see your new schedule appear in the list of scheduled tasks.

That’s it! Your new workflow will now run every day at 9:00AM!

Please leave comments below!

Automation using MicroStrategy System Manager

Let’s face it, every shop can benefit from automation. Automation with MicroStrategy System Manager improves reliability and efficiency. What do I mean? Well, let’s say you have very important executive reports that need to be sent every Sunday but you do not want to send the reports in the event that a data load fails because then they will be wrong. In addition, if the data load fails and the reports do not go out, you would like the BI team to be notified. This is a very common type of requirement in the wonderful world of BI. So, what should you do? Develop a custom process outside of MicroStrategy? No, this is a perfect scenario that can be handled by System Manager.

Let’s get started developing a process with System Manager to fulfill this requirement.

Step 1. Open a new workflow in System Manager

Open System Manager and click File->New Workflow

Step 2. Determine if the current day is Sunday

In System Manager in the upper left, click the question mark icon and then click anywhere on the empty white space in our new workflow to create a new decision object. Now, click the white mouse icon to the right of the question mark; other wise every time you click inside the workflow designer a new decision object will be created.

This is an important object because it will help you control the flow of the process. This object can check for files, days of the week or other parameter values. For now we are going to use this to check if the current day of the week is Sunday.

Now, select the new decision object and a list of properties should appear on the right. Choose the option “Weekday(s) Check” and check the option for “Sunday”. Also, rename this object “Sunday” by right clicking and choosing rename.

Step 3. Determine if ETL data load succeeded or failed

Usually in BI data loads are performed by an ETL tool with processes that are developed by the ETL team. It is common practice to set some sort of flag upon successfully loading the data. In my current environment our ETL team will create what we call a “trigger file”. This is simply an empty file the ETL process will create in a specific directory after the data is successfully loaded. The presence of this file lets other processes know that the database is up to date with the correct data. Another approach is to set a database value in a table after the data is loaded. Either approach will work with System Manager. For now, let’s assume the ETL process creates an empty file upon successfully loading the data.

Now, in System Manager in the upper left, click the question mark icon and then click anywhere on the empty white space in our new workflow to create a new decision object. Next click the white mouse icon to the right of the question mark; other wise every time you click you will keep creating new decision objects.

Next select the new decision object and a list of properties should appear on the right. Choose the option labeled “File/Directory Check” and enter the full path to the trigger file. Also, make sure the drop down box labeled “File/Directory Check Condition” has the value “Exists”. This means that if the file exists this object will return SUCCESS, otherwise it will return FAILURE. Rename this object “Check flag”.

Finally, click the green arrow button in the upper left menu, then click the “Sunday” decision object and drag your mouse pointer over to the “Check Flag” decision object. This will create a link between the two objects where if the Sunday decision object returns SUCCESS, the process will then move on to the next step and execute the Check Flag decision object. Your process should look similar to below.

It is also worth mentioning that if the file is available via FTP, we could use the File Transfer Download object instead. Also, if the ETL process sets a database value after loading the data instead of creating an empty file, we could use the Execute SQL task to determine if the data load was successfully. The point is that there are many ways to handle this part of the process. It really depends on your environment, however, the methods I have pointed out are the most common I find.

Step 4. Execute the reports

To execute the reports for this example we are going to use a Narrowcast Server object. In the left tree menu under MicroStrategy Products->Command Manager click “Narrowcast Server (Windows Only)” and drag it over to the workflow designer to create a new object. Next enter the logon info for your Narrowcast meta data and choose the option to “Execute script statements” and enter the script command to execute the Narrowcast job you would like to run. See below example.

Next click the green arrow button, then click the Check Flag decision object and drag your mouse pointer to the new Narrowcast object. Your workflow should now look similar to below.

At this point our workflow will check if the current day is Sunday and if it is it will check that the data has been loaded based on the existence of a file and if the file exists, the process will execute the executive reports via Narrowcast.

5. Alert BI team if data load fails

Currently the workflow will execute the report job if every works but what if the data load fails and the trigger file does not exist? Now we need to add logic to handle failures.

In the left tree view under System Tools folder, click the “Send Email” object and drag it over to the workflow. Next click the red arrow and click the “Check Flag” decision object and drag your mouse pointer over to the new “Send Email” object. Rename the Send Email object “Alert BI Team”. Finally, enter the send email properties such as From, To, Body, Subject and you will probably need the SMTP server info for your companies SMTP server. The finished workflow should look similar to below. To run the workflow, click the lightning bolt located in the top menu.

Finally, save the workflow with a name of your choosing and that’s it! Now, this exact solution may not work for everyone because I have assumed that you send reports using Narrowcast or that your ETL process sets some sort of flag if it fails or even that you have an SMTP server to send email. A lot of this stuff is pretty standard but the main point of this post is to introduce you to the kinds of things that can be handled by System Manager to make your environment a little more manageable and efficient.

In my next post I show you how to schedule this System Manager workflow to run automatically.

Please share your comments!

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.