Planet Jaspersoft

March 6, 2014

I’ve been working with Infor PM OLAP Server for almost four years now, I knew it when it was known as MIS Alea, before Infor took over MIS AG. It is one of those products you don’t select, a client has it and you have to work with it. I’ve always used Infor products, like Infor Office Plus and Infor Application Studio, in order to access to this OLAP database, but, last week, I tried to do it using my favourite reporting platform: JasperReports Server, so I’d like to share my findings.

First of all, i’d like to point out that Jaspersoft hasn’t certified this OLAP DB  as a source of data. It is obvious, but I must say that I just want to tell you my experience, so I am not responsible for any misfunction caused, if you try to do what I did.

Infor PM OLAP Server (10.1.7 version, in my case) is tightly coupled to Microsoft Windows. Therefore, the interaction with JasperReports has to run through a standard, in this case XML for Analysis (XMLA). It is difficult to find information about Infor PM OLAP products on the Internet, so I relied on the documentation of the DVD which has the software, specially the “Provider for Infor PM OLAP” manual.

The default installation of Infor had the XML interface up and running. I just had to change the name of the server in the file C:\Program Files\Infor PM\OLAP\XMLA\DataSources.xml, I replaced the default host, localhost, by the name of my server (for this post inforserver), I also enabled the Basic Authentication on the site InforPMOLAPXmla. After I restarted IIS, I browsed through the URL of the interface: http://inforserver/InforPMOlapXMLA/XmlaWebService.asmx

Later on, I started to work with iReport by creating an OLAP connection (option XMLA Server of the datasources dialog). I could test the connection and get the metadata I needed: datasource, catalog and cube. The problems began when I tried to execute a MDX query through iReport, I didn’t get an error but nothing happened.  I searched for a solution on the Internet and I realized that you have to perform the fields mappings manually, the JasperReports Ultimate Guide was very helpful at this point. After an effort, I had my first test report and, of course, it didn’t run:


Error filling print... XML/A fault: The server did not recognize the value of the header 
HTTP SOAPAction: "urn:schemas-microsoft-com:xml-analysis:Execute".; Code: Client;
net.sf.jasperreports.engine.JRRuntimeException: XML/A fault: The server did not recognize the 
value of the header HTTP SOAPAction: "urn:schemas-microsoft-com:xml-analysis:Execute".; Code: Client;
at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.handleResultFault(JRXmlaQueryExecuter.java:494)
at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.parseResult(JRXmlaQueryExecuter.java:335)
at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.createDatasource(JRXmlaQueryExecuter.java:130)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1112)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:689)
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:900)
at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:152)
at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:464)
at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:300)
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:757)

I researched the issue and, finally, I had to modify the source code (version 5.2.0) of the class net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter I changed the line 222 of the method createQueryMessage:


mh.setHeader("SOAPAction", "\"urn:schemas-microsoft-com:xml-analysis:Execute\"");

to


mh.setHeader("SOAPAction", "urn:schemas-microsoft-com:xml-analysis:Execute");

This is the great advantage of the open source! I compiled the source code, took the file jasperreports-5.2.0 and changed the one shipped with iReport. I tried a preview of my report and, ufs, another exception:


net.sf.jasperreports.engine.JRRuntimeException: Could not find dimension "MONTH" on axis 1.
at net.sf.jasperreports.olap.JROlapDataSource.getDimensionIndex(JROlapDataSource.java:451)
at net.sf.jasperreports.olap.mapping.MappingParser.getDimensionIndex(MappingParser.java:75)
at net.sf.jasperreports.olap.mapping.MappingParser.tuplePosition(MappingParser.java:363)
at net.sf.jasperreports.olap.mapping.MappingParser.member(MappingParser.java:263)
at net.sf.jasperreports.olap.mapping.MappingParser.memberMapping(MappingParser.java:163)
at net.sf.jasperreports.olap.mapping.MappingParser.mapping(MappingParser.java:133)
at net.sf.jasperreports.olap.JROlapDataSource.init(JROlapDataSource.java:322)
at net.sf.jasperreports.olap.JROlapDataSource.(JROlapDataSource.java:122)
at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.createDatasource(JRXmlaQueryExecuter.java:141)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1112)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:689)
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:900)
at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:152)
at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:464)
at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:300)
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:757)

This time it was a bit more difficult to find the error, I had to debug and I found that Infor PM return the dimension name between [], so I modified the class net.sf.jasperreports.olap.JROlapDataSource, changing the line 431 of the method getDimensionIndex


if (dimension.equals(hierarchy.getDimensionName()))

to


if (dimension.equals(hierarchy.getDimensionName()) || 
    (("[" + dimension + "]").equals(hierarchy.getDimensionName())))

Once I compiled and changed the file on iReport, I could run the report properly.

The next step was to move the report to JasperServer. The first task here was to replace the default jasperreports-5.2.0.jar  by the one I modified. After that, I restarted JasperReports Server. When I tried to run the report, I got the following exception:



net.sf.jasperreports.engine.JRRuntimeException: Message-Call failed. 
at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.executeQuery(JRXmlaQueryExecuter.java:310) 
at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.createDatasource(JRXmlaQueryExecuter.java:127) 
...
Caused by: com.sun.xml.internal.messaging.saaj.SOAPExceptionImpl:
 java.security.PrivilegedActionException:
 com.sun.xml.internal.messaging.saaj.SOAPExceptionImpl:
 Bad response: (401Unauthorized at com.sun.xml.internal.messaging.saaj.client.p2p.HttpSOAPConnection.call(HttpSOAPConnection.java:157) 
at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.executeQuery(JRXmlaQueryExecuter.java:306) ... 16 more

It was exasperating! According to this forum post, the cause rely on the JVM, so I adapted me to the circumstances and revert the first change I made on the source code (“\”urn:schemas-microsoft-com:xml-analysis:Execute\””). I know have two different versions of the file jasperreports-5.2.0.jar: one for iReport and one for JasperServer.

March 6, 2014

October 28, 2013

I discovered Central Authentication Service (CAS) through an old copy of the “JasperServer External Authentication Cookbook”, about two years ago. Therefore, CAS was my first option when I was appointed to evaluate open source single-sign on products, and, of course, JasperReports Server was the first Web application to try it. Learning how to configure and deploy was relatively easy using the CAS User Manual, but the set up of JasperServer was a nightmare because the lack of documentation of the community version, that’s why I’m writing this post!

First of all, I set up a virtual machine with CAS configured to use an Active Directory user authentication, the tutorial “End-to-end Windows Example” of the CAS wiki was very useful at this point. Later on, I deployed JasperReports Server to another virtual machine. The communication between these two machines is through SSL, so I had to export the certificate used by CAS one and import into the trust store of the JasperServer one, the key points here were:

  1. Put the fully qualified domain name (FQDN) of CAS machine as the CN of the certificate.
  2. Register that FQDN into the DNS used by JasperServer machine.
  3. Import the certificate into the cacerts file of the JVM where runs the application server where JasperServer Web application is deployed.

Configuring JasperReports Server v5.2.0 was a matter of researching on forums and blogs, intuition, trial and error. Finally, the successful changes on the original JasperServer configuration were:

  • Adding the following beans to applicationContext-security.xml:

      <bean id="casAuthenticationProvider"
          class="org.springframework.security.providers.cas.CasAuthenticationProvider">
        <property name="userDetailsService"><ref local="casUserAuthorityService"/></property>
        <property name="serviceProperties"><ref local="authenticationServiceProperties"/></property>
        <property name="ticketValidator">
            <bean class="org.jasig.cas.client.validation.Cas20ServiceTicketValidator">
                <constructor-arg index="0" value="https://cas.test.com/cas" />
            </bean>
        </property>
        <property name="statelessTicketCache">
            <bean class="org.springframework.security.providers.cas.cache.EhCacheBasedTicketCache">
                <property name="cache"><ref local="ticketCache"/></property>
            </bean>
        </property>
        <property name="key"><value>lam_or_lame</value></property>
    </bean>

    <bean id="authenticationServiceProperties"
          class="org.springframework.security.ui.cas.ServiceProperties">
        <property name="service">
           <value>http://jasperserver.test.com/jasperserver/j_spring_cas_security_check</value>
        </property>
        <property name="sendRenew"><value>false</value></property>
    </bean>

    <bean id="ticketCache" class="org.springframework.cache.ehcache.EhCacheFactoryBean">
       <property name="cacheManager"><ref local="cacheManager"/></property>
       <property name="cacheName"><value>casTicketCache</value></property>
    </bean>

    <bean id="casUserAuthorityService"
          class="com.jaspersoft.jasperserver.api.metadata.user.service.impl.UserDetailsServiceImpl">
        <property name="adminUsers">
            <list>
                <value>fcosfc</value>
            </list>
        </property>
        <property name="defaultAdminRoles">
            <list>
                <value>ROLE_USER</value>
                <value>ROLE_ADMINISTRATOR</value>
            </list>
        </property>
        <property name="defaultInternalRoles">
            <list>
                <value>ROLE_USER</value>
            </list>
        </property>
    </bean>
  • Adding a new Authentication provider to applicationContext-security.xml:

    <bean id="authenticationManager" class="org.springframework.security.providers.ProviderManager">
        <property name="providers">
            <list>
                <ref local="casAuthenticationProvider"/>

                ...

            </list>
        </property>
    </bean>
  • Replacing the beans authenticationProcessingFilter and authenticationProcessingFilterEntryPoint in applicationContext-security.xml:

     <bean id="authenticationProcessingFilter"
          class="org.springframework.security.ui.cas.CasProcessingFilter">
        <property name="authenticationManager"><ref local="authenticationManager"/></property>
        <property name="authenticationFailureUrl"><value>/loginerror.html</value></property>
        <property name="defaultTargetUrl"><value>/loginsuccess.html</value></property>
        <property name="filterProcessesUrl"><value>/j_spring_cas_security_check</value></property>
    </bean>

    ...

    <bean id="authenticationProcessingFilterEntryPoint"
          class="org.springframework.security.ui.cas.CasProcessingFilterEntryPoint">
        <property name="loginUrl"><value>https://cas.test.com/cas/login</value></property>
        <property name="serviceProperties"><ref local="authenticationServiceProperties"/></property>
    </bean>
  • Modifying the filter chain in applicationContext-security-web.xml:

    <bean id="filterChainProxy" class="org.springframework.security.util.FilterChainProxy">
        <property name="filterInvocationDefinitionSource">
            <value>
                CONVERT_URL_TO_LOWERCASE_BEFORE_COMPARISON
                PATTERN_TYPE_APACHE_ANT
                ...
                /j_spring_cas_security_check=httpSessionContextIntegrationFilter,
authenticationProcessingFilter,anonymousProcessingFilter,
exceptionTranslationFilter,filterInvocationInterceptor
                ...
                /**=httpSessionContextIntegrationFilter, ...,
delegatingRequestParameterAuthenticationFilter,JIAuthenticationSynchronizer,
anonymousProcessingFilter,...
            </value>
        </property>
    </bean>

References

October 28, 2013

October 2, 2013

I’ve just upgraded my JasperServer installation from version 4.2.1 to 5.2.0 I followed the chapter 9 of the JasperReports Server community project installation guide, release 5.2.0, all ran properly and I logged on using the user jasperadmin. The former version used to authenticate its users connecting to a Microsoft Active Directory, the manual says at this point:

“Configuration modifications, such as client-specific security classes or LDAP server configurations, need to be hand-copied from your previous environment and re-integrated into the upgraded environment”

So, I copied the LDAP server configuration I described on my post JasperServer user authentication with Microsoft Active Directory, I restarted tomcat and tried to log on using my Active Directory credentials, but the access was denied. I reviewed the server log and I read the following exception:

org.springframework.beans.NotReadablePropertyException: Invalid property ‘principal.fullName’ of bean class [org.springframework.security.providers.UsernamePasswordAuthenticationToken]: Bean property ‘principal.fullName’ is not readable
or has an invalid getter method: Does the return type of the getter match the parameter type of the setter?
at org.springframework.beans.BeanWrapperImpl.getPropertyValue(BeanWrapperImpl.java:729)
at org.springframework.beans.BeanWrapperImpl.getPropertyValue(BeanWrapperImpl.java:721)

After researching on the Internet, I edited the file applicationContext-security-web.xml and added the filter in bold:

/**=httpSessionContextIntegrationFilter,encryptionFilter,multipartRequestWrapperFilter,
webAppSecurityFilter,jsCsrfGuardFilter,${bean.loggingFilter},${bean.userPreferencesFilter},
delegatingAuthenticationProcessingFilter,${bean.userPreferencesFilter},${bean.basicProcessingFilter},
delegatingRequestParameterAuthenticationFilter,JIAuthenticationSynchronizer,
anonymousProcessingFilter,delegatingExceptionTranslationFilter,filterInvocationInterceptor,
switchUserProcessingFilter,iPadSupportFilter

I restarted the application server and Active Directory authentication became available again.


References

October 2, 2013

May 24, 2012

I wrote about the integration between JasperServer and Microsoft Active Directory last October. One of the readers of the post, Andrés Arenas, drew my attention to a problem: if an person input its user name one time in lowercases, another in uppercases, Jasper Reports Server creates two users in its repository. Here you have an excerpt of a screenshot that shows the problem clearly:

JasperServer Screenshot

Fortunately, if you follow the best practice of assigning permissions to the Active Directory groups imported, you won’t have problems. I’m sure that the solution to this issue would be to patch the source code that imports the users, but for a systems administrator this isn’t practical. A quick way to avoid this problem is to edit the file $JASPERSERVER_HOME/apache-tomcat/webapps/jasperserver/WEB-INF/jsp/templates/login.jsp and change the line:

  <input id="j_username" name="j_username" type="text"/>

for

  <input id="j_username" name="j_username" type="text" 
    style="text-transform:lowercase;" onkeyup="javascript:this.value=this.value.toLowerCase();"/>

On the other hand, I recently had to design a report for a tracking system where each user just could see its own data, not those saved by others. Therefore, the SQL statement must include the user name within the WHERE clause. My first option was to create a parameter named LoggedInUser of the class com.jaspersoft.jasperserver.api.metadata.user.domain.User and put this clause:

 where username = $P{LoggedInUser}.getFullName()

But I got the following exception:

  com.jaspersoft.jasperserver.api.JSExceptionWrapper: Report design not valid : 
    1. Parameter type not supported in query : LoggedInUser class 
    com.jaspersoft.jasperserver.api.metadata.user.domain.User

A workaround to this problem is to create another string parameter, LoggedInUserName, and assign the expression $P{LoggedInUser}.getFullName() as its default value.

May 24, 2012

November 27, 2011

I’ve recently installed JasperReports Server (release Community Project 4.2.1) on Linux CentOS, so I would like to talk about some issues I’ve been dealing with.

First of all, I have to write about a JRStyledTextParser exception that I got and didn’t have on Microsoft Windows. Once more a forum post was the beginning of the solution, so I edited the file $JASPERSERVER_HOME/apache-tomcat/scripts/clt.sh and set the property java.awt.headless=true:

export JAVA_OPTS="-Djava.awt.headless=true ..."

I wanted to run JasperServer as a service so I had to code the following script in /etc/init.d:

#!/bin/bash
#
# jasperserver        
#
# chkconfig: 2345 95 15
# description: Script de inicio de JasperReports Server

RETVAL=$?
JASPERSERVER_HOME="/jaspersoft/jasperreports-server-cp-4.2.1"

case "$1" in
 start)
        if [ -f $JASPERSERVER_HOME/ctlscript.sh ];
          then
            echo $"Starting JasperReports Server"
            /bin/su - jasperadmin -c "$JASPERSERVER_HOME/ctlscript.sh start"
        fi
        ;;
 stop)
        if [ -f $JASPERSERVER_HOME/ctlscript.sh ];
          then
            echo $"Stopping JasperReports Server"
            /bin/su - jasperadmin -c "$JASPERSERVER_HOME/ctlscript.sh stop"      
        fi
        ;;
 restart)
        if [ -f $JASPERSERVER_HOME/ctlscript.sh ];
          then
            echo $"Restarting JasperReports Server"
            /bin/su - jasperadmin -c "$JASPERSERVER_HOME/ctlscript.sh restart"
        fi
        ;; 
 status)
        if [ -f $JASPERSERVER_HOME/ctlscript.sh ];
          then
            /bin/su - jasperadmin -c "$JASPERSERVER_HOME/ctlscript.sh status"      
        fi
        ;;
 *)
        echo $"Usage: $0 {start|stop|restart|status}"
        exit 1
        ;;
esac

exit $RETVAL

After that, I added the service:

chmod 755 jasperserver
chkconfig --add jasperserver

Finally, I used a specific user jasperadmin as the owner of JasperServer software, this user could not open the restricted ports 80 and 443 (I needed HTTPS), so I set up Apache to act as a proxy, adding this line to the httpd.conf configuration file:

ProxyPass / ajp://jasperserver.test.local:8009/

I hope that my experience will be useful for you!

November 27, 2011

October 31, 2011

I was appointed to evaluate JasperServer last week and I have to say that I’m really pleased with this Open Source reporting server, specially from my developer’s point of view, because it’s fully integrated with iReport IDE, which I had previously used to design JasperReports that I embed in my Java Swing Applications.

One of the requisites was that the product could be integrated with an Microsoft Active Directory infrastructure, so our systems administration team could setup and maintain the server in a quick and easy way. Although the user guide of my version (community project, release 4.1) referred to an “External Authentication Cookbook”, I just could found a former version on the Internet (release 3.5) and there are some differences, so I’d like to write about my findings. thus you don’t have to spend a morning configuring a simple test environment.

First of all, you have to edit the <application-server-path>/jasperserver/WEB-INF/applicationContext-security.xml config file, in my case C:\Program Files\jasperreports-server-cp-4.2.1\apache-tomcat\webapps\jasperserver\WEB-INF\applicationContext-security.xml, locate the bean authenticationManager and uncomment the line <ref local=”ldapAuthenticationProvider”/> , so your system will search for users in Active Directory first.

The next step is to look for the bean ldapContextSource, uncomment the lines and point to one of your domain controllers, using the credentials of an user that can read the directory. Here you have an example:

<bean id="ldapContextSource">
   <constructor-arg value="ldap://dc01.test.local:389/dc=test,dc=local"/>
   <property name="userDn">
      <value>CN=administrator,CN=Users,DC=test,DC=local</value>
   </property>
   <property name="password">
      <value>p@ssw9rd</value>
   </property>
</bean>

The next bean to configure is the userSearch one, changing the default constructor argument (uid={0}) by (sAMAccountName={0}) and setting up the DN root where you have configured your user accounts:

<bean id="userSearch">
  <constructor-arg index="0">
    <value>OU=USERS_OU</value>
  </constructor-arg>
  <constructor-arg index="1">
    <value>(sAMAccountName={0})</value>
  </constructor-arg>
  <constructor-arg index="2">
    <ref local="ldapContextSource" />
  </constructor-arg>
  <property name="searchSubtree">
    <value>true</value>
  </property>
</bean>

The last step is to change some values into the ldapAuthenticationProvider configuration, here you have an excerpt of  the one running on my test server, so you can compare with yours:

<bean id="ldapAuthenticationProvider"
      class="org.springframework.security.providers.ldap.LdapAuthenticationProvider">
  <constructor-arg>
    <bean class="org.springframework.security.providers.ldap.authenticator.BindAuthenticator">
      <constructor-arg><ref local="ldapContextSource"/></constructor-arg>
      <property name="userSearch"><ref local="userSearch"/></property>
    </bean>
  </constructor-arg>
  <constructor-arg>
    <bean class="org.springframework.security.ldap.populator.DefaultLdapAuthoritiesPopulator">
      <constructor-arg index="0"><ref local="ldapContextSource"/></constructor-arg>
      <constructor-arg index="1"><value>OU=GROUPS_OU</value></constructor-arg>
      <property name="groupRoleAttribute"><value>CN</value></property>
      <!--property name="groupSearchFilter"><value>((member={1})(CN=*))</value></property-->
      <property name="searchSubtree"><value>true</value></property>
    </bean>
  </constructor-arg>
</bean>

Finally, I’d like to point out that it is not a good idea to have your Active Directory passwords navigating as clear text through the insecure HTTP protocol, so it’s a good idea to change the default security constraints, in order to use the HTTPS protocol, enabling SSL into the <application-server-path>/jasperserver/WEB-INF/web.xml file. Please, review the Apache Tomcat documentation to enable HTTPS, taking into account that the JasperServer bundled Tomcat server uses APR.

October 31, 2011

Feedback
randomness