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.

Python PDF Splitter – Split PDF files for free with Python

If you work with a BI tool such as MicroStrategy, I’m sure at some point you have come across the need to split large PDF files / reports into smaller separate files.

I have searched far and wide and the only solutions I have found require you to purchase a tool. One such tool is PDF Splitter. It is not very expensive but if you need to split a lot of large files, it can be slow.

I have developed a script in Python that I use in a production environment and it is much faster than an application like PDF Splitter, not to mention free.

You will need to install the Python 3 interpreter which is a free download from www.python.org. You will also need the PyPDF2 package which you can download from here. To install the PyPDF2 package first extract the contents, then browse to the directory from a command prompt and run the following command: python setup.py install.

You can download the PDF Splitter Python code from my GitHub repository here.

The Python script takes 4 parameters.

  1. Source PDF File: This is the source PDF file that you want to split
  2. Output Directory: This is the output directory where the split files are placed
  3. Output Name Prefix: This is a naming prefix that will be appended to the beginning of each file. The script automatically names the files after the bookmarks. So for example if your PDF file has a bookmark labeled “EAT_AT_JOES_STORE_1” and you enter an output prefix of “TEST_RUN – “, the splitter will name this file after it has been split “TEST_RUN – EAT_AT_JOES_STORE_1.pdf“.
  4. Delete Source File: This is a True/False Boolean value that will delete the source file if set to True

I have this script running automatically in a production environment using Windows Task Scheduler. I create the task, pass the appropriate parameters and schedule it to run accordingly and it works great!

If you need help scheduling this to run automatically in Windows, here is how I did it:

  1. Create a batch file (for example Execute_Split_PDF_Reports.bat)
  2. Add the following line of code to the batch file: “C:\Program Files (x86)\Python35-32\python.exe” <Path to the Python script>\Split_PDF_Reports.py %1 %2 %3 %4 (Note: The values %1, %2, etc. are the parameters that will be passed from the Scheduled Task we will create in later steps)
  3. Replace the underlined portion of the above code with the path to where you saved the Split_PDF_Reports.py Python script. (Also, if you installed Python interpreter to anywhere other than the default directory, you may need to edit that path as well)
  4. Create a new Scheduled Task by going to Start->Control Panel->Administrative Tools->Task Scheduler
  5. Under the general tab make sure you choose the option “Run whether user is logged on or not” otherwise the task will not run unless a user is actively logged in. If this is a production server, you definitely want this option checked.
  6. Go to the Triggers tab and setup the schedule you want.
  7. Go to the Actions tab, click “New…” in the dropdown labeled “Action” choose “Start a program”. Then in the text box labeled “Program/script” click browse and choose the batch file we created in step 1. Next, add the 4 parameter values for the Python script separated by a space (Note: If your parameter values contain spaces make sure you surround them with quotes). Finally, enter the path where the batch file is located in the optional “Start in” text box. For example, if the batch file is located in the folder C:\Programs\BatchFiles then this is what you would enter in this field.
  8. Click OK and OK again in main window and your done!

Don’t forget to please leave comments below. Enjoy!