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.