Gerrit Neumann

In the previous post I showed several examples for customizing JFreeCharts in JasperReports by using customizer classes. The examples covered the topics of legends, parameters, ticks and gridlines. In this post I’ll cover the topics of markers and rendering. All details discussed in this article are related to JFreeCharts, so they can be applied both in the community edition as well in the professional or enterprise editions of JasperReports, iReport Designer and Jaspersoft Studio. MarkerThe readability of charts can be improved by adding horizontal and vertical marker lines or marker intervals to emphasize important or critical areas. In JasperReports markers can be configured by using chart customizer classes. The following examples show the use of marker lines and marker intervals in JasperReports. Displaying Marker LinesMarker lines can be added to a chart both in horizontal and in vertical orientation. The position of the marker, its style and color can all be configured to meet any needs. Following example shows two horizontal marker lines which depict the highest and the lowest value in the chart. http://blog.itransparent.de/wp-content/uploads/2013/11/XYMinMaxMarkerChart-300x176.png In many cases it’s not the minimum and maximum values that are required but rather marker lines showing the 20% and 80% values. Because a chart customizer class has access to the lowest and highest value of the chart and markers can be positioned at any value, the 20/80% marker lines can be easily implemented using a chart customizer class. Following example shows the 20/80% marker lines. http://blog.itransparent.de/wp-content/uploads/2013/11/XY8020MarkerChart-300x176.png Displaying a Marker IntervalIn addition to marker lines it is also possible to emphasize regions of a chart by displaying horizontal or vertical marker intervals. Similarly to marker lines, the appearance, position and style of marker intervals can be configured. For displaying a marker interval in JasperReports charts a chart customizer class is required. Following example shows a marker interval for the 20/80% area of the chart. http://blog.itransparent.de/wp-content/uploads/2013/11/XY8020MarkerIntervalChart-300x176.png Rendering of SeriesChart properties and chart themes only allow to set the colors for the series. Chart customizer classes also enable to set the line style, fill or renderer for a series. Following examples show some customizations for the rendering of series. Changing the Line Style for a SeriesDefining the line styles of a chart in iReport Designer or Jaspersoft Studio via chart properties or chart themes is unfortunately limited to a few border lines of the charts. Style settings for series are limited to colors only. Using chart customizer classes in JasperReports the line style for series can be changed. This is especially required in b/w print, because different line styles are necessary for creating readable charts. Following example shows lines for a series as dashed, solid and custom styled lines. Also the line thickness can be adjusted by using chart customizer classes. http://blog.itransparent.de/wp-content/uploads/2013/11/XYLineStyleChart-300x176.png Changing the Renderer for a SeriesNext to changing the line style for a series it’s also possible to change the appearance of a series by setting a different renderer for it. The JFreeCharts library offers a wide range of renderers, much more than the number of renderers configurable in JasperReports via the chart properties or chart themes. For example when displaying monthly values in a chart the default connecting lines don’t make any sense, since there are no in-between values. It would be more useful to render monthly values as steps. Following example shows the monthly values using steps instead of the default lines. The according renderer has been changed in the chart customizer class accordingly. Line styles can also be applied when using the step visualization. http://blog.itransparent.de/wp-content/uploads/2013/11/XYRenderingStepLineStyleChart-300x176.png By combining chart customizer classes and multi axis charts it’s also possible to combine different renderers in the same chart. http://blog.itransparent.de/wp-content/uploads/2013/11/XYRenderingMultiAxisChart-300x176.png Patterned Bar ChartsSimilarly to line charts it might also be necessary for bar charts not only to set the colors of the series but also define a texture or pattern. Especially when printing the reports in b/w patterns help differentiating between the series. Following example shows a bar chart with a selection of striped patterns. In general there are no limitations for the textures to be used in bar charts. http://blog.itransparent.de/wp-content/uploads/2013/11/stripedbars-300x128.png Setting the Color for a Series Based on its Name / Setting the Color for a Bar Based on its X or Y ValueImagine the scenario of a single report or several reports where different data for the same categories (series) is depicted in several charts. It would be preferable to always use the same color for the same category during the whole report or reports (e.g. lines and bars for Saxony should always be yellow and for Bavaria the color should be blue). It can be solved by setting the series colors using the chart properties or chart themes but if in a chart some series are missing or the series are scrambled, the colors will be mixed up. A more useful implementation would be the use of a chart customizer class. Chart customizer classes can access the dataset of the chart and therefore also know the categories (and series names). Based on this information a mapping can be defined in the chart customizer class which always sets the color for a series with the same name to the same color. A related problem is the coloring of single bars in the same series based on the value of the bar on the x or y axis. For example by using chart customizer classes all bars can be colored green which surpass a given value. The limit can be computed dynamically or be given as a report parameter from the user. Following example shows a bar chart with a single series where bars surpassing a given limit are colored green (the limit was set to 80% of the maximum value). http://blog.itransparent.de/wp-content/uploads/2013/11/ColoredBars2-300x162.png Using chart customizer classes it’s also possible to change the color of a bar in a series to e.g. green if the bar belongs to a category value e.g. “March”. Following example shows this scenario. Of course the name of the bar to be filled differently can be also set by a report parameter. http://blog.itransparent.de/wp-content/uploads/2013/11/ColoredBars-300x162.png Reading the Design Information from a Design-File oder Design-DatabaseChart customizer classes are regular Java classes, so when implementing the customize method there are no limitations. It’s also possible to use implementations from other Java libraries. This also allows us to retrieve all design information from a design file or from a design database. All examples presented in this and the previous posts can be adapted to work with a design database. ConclusionIn this post I presented several examples for customizing JFreeCharts in JasperReports by using customizer classes. The examples covered the topics of markers and rendering. In the end I also discussed the possibility of reading all design information from a design file or database – instead of hard-coding the design values in the code of the chart customizer classes. Download our free example chart customizing project for Jaspersoft Studio: ChartCustomizerBlog.zip

In the previous post on chart customizing in JasperReports I gave a short tutorial on the implementation and usage of chart customizer classes based on an example for enabling and configuring minor ticks. Chart customizer classes can leverage the full functionality of the JFreeCharts library, so the customizing possibilities are almost limitless. In this post I’ll show a few examples for JFreeCharts customization in JasperReports by using chart customizer classes. This first part of the overview is limited to the topics gridlines, ticks, parameter passing and legends. All details discussed in this article are related to JFreeCharts, so they can be applied both in the community edition as well in the professional or enterprise editions of JasperReports, iReport Designer and Jaspersoft Studio. Ticks and GridlinesTicks displayed on the y or x axis are closely related to gridlines, since the number of ticks define the number of gridlines (if visible). Chart properties and chart themes enable only limited configuration for ticks and gridlines. The examples below show several customizations which can be only implemented by chart customizer classes. Changing the Number of GridlinesJasperReports embeds the JFreeCharts library, automatically computes the optimal number of horizontal and vertical gridlines (and resp. the optimal number of ticks on the y and x axis) for a chart. If gridlines should appear more or less dense than automatically computed, a chart customizer class can be used to change the number of ticks and gridlines to be displayed. Following screenshot depicts a chart where the number of gridlines (and ticks) has been set to the fixed value of 5. http://blog.itransparent.de/wp-content/uploads/2013/11/XYYaxisChart1-300x176.png Cofiguration of the Ticks on the Date AxisThe previous example shows months values on the x axis. Although the date format can be set in iReport Designer and Jaspersoft Studio, the number of ticks on the date axis is still computed automatically by JFreeCharts which doesn’t necessarily result in a tick for each month. Chart customizer classes enable the detailed configuration of the ticks for a date axis. E.g. the chart in the previous example can be configured in a way that the date axis sets a tick for each month. The resulting chart will look like as follows: http://blog.itransparent.de/wp-content/uploads/2013/11/XYYaxisChart11-300x176.png It’s also possible to set the number of repetitions per unit on the date axis (e.g. you could repeat each year 4 times on the x axis). Enabling a ZerolineIn some cases the readability of a chart is much improved by displaying a zeroline. One solution might be to generate a pseudo-line in a line chart with zero values only. The drawback of this solution is that this pseudo-line will always be displayed, even if there are no negative values. The JFreeCharts library natively supports zerolines, which are displayed only if there are values present above and below or close to the zero region. The zeroline can only be enabled by implementing a chart customizer class. It’s also possible to configure the line to specific needs, e.g. the following example shows a red dotted zeroline. http://blog.itransparent.de/wp-content/uploads/2013/11/XYZeroLineChart-300x176.png Mapping Multiple Charts to the Same Y Axis in MultiAxis ChartsMultiAxis charts can be used either for displaying different type of values in the same chart (e.g. rain, temperature and hours of sunshine) or for displaying similar type of values using different appearances (e.g. displaying the values for each region as lines and displaying the country average as area). The latter requires the mapping of all charts to the same y axis. This can be achieved by chart customizer classes. Following example uses three different renderers for the different series. Each series is a separate chart in a MultiAxis chart. A chart customizer class has been applied for mapping all three charts to the same y axis. http://blog.itransparent.de/wp-content/uploads/2013/11/XYRenderingMultiAxisChart-300x176.png Changing the Chart Appearance by Setting Report Parameters / Input ControlsAnother advantage of chart customizer classes compared to chart properties or chart themes is that the appearance of a chart can be manipulated based on the values of report parameters. All customizations implemented using chart customizer classes can be extended so that they can be controlled by report parameters. Following screenshots show the usage of report parameters with chart customizer classes. In this example the number of ticks on the y axis can be set via report parameters (or input controls in the JasperReports Server). http://blog.itransparent.de/wp-content/uploads/2013/11/XYYAxisChart10-300x196.png When changing the value from 10 to 4, only 4 ticks (and 4 gridlines) are generated on the y axis of the chart. http://blog.itransparent.de/wp-content/uploads/2013/11/XYYAxisChart4-300x190.png LegendsChart properties and chart themes enable only limited customizations for legends. If for example a legend should be displayed vertically and without borders, it can only be achieved by a chart customizer class. The style of a legend can be customized very flexibly using chart customizer classes. Following example shows a legend without borders and in vertical arrangement. http://blog.itransparent.de/wp-content/uploads/2013/11/TimeSeriesChart_Legend-300x171.png ConclusionIn this post I showed several examples for customizing JFreeCharts in JasperReports by using customizer classes. The examples covered the topics of legends, parameters, ticks and gridlines. In the upcoming post I’ll show further examples for customizing JFreeCharts in JasperReports using chart customizer classes, covering the topics of markers and rendering. Download our free example chart customizing project for Jaspersoft Studio: ChartCustomizerBlog.zip

Though Microsoft Visual FoxPro is discontinued since 2007, there are still many legacy applications running with this database. Using the JDBC driver provided by Caigen, DBF files (dBase III/ IV/ V, Clipper, xBase, FoxPro, Microsoft Visual FoxPro) can be used in Jasperreports (evaluated: iReport Designer, JasperReports Server V 5.2). You can create good-looking reports in the simple way of Jaspersoft.

IBM Cognos TM1 is a multi-dimensional database system for MOLAP applications. We are able to use Cognos TM1 as a data source for Jaspersoft (JasperReports: iReport and Server) and offer a free component on Talendforge. tTM1Input is a Talend Component for accessing IBM Cognos TM1 Data Cubes. It uses the MDX query language as used by IBM Cognos TM1 (slightly different to other MDX dialects). http://www.talendforge.org/exchange/index.php?eid=1046&product=tos&action=view For Jaspersoft, we will publish our solution in the near future. It includes a fields reader and data sources for both server and iReport designer. We used IBM Cognos TM1 Version 9.5.2.

In the previous article I introduced the topic of customizing JFreeCharts in JasperReports by giving an overview of customizing possibilities for JFreeCharts in JasperReports (and resp. iReport Designer / Jaspersoft Studio). The most flexible and powerful way of customizing JFreeCharts can be achieved by using chart customizer classes. In this post I’ll give a short tutorial on the implementation and usage of customizer classes for customizing JFreeCharts. All details discussed in this article are related to JFreeCharts, so they can be applied both in the community edition as well in the professional or enterprise editions of JasperReports, iReport Designer and Jaspersoft Studio. What is a Chart Customizer Class?Chart customizer classes are Java classes that can be used to implement changes in the appearance of charts. Compared to chart properties or chart themes, customizer classes can leverage the full functionality of the JFreeCharts library. For example by using chart customizer classes you can change the line style for a series from solid to dashed; change the style for bars to custom gradients or even striped patterns; display a zero-line, marker-lines or marker-regions; read all design information from a database, etc. All these aforementioned customizations are not possible by setting chart properties or creating a chart theme, but they can only be achieved by implementing a chart customizer class. Implementation of a Chart Customizer ClassFor the implementation of a chart customizer class the interface JRChartCustomizer needs to be implemented. This interface defines a customize method, which takes a JFreeChart and a JasperReports chart object as parameters. Inside the customize method we can access all settings defined for a chart and apply our custom changes to them. During report execution JasperReports automatically calls this customize method for our chart and applies the implemented instructions. Following example shows the implementation of a chart customizer class for displaying minor ticks on the y-axis of a JFreeChart. Neither the chart properties panel, nor chart themes offer a possibility for enabling minor ticks, so displaying minor ticks requires the implementation of a chart customizer class. import net.sf.jasperreports.engine.JRChart;import net.sf.jasperreports.engine.JRChartCustomizer;import org.jfree.chart.JFreeChart;import org.jfree.chart.axis.ValueAxis;import org.jfree.chart.plot.XYPlot;public class XYMinorTicksCustomizer implements JRChartCustomizer{ public void customize(JFreeChart chart, JRChart jasperChart){ XYPlot plot = chart.getXYPlot(); ValueAxis rangeAxis = plot.getRangeAxis(); rangeAxis.setMinorTickCount(4); rangeAxis.setMinorTickMarkOutsideLength(1.0f); rangeAxis.setMinorTickMarksVisible(true); rangeAxis.setTickMarkOutsideLength(2.5f); }}The number of minor ticks is set using the setMinorTickCount method. The first minor tick always overlaps with the lower major tick, so by setting the value for the minor tick count to ’4′ we get 3 minor ticks between two major ticks. The length for the major and minor ticks can be adjusted by setting the values accordingly using the methods setTickMarkOutsideLength and setMinorTickMarkOutsideLength. By setting a new length for the ticks the minor ticks appear smaller than the major ticks. The visibility of the minor ticks can be adjusted by the method setMinorTickMarksVisible. By applying the customizer class to a chart, we get the following result (notice the minor ticks on the y-axis): http://blog.itransparent.de/wp-content/uploads/2013/10/XYMinorTicksChart1-300x176.png Using Chart Customizer Classes in a ReportBefore we can use a chart customizer class in a report, first we need to compile its code and export it to a JAR file. In the iReport Designer this JAR file needs to be added to the classpath. For adding a JAR to iReport’s classpath, go to Tools / Options / iReport / Classpath, click on ‘Add’ and select your JAR file. I’d advise to always set the tick in the ‘Reloadable’ column for the chart customizer JAR file. By making it reloadable, changes in the JAR file will appear automatically in iReport without the need of restarting the iReport Designer. This simplifies the development and debugging of chart customizer classes when using an external IDE.In Jaspersoft Studio the chart customizer class can be managed and developed in the same project as the reports. This simplifies the development and debugging of custom code used by reports, since there is no longer any need for an external IDE – everything can be developed using Eclipse. If an already existing chart customizer should be used in a Jaspersoft Studio project, then the JAR containing the customizer class needs to be added to the build path of the given project.The chart customizer class is a property of every JFreeChart, so every chart in a report can have its own customizer class. Of course the same customizer class can be applied for several charts in several reports. A chart can be bound to a customizer class by setting the “Customizer Class” property of the selected JFreeChart. The customizer class is referenced by its package name followed by the name of the class which contains the implementation of the customization. Following image displays the setting of the customizer class value for a JFreeChart in the iReport Designer. http://blog.itransparent.de/wp-content/uploads/2013/10/set_chart_customizer-300x201.png If report containing a chart which are manipulated by a customizer class needs to be deployed to the JasperReports Server, the customizer class implementation needs to be included to the classpath of the given report unit. To achieve this the JAR file containing the customizer class needs to be added as a resource to the report unit. To add a resource to a report unit in iReport Designer, right-click on the subfolder ‘Resources’ of the report unit and select JAR file from the context menu. Next a window will appear where you can set the ID, name and source path for the JAR resource (as depicted in the image below). The ID and the name of the resource can be different from the filename of the uploaded JAR. In Jaspersoft Studio the JAR file can be added by right-clicking on the folder of the report unit, selecting ‘New’ and ‘JAR’ from the context menu and adding the values for ID, name and the source path. http://blog.itransparent.de/wp-content/uploads/2013/10/add_jar_as_resource-300x168.png ConclusionIn this article I gave a short introduction on the implementation and usage of chart customizer classes for customizing JFreeCharts in JasperReports, iReport Designer, Jaspersoft Studio and JasperReports Server. The example customizer class which adds minor ticks to the y-axis of a chart can be downloaded as a Jaspersoft Studio project at the following link: ChartCustomizerBlog.zip The next post will give a more extensive overview on the customizing possibilities for JFreeCharts in JasperReports.

JasperReports allows many different visualizations for data in reports. In addition to tables and crosstabs, data can be visualized as charts or widgets. Charts and widgets in JasperReports are implemented using different chart libraries, such as JFreeCharts, HighCharts or FusionCharts. The design of a report created with JasperReports can be accurately adjusted to a defined corporate identity. This also includes the customization of charts embedded into the report. This article gives an overview of customizing possibilities for JFreeCharts in JasperReports (and iReport Designer). All possiblities discussed in this article are related to JFreeCharts, so they can be applied both in the community edition as well in the professional or enterprise editions of JasperReports and the iReport Designer. Chart PropertiesThe most direct way for customizing charts is by accessing the chart’s properties. After you’ve selected the chart in the Report Designer or in the Report Inspector panel, its properties will appear in the Properties panel. The properties for a chart are organized into three groups: Common properties are properties that all JasperReports elements have – like transparency, style or a print when expressionCommon chart properties can be set for any kind of JFreeChart – e.g. colors for the series, visibility of the legend or displaying and setting the title / subtitleChart specific properties, which include settings that can be applied only for specific chart types – e.g. configuration of the x- and y-axis or the visibility of shapes on plotted linesThe screenshot below displays the properties for a 3D Pie JFreeChart. The property groups are marked by grey backgrounds. http://blog.itransparent.de/wp-content/uploads/2013/10/pie3d_properties-251x300.png Chart ThemesUsing the Properties panel several chart settings can be edited in a quick and easy way. Unfortunately, the property values have to be set manually for each chart and in each report. For example if would be preferable to set the series colors only once and then apply the colors for several charts. To achieve this, JasperReports allows the definition of Chart Themes. The Chart Theme can be designed once and then applied on multiple charts in any report. Another benefit of using Chart Themes is that they enable setting a few more properties which or not available in the Charts Properties panel. The iReport Designer includes a Chart Theme designer which simplifies the design of Chart Themes. The properties for a chart can be accessed via the Template Inspector and then edited in the Properties panel. All changes are automatically applied in the Design panel to an example chart. The example chart type can be changed by using the drop-down menu at the top of the panel. The screenshot below shows the Chart Theme designer interface of the iReport Designer. http://blog.itransparent.de/wp-content/uploads/2013/10/chart_theme_designer-300x170.png To enable a newly designed Chart Theme in iReport so you can apply the theme to charts in reports, the Chart Theme needs to be exported as a JAR file and added to the classpath of iReport. The simplest way to do this is to right-click on the root node in the Template Inspector and select Export to JAR file from the context menu. This way the generated JAR file will be added automatically to the classpath of iReport. After exporting the JAR file from the Chart Theme designer in iReport, the Chart Theme can be accessed in the Properties panel of a chart (using the drop-down list for the property ‘Theme’). Chart Customizer ClassThe JFreeCharts library is very powerful and allows the configuration or customization of almost any detail of a chart. The Properties panel for a chart and the Chart Themes display only the most common JFreeChart settings. JasperReports enables the the customization of special chart details by using chart customizer classes. Customizer classes can leverage the full functionality of the JFreeCharts library. For example by using chart customizer classes you can change the line style for a series to dashed; change the style for bars to custom gradients or even striped patterns; show a zero-line, marker-lines or marker-regions; read all design information from a database, etc. Similarly to scriptlets, chart customizer classes have to be implemented separately from the report in Java using an IDE, such as Eclipse or Netbeans. After implementing the chart customizer class, the implementation needs to be compiled and exported to a JAR file. Then this JAR file has to be added to the classpath of the iReport Designer, so the customizer class can be found when the report is executed. The customizer class is then assigned directly to a (JFreeChart) chart in the report by adding the package name and the class name of the customizer class in the ‘Customizer Class’ property for the selected chart in its Properties panel. I will give a more detailed introduction on implementing and using chart customizer classes in the next blog post. ConclusionThis article gave an overview of customizing possibilities for JFreeCharts in JasperReports (and iReport Designer). Charts can be customized via the Properties panel for the selected chart, Chart Themes or Chart Customizer Classes. The next blog post will focus on the details for the implementation and usage of chart customizer classes.

German version (original): http://blog.itransparent.de/java-reflection-in-talend-data-integration-jobs A code generator (using JET) transforms Talend jobs to Java code. Schemas are represented by inner Java classes. Background: A webservice call used a string containing comma-separated values that should be read from an Excel file. The set of fields should be configurable at runtime. Solution: The component tJavaRow got out4 as input and read the relevant fields (tXMLMap created the SOAP document for the webservice call). The choice of relevant fields ist stored in a context variablen as comma-separated string at runtime. tJavaRow_3 (completely): String delims = "[,]";String[] attributes = context.Attributes.split(delims);String s = "";for (int i=0;i<attributes.length; i++){ // Replace empty spaces and brackets in the same way as Talend String methodName="get"+attributes[i].replaceAll("\\s|\\(|\\)","_"); java.lang.reflect.Method method = input_row.getClass().getMethod(methodName); String t =(String)method.invoke(input_row); if(i>0) s+=","; s+="\""+((t == null)?"":t)+"\"";}output_row.attributes=s;You can see that this component uses input_row and output_row internally instead of out4 and row7 respectively. Generally input_row has identical attributes as stream out4. It is not self-evident that Java reflection can be applied directly to input_row. The code from tJavaRow is transformed to this code fragment (manually formatted): ...String delims = "[,]";String[] attributes = context.Attributes.split(delims);String s = "";for (int i = 0; i < attributes.length; i++) { // Replace empty spaces and brackets in the same way as Talend String methodName = "get"+ attributes[i].replaceAll("\\s|\\(|\\)", "_"); java.lang.reflect.Method method = out4.getClass().getMethod(methodName); String t = (String) method.invoke(out4); if (i > 0) s += ","; s += "\"" + ((t == null) ? "" : t)+ "\"";}row7.attributes = s;...The methods getXXX() that are called by Java reflection are provided by class out4Struct. Here, out4 is an object of class out4Struct. These classes are generated automatically by Talend. As Talend generates public fields in this version (5.3), Java reflection could access fields instead of methods, too. Résumé: tJavaRow provides an easy way for adding user-defined Java code to a Talend job. input_row and output_row are replaced directly without sophisticated checks or generation rules. This facilitates interesting opportunities like using Java reflection.

German Translation (Original): http://blog.itransparent.de/datenquellen-aus-jndi-in-subreport-jasperserver/ My friend Dr. Robert Nagy has written a Whitepaper and describes how to combine heterogeneous data sources in subreports. One of my customers has had the concrete issue of a different data source in a subreport that is called in the detail band of the main report very often. This lead to many (too many) open connections. The common hint provided by Jaspersoft is the use of JNDI for management of JDBC data sources. In “Ultimate Guide” for JasperReports Server they write Data sources can be defined as either JDBC connections directly to a database or JDBC connections from the application server to the database that are exposed through JNDI. Jaspersoft recommends using JNDI data sources because the application server often has better connection pooling and management than JasperReports Server. Using JNDI-Datenquelle for main reports is straightforward. Data sources can be chosen either via Web application or via iReport/Jaspersoft Studio. Connection Expressions and Data Source Expressions respectively describe differing data sources for subreports. However, it is not obvious how to define a connection expression that creates a connection by using JNDI-managed data sources of the Repository. There has been a helpful article in the Jaspersoft-Community (http://community.jaspersoft.com/questions/542097/subreports-and-jndi, last visited 2013-10-01). They suggested a piece of Java that returns a connection after a lookup in JNDI. I try to avoid additional classes where possible as these have to be deployed to the JasperServer. The corresponding one-liner can used immediatly as connection expression (sugarcrm example as provided by Jaspersoft as sample data): ((javax.sql.DataSource)(new javax.naming.InitialContext()) .lookup("java:comp/env/jdbc/sugarcrm")).getConnection()This solution did not satisfy my requirements as JNDI name is managed by expression in the main report and not by the repository as expected. The RepositoryService can be looked-up within a report by com.jaspersoft.jasperserver.api.engine.jasperreports.util .RepositoryUtil.getThreadRepositoryContext().getRepository()The JNDI name of a data source in the repository of a JasperServer running the report can be obtained by ((com.jaspersoft.jasperserver.api.metadata.jasperreports.domain .JndiJdbcReportDataSource) (com.jaspersoft.jasperserver.api.engine.jasperreports.util .RepositoryUtil.getThreadRepositoryContext().getRepository()) .getResource(null,"/analysis/datasources/SugarCRMDataSourceJNDI")).getJndiName()A compact one-liner for a connection expression is ((javax.sql.DataSource)(new javax.naming.InitialContext()) .lookup("java:comp/env/" + ((com.jaspersoft.jasperserver.api.metadata.jasperreports.domain .JndiJdbcReportDataSource) (com.jaspersoft.jasperserver.api.engine.jasperreports.util .RepositoryUtil.getThreadRepositoryContext().getRepository()) .getResource(null, "/analysis/datasources/SugarCRMDataSourceJNDI")) .getJndiName())).getConnection() Due to performance reasonse, I recommend to keep a javax.sql.DataSource object as a variable in the main report instead of the one-liner. This prevents a repository lookup and a JNDI lookup for each row of the main report. Hint: For compiling reports in iReport Designer with connection expressions as described above, the classpath has to contain the relevant JAR files of JasperServer.

The Microsoft ® SQL Server Analysis Services (SSAS MS) provide the OLAP service of Microsoft SQL Server ® since version 2000 (Shiloh) and also in its later versions 2005 (Yukon) and 2008 (Katmai). Though the next version is available by SQL Server 2012 (Denali), SQL Server 2008 R2 (Kilimanjaro) is still the up-to-date version that is used in this evaluation. An ongoing project had the demand of representing the OLAP cube from SSAS 2008 R2 in JasperReports server. This could be evaluated positively with the JasperServer Pro version 4.5. According to the provided documents, Jasper focuses on Mondrian OLAP connections. However, XML/A connections are explicitly provided as a connection option by the vendor. XML/A connections can be provided by MS SSAS using “HTTP Access to SQL Server Analysis Services on IIS 7.0″ by providing the msmdpump.dll DLL as an ISAPI extension (Internet Server Application Programming Interface). Instructions on how to provide this service can be found on the Microsoft Developer Network (MSDN) at http://msdn.microsoft.com/en-us/library/gg492140.aspx. The integration in iReport is no problem with the help of XML/A connections. Unfortunately, it can create reports that can be run by iReport, but not be uploaded to the Jasperserver. The attempt of uploading results in the error message: “Unsupported language: You are trying to upload a report on JasperServer XMLA Which uses the mdx query language. This language is not supported by JasperServer and it is outdated. You should use the language mdx instead. ” The iReport with its repository navigator can upload XML/A connection to the JasperReports Server. This process runs without error, but the resulting repository entries of type “XML-A-Verbindung” or “XML-A connection” are of no use for subsequent OLAP queries, since the entries for the catalog and data source are erroneous. It leads to the error message: “com.jaspersoft.jasperserver.api.JSException: com.tonbeller.jpivot.olap.model.OlapException: Malformed data source given for determining XML/A provider” The solution is to suggest the “SQL Server Analysis Services” as Mondrian data source to JasperReports. The theoretical correct data source (e.g., provider = MSOLAP.4; Data Source = localhost; Initial Catalog = AdventureWorksDW2008R2) is not recognized and raises the error message: “com.jaspersoft.jasperserver.api.JSException: com.tonbeller.jpivot.olap.model.OlapException: Unexpected data source determining XML/A provider”is acknowledged. Instead, a Mondrian data source (e.g., “Provider = Mondrian; DataSource = Adventure Works DW 2008R2″) with the URI of the SSAS service (e.g., http://localhost/olap/msmdpump.dll) that is provided by MSMDPUMP.dll leads to the desired result. A working XML/A connection is shown in the following screenshot: As an example, I provide an OLAP cube that is navigable in JasperReports. It uses an example MDX query of the AdventureWorks sample data: SELECT { [Measures].[sales Amount], [Measures].[Tax Amount] } ON COLUMNS, { [Date].[Fiscal].[Fiscal Year].&[2008], [Date].[Fiscal].[Fiscal Year].&[2009] } ON ROWS FROM [Adventure Works] WHERE ( [sales Territory].[southwest] ) http://blog.itransparent.de/wp-content/uploads/2012/05/OLAP.png Using this workaround, MS SSAS can be used as an OLAP data source by JasperReports. Here are some pitfalls that occurred during this evaluation: com.jaspersoft.jasperserver.api.JSException: com.tonbeller.jpivot.olap.model.OlapException: java.lang.Exception: Schwerwiegender Fehler beim Parsen von MDX: Couldn’t repair and continue parseUngültiges Symbol “FROM” This error might raise, if the parser of the fields provider cannot process the MDX query. This might also happen with MDX queries that can be run in the Microsoft SQL Server Management Studio without errors.