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 :
Webmentions
biosavons
rockies 4th of july hat for cheapla rams snapback billigtoddler knit hat ear flaps pattern librarynew york mets championship hat 55 nike air max oketo zalandoslip on adidas barngs exclusive air jordan 4nike zoom hyperfuse xdr amarillo negro
alyadance
bifold michael kors walletcarmen small saffiano leather belted satchelmichael kors extra small cecemichael kors burgundy crossbody bag air max 90 essential olive verde nike kd viii black and red cute clothing boutiquesdip hem dress boohoonew years dres…
spagazer
savannah chrisley dress acm rb4323 51 beach chiffon wedding dress balenciaga triple 2 air force 1s gs velvet tiered dress
emiliemorin
bucks championship jersey 2021 tim tebow jaguars jersey for sale liu jo maxi alexa ohio state football jersey gray new balance white 608 rockies 19 charlie blackmon green salute to service womens stitched mlb jersey
kaasajans
adidas campus vegan
sellergyllc
nike kobe 9 low graunike tiempo nero legacyair jordan 1 hvit and greyair jordan 30 university bleu redskins t shirt walmart for cheaplem barney jersey for cheapchelsea shirt 2009 for cheapgrimaldi rocco 22 jersey for cheap super bowl 51 shirt for cheap…
efornak
loubi queen black shirred bardot maxi dress red bottoms brand adidas gazelle black greyacg mowabb af1 blancadidas eqt adv salenike air pegasus 83 noir rose or nike air max invigor black white kashee online boutiquevampires wife collarblue dress with ja…
doramago
bears 1936 uniformdwyane wade swingman jerseyrangers 2018 winter classic jerseynike heat shirt air max 95 pink mens solid color sundresseswhite dress for church confirmationstrappy bandage dresssimple dress korean style royal blue chargers jersey air f…
muadotho
adidas predator malicejordan 6 low chrome for salejordan 5 retro size 7jordan 13 blu nero bianca womens wave rider 22 running shoe jessie dress lularoe nike air force high on feetmennns nike air max motion hvitnike air jordan sbnike dunk n7 michael kor…
filigrania
nike gt cut all blacknike air max 90 hyperfuse qs svartjordan 1 chicago 2020 gs noirnike sb blazer low premium red and sort nike internationalist negro junior adidas superstar white with black stripes junior backless glitter dressevening dresses stores…
cpsmath
nike gt cut all blacknike air max 90 hyperfuse qs svartjordan 1 chicago 2020 gs noirnike sb blazer low premium red and sort nike internationalist negro junior adidas superstar white with black stripes junior backless glitter dressevening dresses stores…
lacarspotting
nike gt cut all blacknike air max 90 hyperfuse qs svartjordan 1 chicago 2020 gs noirnike sb blazer low premium red and sort nike internationalist negro junior adidas superstar white with black stripes junior backless glitter dressevening dresses stores…
makersfinds
rockies 4th of july hat for cheapla rams snapback billigtoddler knit hat ear flaps pattern librarynew york mets championship hat 55 nike stock vapor pro jersey tiffany x and o necklace nike react 270 mens noirnike shox online noirnike free run 50 femmi…
ds-flow
mm6 north face denali kids dress online purchase jordan 4s air adidas messi 15.2 green redmi note 5 black cover korean summer dress
sinsumbar
nike flyknit racer red university ice yeezy 350 toronto blue jays floral hat knitting patterns
maxtraderclube
silk long sleeve mini dresses dealio north face red bottoms booties josh robinson 26 jersey for cheap q47223 how to make a baby hat with yarn 45
mytapbath
nike flyknit racer red university ice yeezy 350 toronto blue jays floral hat knitting patterns
lucianoluz
authentic new era fitted hats 2016 night t shirt for ladies wedding dress boho sleeves dress up online store vacation womens clothes north face vest mens
tertogel
pandora essence beaded necklace moncler cyclope vintage dallas cowboys shirt river plate 2019 kit black and red nike tracksuit bottoms air jordan 5 white low
netverka
nike air force 1 high x nba white one shoulder dress beige air jordan 6 white red for cheap 47 brand san francisco giants hat line 58 double pique polo michael michael kors lillie signature large chain shoulder tote
easycarup
free cookie monster knit hat pattern queen adidas lucas puig palace nike lakers crown shirt minnesota twins gang hat fall bridal shower dress for bride bolsa pillow tabby 26 coach
lesmatons
nike flyknit racer red university ice yeezy 350 toronto blue jays floral hat knitting patterns
flipcartbd
bright floral maxi dress black night dress with buttons urban outfitters betsey johnson kabru triclimate the north face limited edition christian louboutin shoes orange football jersey design for cheap
slowcicle
pandora essence peace miami hurricanes 2 jon beason green jersey ferragamo red bottoms pandora disney cat phone wallet for iphone 12 pro max new york yankees cap in delhi ncr
bcsarts
dress with skirt attached speights marreese jersey chicago white sox scrub hat usa party wear for 8 year boy mizuno wave horizon womens san francisco giants retro g hat game
fiverrsnooper
ganni printed mesh wrap dress minshew ii jersey for cheap boston red sox franchise hat quarter white air force 1 with red bottom best online clothing stores us nike mercurial superfly all pink
frisurenklub
nike shoes jordan retro 13 shoe white light blue men all air jordan 12 red sox fitted hats cheap prices the north face womens denali 2 hoodie black hooded body warmer pandora family forever and always charm
vanllen
lace top flower girl dress youth football uniforms packages for cheap mens ohio state buckeyes 12 cardale jones red 2015 college football nike limited jersey nike hypervenom phantom fg size 5.5 moncler full body coat gothic clothing usa
alniam
cotton salwar suit online lacoste polo dress sale josh jacobs white jersey nike sb opti yellow black moncler mens coat youth nike denver broncos dandre goodwin 11 limited orange team color jersey sale
npbusher
tiered sleeveless maxi dress pinkish purple dress elite derrick morgan mens jersey tennessee titans 91 camo fashion black nfl air jordan 5 retro black metallic silver moncler causses vest mens nike dallas cowboys morris claiborne 24 elite navy blue tea…