Using Pentaho with opentaps

How to design and run a report with the pentaho framework

1. Activate the opentaps pentaho component by uncommenting the following line in hot-deploy/component-load.xml :

   <!-- <load-component component-location="${ofbiz.home}/hot-deploy/pentaho"/> -->

2. Change the base-url context parameter in the web.xml file located in <OPENTAPS-HOME>\hot-deploy\pentaho\webapp\pentaho\WEB-INF to suit your web url :

   <context-param> 
     <param-name>base-url</param-name>
     <param-value>http://myweburl/pentaho</param-value>      
     <!-- 
     <param-value>http://localhost:8080/pentaho/</param-value>
     --> 
   </context-param>

3. Change the baseUrlOverride parameter in the file DefaultParameterForm.xsl located in <OPENTAPS-HOME>\hot-deploy\pentaho-solutions\system\custom\xsl folder :

   <xsl:param name="baseUrlOverride" select="'https://myweburl/pentaho/control/'" />	

4. Download the Pentaho Report Designer (stable version 1.5.6) Pentaho Report Designer 1.5.6 and the Pentaho Design Studio. Please refer to Introducing the Pentaho Report Designer and Setting Up Parameters With the Design Studio for insight into downloading and using these tools.

5. After having designed the report, publish it to the opentaps BI solutions repository :

    The opentaps BI solutions repository is located in <OPENTAPS-HOME>\hot-deploy\pentaho-solutions. In the following pictures the TransactionSummary report sample is published in the pentaho-solutions\financials\acctgtrans folder.

Configuration of a database connection

    To configure a database connection copy the template file datasource.xaction from, for example, <OPENTAPS-HOME>\hot-deploy\pentaho-solutions\financials\datasource.xaction to your customized solution folder and set the jdbc parameters as shown in the next picture :

Sharing a datasource connection configuration between reports

    After designing and publishing the report, open the associated action sequence script (<REPORT_NAME>.xaction) with Pentaho Design Studio and add a Pentaho BI Process action as shown in the next picture :

    Configure the XAction File text box so that it points to the datasource.xaction file (which contains the parameter configuration for the database connection). In the Outputs box insert an entry with the name shared_sql_connection and object as type as shown in the next picture :

    Select the action that executes the query, set the Database Connection Type to Shared and write <shared_sql_connection> on the Name dropdown as shown next :

Testing the action sequence in the Pentaho Design Studio

    To test the action sequence, keep your opentaps server running, select the Test tab on the Pentaho Design Studio, set the Pentaho Server URL text box to http://myserverurl/pentaho/control, click on the Generate URL button and then Run. You should then see your report rendered on the screen.

Security configuration in Opentaps BI

    Access permissions to Opentaps BI reports can be configured by associating the user login with an Opentaps security group which has REPORTING_VIEW and REPORTING_<CUSTOMIZED SECURITY_ID> permissions and then configuring an input parameter in the action sequence script (.xaction file) named permissionId with default value _<CUSTOMIZED SECURITY_ID>.

Step by Step security configuration Using Pentaho Design Studio

1. Click File --> Open File --> Navigate to where you have stored the ".xaction" file you created with Pentaho Report Designer, and click "OPEN". Note if it prompts you to update the file, do not update unless you are sure it will work.
2. Under the Process Inputs section click the blue plus icon and create a type of "string"
3. A new section called Process Input will now be created
   1. Under Name enter in "permissionId"
   2. Keep Type as "String"
4. Under Source Of Input
   1. Click the blue plus icon and add type of "security"
   2. Delete the "request" type of Source Of Input
5. Under Default Value
   1. Make sure the "Has Default Value" radio button is selected
   2. Inside the Default Value text box enter "_FINANCIALS"
6. Click File --> Save

Your finished configuration should look similar to the following picture

    In the next picture we configure the TransactionSummary.xaction script with a permissionId of _FINANCIALS which maps to a REPORTING_FINANCIALS permission we associated with the user login demofinadmin :

    Now only demofinadmin can can execute the TransactioSummary report.

Demo tour Opentaps BI

    Try the Opentaps BI demo at Opentaps BI Demo.

    Login first as demofinadmin password opentaps and navigate the Financials solution to the Transaction Summary Demo Report. Then login as demopurch1 password opentaps and navigate the Planning solution to Open Requirements CrossTab - Postgres.

    Have fun!

Error Messages

Could not remove disk store

Though unlikely, you may encounter a situation where a user can run reports, but when they move back up the report tree or to the top level they encounter an error similar to this:

Error calling event: org.ofbiz.webapp.event.EventHandlerException:
Problems processing event: org.hibernate.cache.CacheException:
net.sf.ehcache.CacheException: pentahoCacheCache: Could not remove disk store
entry for 169B6E44132ED9F6A1EFB690BAA75B0D.jvm1 repositoryen_US. Error was
pentahoCache Cache: The Disk store is not active.
(net.sf.ehcache.CacheException: pentahoCacheCache: Could not remove disk store
entry for 169B6E44132ED9F6A1EFB690BAA75B0D.jvm1 repositoryen_US. Error was
pentahoCache Cache: The Disk store is not active.)

The problem is that the ehcache.jar is having problems controlling it's cache, and may only happen with a report that has a lot of elements. To fix this problem

Change the cache that ehcache uses

Note: with 64-bit Java 1.6 on 64-bit Linux, only the second solution below worked

• edit [OPENTAPS_INSTALL_DIRECTORY]/hot-deploy/pentaho/config/ehcache_user.xml.
• change the line with <diskStore path="java.io.tmpdir"/> to <diskStore path="user.home"/> or one of the other choices listed above that line.

Or force Opentaps to use /tmp directory

• make sure [OPENTAPS_INSTALL_DIRECTORY]/hot-deploy/pentaho/config/ehcache_user.xml contains the line <diskStore path="java.io.tmpdir"/>
• edit [OPENTAPS_INSTALL_DIRECTORY]/startofbiz.sh
• add the following to the #VM args section

TMPDIR="-Djava.io.tmpdir=/tmp/"

• modify the last #VM args line to include that new argument

VMARGS="$MEMIF $DEBUG $RMIIF $ADMIN $LANGUAGE $TMPDIR"

References

1. Introducing the Pentaho Report Designer

2. Setting Up Parameters With the Design Studio