Generate a report from an SQL Server 2008 Anaysis Services OLAP cube with iReport and JasperReports

Software versions that i used :

iReport 3.6.1
JasperReports 3.7.1
Microsoft SQL Server 2008 Analysis Services
Tomcat Web Server 6.0

I was recently tasked to generate a report through JasperReports/iReport
with data coming from an OLAP cube stored in SQL Server 2008 Analysis Services.

It was rather tricky because it seems there is no default Query Executer Factory that can handle MDX
through the XML/A protocol for SQL Server 2008 Analysis Services.

JasperReports defines a Query Executer factory for each query language (SQL, MDX …).
But i could not find a working Query Executer factory for SQL Server 2008 Analysis Services.
The factory net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuterFactory defined in the JasperReports API for MDX
through the XML/A protocol did not work.
I found out that iReport uses a different factory for MDX with the XML/A protocol as showed in the options :
Tools / Options / Query executers tab :

So in my servlet i used that same factory : net.sf.jasperreports.engine.query.JRXmlaQueryExecuterFactory.
It is not defined in the JasperReports API. It can be found in the cincom-jr-xmla.jar library which is located in
the C:\Program Files\JasperSoft\iReport-nb-3.6.1\ireport\modules\ext folder.

You then need to put it in WEB-INF/lib :

The important properties to define are the following:

HashMap parameterMap = new HashMap();
parameterMap.put(net.sf.jasperreports.engine.query.JRXmlaQueryExecuterFactory.QUERY_EXECUTER_FACTORY_PREFIX,    “xmla-mdx”);
parameterMap.put(net.sf.jasperreports.engine.query.JRXmlaQueryExecuterFactory.PARAM_XMLA_URL,    “http://localhost/olap/msmdpump.dll”);
parameterMap.put(net.sf.jasperreports.engine.query.JRXmlaQueryExecuterFactory.PARAM_XMLA_DS,
“W2K8SQLVS”);
parameterMap.put(net.sf.jasperreports.engine.query.JRXmlaQueryExecuterFactory.PARAM_XMLA_CAT,    “Adventure Works DW 2008 SE”);
net.sf.jasperreports.engine.util.JRProperties.setProperty(“net.sf.jasperreports.query.executer.factory.xmla-mdx”,
“net.sf.jasperreports.engine.query.JRXmlaQueryExecuterFactory”);

I decided to compile the .jrxml file directly from iReport. So the .jasper file is generated.
Here is the servlet that i used :

package servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.export.JExcelApiExporter;
import net.sf.jasperreports.engine.export.JRHtmlExporter;
import net.sf.jasperreports.engine.export.JRHtmlExporterParameter;
import net.sf.jasperreports.engine.export.JRPdfExporter;
import net.sf.jasperreports.engine.export.JRRtfExporter;
import net.sf.jasperreports.j2ee.servlets.ImageServlet;

public class ApriaServlet extends HttpServlet {
private static final long serialVersionUID = 1L;

public ApriaServlet() {
super();
}

public void service(HttpServletRequest request,    HttpServletResponse response)
throws IOException, ServletException
{

ServletContext context = this.getServletConfig().getServletContext();
String typeRapport = request.getParameter(“typeRapport”);

// 2.Fill  the .jasper file with data from the SSAS database. Generates a .jrprint file.
JasperPrint jasperPrint = null;

try    {

String utilisateurLogin =”myUser”;
String utilisateurPwd =”myPwd”;

HashMap parameterMap = new HashMap();

parameterMap.put(net.sf.jasperreports.engine.query.JRXmlaQueryExecuterFactory.QUERY_EXECUTER_FACTORY_PREFIX, “xmla-mdx”);
parameterMap.put(net.sf.jasperreports.engine.query.JRXmlaQueryExecuterFactory.PARAM_XMLA_URL, “http://localhost/olap/msmdpump.dll”);
parameterMap.put(net.sf.jasperreports.engine.query.JRXmlaQueryExecuterFactory.PARAM_XMLA_DS, “W2K8SQLVS”);
parameterMap.put(net.sf.jasperreports.engine.query.JRXmlaQueryExecuterFactory.PARAM_XMLA_CAT, “Adventure Works DW 2008 SE”);

String reportFileName = context.getRealPath(“/reports/report1.jasper”);

net.sf.jasperreports.engine.util.JRProperties.setProperty(“net.sf.jasperreports.query.executer.factory.xmla-mdx”,
“net.sf.jasperreports.engine.query.JRXmlaQueryExecuterFactory”);

jasperPrint = JasperFillManager.fillReport(reportFileName, parameterMap);
}
catch (JRException e)
{
e.printStackTrace();
}

//3. Export the .jrprint file to HTML

if (“pageHTML”.equals(typeRapport)){
PrintWriter out = response.getWriter();
try
{
JRHtmlExporter exporter = new JRHtmlExporter();
request.getSession().setAttribute(ImageServlet.DEFAULT_JASPER_PRINT_SESSION_ATTRIBUTE, jasperPrint);
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_WRITER, out);
exporter.setParameter(JRHtmlExporterParameter.IMAGES_URI, “image?image=”);
exporter.exportReport();
}
catch (JRException e)
{

e.printStackTrace();
}
}
if (“pdf”.equals(typeRapport)){
try
{
JRPdfExporter exporterPDF = new JRPdfExporter();
request.getSession().setAttribute(ImageServlet.DEFAULT_JASPER_PRINT_SESSION_ATTRIBUTE, jasperPrint);
exporterPDF.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);

ServletOutputStream out2 = response.getOutputStream();
exporterPDF.setParameter(JRExporterParameter.OUTPUT_STREAM, out2);
response.setContentType(“application/pdf”);
response.setHeader(“Content-disposition”, “filename=RapportSimple.pdf”);

exporterPDF.exportReport();

}
catch (JRException e)
{
e.printStackTrace();
}
}

if (“xls”.equals(typeRapport)){
try
{
JExcelApiExporter exporterXLS = new JExcelApiExporter();
request.getSession().setAttribute(ImageServlet.DEFAULT_JASPER_PRINT_SESSION_ATTRIBUTE, jasperPrint);
exporterXLS.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);

ServletOutputStream out2 = response.getOutputStream();
exporterXLS.setParameter(JRExporterParameter.OUTPUT_STREAM, out2);

response.setContentType(“application/xls”);
response.setHeader(“Content-disposition”, “filename=RapportSimple.xls”);

exporterXLS.exportReport();
}
catch (JRException e)
{
e.printStackTrace();
}
}

}
}

To create the template in iReport, you need to set up a datasource connection in the options :

It is used by iReport to retrieve the fields from the MDX query :

Here is the QueryString that i used :

<queryString language=”xmla-mdx”>
<![CDATA[SELECT NON EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS, NON EMPTY { ([Geography].[Country].[Country].ALLMEMBERS * [Geography].[State-Province].[State-Province].ALLMEMBERS * [Geography].[City].[City].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS]]>
</queryString>

Note :
I found that each time i restart iReport and open the report template, I get a popup
that shows up saying that it cannot find a registered Query Executer Factory class for xmla-mdx
queries, even if i previously set it up.

It seems to be a bug because the way i found to get around it is
to set up the Fields provider class  to blank (for instance), click on OK and then reset it up again to the class:
com.jaspersoft.ireport.designer.data.fieldsproviders.CincomMDXFieldsProvider

Here is the interface :

The template in iReport :

The generated PDF file :