Thursday, May 10, 2012

Adding the OpenERP datasource to Pentaho Report Designer 3.9

The development version of Pentaho Report Designer (PRD) includes a new OpenERP data source to develop OpenERP reports.

This document explains the steps to add the data source to a stable PRD 3.9 install to build Pentaho reports using OpenERP.  This document does not cover how to call these reports from OpenERP.  Have a look at the Pentaho-reports-for-OpenERP project created by WillowIT for details on how to call your reports from OpenERP.

Step 1: Download Required Packages

Download the Jar files for the following components:


Download the XMLRPC binary libraries from Apache (Usually in a zip/tar):


Step 2: Copy contents to lib folder

  • Copy the jar files previously downloaded and place it in the 'lib' directory of your PRD installation.
  • Extract the contents of the XMLRPC zip to a temporary folder. Copy xmlrpc-client-3.1.3.jar, xmlrpc-common-3.1.3.jar and ws-commons-util-1.0.2.jar from the extracted 'lib' folder into the PRD installation 'lib' folder.

Start Developing

Done. Simply start PRD as normal and start using the new data source. The data source is located in the 'Advanced' submenu when adding a data source.

45 comments:

  1. Hi i've followed your steps, except the download part PRD OpenERP data source and PRD OpenERP data source editor are not available, so i skipped those downloads.

    Now i'd to start developing using PRD for OpenERP report, can you provide me the steps more detail to create the report?

    ReplyDelete
    Replies
    1. Hi Stone

      Thanks for reporting the broken links. They should be fixed now. You will need to download those two libraries to develop an OpenERP report using PRD.

      To answer your second question. I guess there are two parts when you talk about developing an OpenERP report:
      1. Designing the report in PRD.
      2. Running the report from OpenERP.

      If you are a regular PRD developer, you should be able to develop an OpenERP report. The data source is (hopefully) pretty straight forward and shouldn't cause you too much trouble. I may write up a blog at some point to help developers who are unfamiliar with PRD.

      If you want to run the report from the OpenERP web interface then you need to have a java process/server to render the Pentaho report and return the report to the OpenERP web server. WillowIT has developed an OpenERP plugin to execute the reports from OpenERP. Their project can be found here: https://github.com/WillowIT/Pentaho-reports-for-OpenERP.

      Delete
    2. Hi Pieter,

      Thx so much for your quick response,
      The problem i'm facing here is when i create a new report at PRD, i select data source, choose advance menu, but there're no openerp data source there even after i've downloaded openerp data source editor.

      i downloaded openerp data source editor at here : http://ci.pentaho.org/job/datasource-editor-openerp/
      is there any configuration or i need to copy and paste any library to my PRD source location?

      please advise

      Delete
    3. Hi Stone

      The links from the CI server will only work for trunk (dev version 4). You need to use the download links provided in this blog for the plugin to work.

      You should just follow the steps in this blog using the links in this blog and you should be OK.

      Delete
    4. Hi Pieter,

      success so thanks for your help...

      i start my PRD and try 1 of the OpenERP object, but when i was about to save it prompt error message as :

      java.lang.NoClassDefFoundError: org/pentaho/reporting/engine/classic/core/modules/parser/base/PasswordEncryptionService.

      the detail error exception i cant post it here cz of limitation character of comments here...

      i copied all jar based on your instruction into my PRD, but still error

      Delete
    5. Hi Stone

      On further testing the plugin doesn't work with PRD 3.8. I have updated the blog to only mention PRD 3.9 While the reporting engine didn't change much between PRD 3.8 and PRD 3.9, there is a new object I use that is only available in 3.9.

      I could go back and build a separate version for 3.8, but since 3.9 is out and in GA, I don't see much use for it.

      So please use PRD 3.9 in your testing and you should be fine.

      Thank you very much for your feedback.

      Delete
  2. Yup i just downloaded PRD 3.9 yesterday and it worked. But anyway thx so much for your detail information Pieter :)

    Excellent email and feedback.
    Bookmarked :D

    ReplyDelete
  3. Hi Peter

    I am new to Pentaho Data Integration. Can you help re this post http://jira.pentaho.com/browse/PDI-6684?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel#issue-tab

    I am trying to find the Plugin for OpenERP

    Regards

    Clive

    ReplyDelete
    Replies
    1. Hi Clive.

      The plugin hasn't been released with any official Kettle build yet but you can get it by downloading a nightly/development build from http://ci.pentaho.com/job/kettle.

      Also see http://wiki.pentaho.com/display/EAI/Pentaho+Data+Integration+Steps for some info on the OpenERP steps.

      Kind Regards,
      Pieter

      Delete
  4. Hi Pieter

    Thanks

    I will give the links and try and provide feedback

    regards

    Clive

    ReplyDelete
  5. Hi Peter..
    I want to ask u about how to design in pentaho that can show the one2many or many2one field on openERP (on wizard part)

    Before that, i have read the description which was written on
    'pentaho_reports' module.

    There were :
    Report Types
    ------------
    Broadly speaking, two types of data sources can be used to produce the reports. OpenERP object data sources or SQL query data sources.

    a) Object data sources have the advantage that they can use OpenERP model columns, even those that are not stored in the database. This includes functional fields, related fields, properties. They can iterate through one2many and many2many subfields. They also respect OpenERP record rules.

    b) SQL data sources have the advantage that they allow greater flexibility in selections, and other SQL features like grouping and selective inclusion of sub-queries. It also allows selection where no OpenERP model relationship exists. It does not respect OpenERP record rules, which may be seen as an advantage for creating summary type reports which may be run by users who may not be able to view low-level data. Because of this, you need to be careful.

    Ehm.. for point "b", it has been implemented by me, but i am still confused for point "a"..

    Do u have any detail explanation for point a..

    Thank you so much Peter

    Best regards,

    Anthony Huang

    ReplyDelete
    Replies
    1. Hi Anthony

      No, I don't have a detail explanation for point A at the moment. If you follow the instructions in this blog, you should be able to use OpenERP in PRD. If you want to know how to integrate Pentaho with OpenERP and run the report you designed in PRD from OpenERP, I suggest you contact WillowIT (http://www.willowit.com.au/) for more details on that.

      WillowIT were talking about doing some tutorials and youtube videos but it is not out yet and I'm not sure when that will be available.

      Delete
  6. Hi Hi Pieter, I installed the prd 3.9 and pentaho_reports module in OpenErp6.1.

    I created a report , when I run the report in openerp , I have this error : (, , )

    Can you help me ??

    ReplyDelete
    Replies
    1. I haven't seen that before. Have a look at the YouTube videos created by WillowIT http://www.youtube.com/playlist?list=PLLB1LZ2mxz0XvDaL9oy1XbrYy5B4q5FHu. Specifically look at this one: http://www.youtube.com/watch?v=y-c-72hy8Vk.

      Kind Regards,
      Pieter

      Delete
  7. Hello

    Wolud you please explain the XMLRPC binary libraries files which to download? I got list of xmlrpc tar files? And where to copy in detail?

    ReplyDelete
  8. In my pentaho report designer i did not find lib folder.

    Is I missing something?

    ReplyDelete
  9. I face this error

    except_osv: (u'111\nConnection refused', (, error(111, 'Connection refused'), ))

    ReplyDelete
  10. Connection refused is usually caused by the fact the small report server is not running.

    ReplyDelete
  11. Hi Pieter,

    I've developed a Pentaho report, and the report runs well in my local openERP. When I tried to run it in my client's server, there's this error:
    No connection could be made because the target machine actively refused it

    (, error(10061, 'No connection could be made because the target machine actively refused it'), )

    Do you have any idea how to solve this?

    Thanks,
    Yuli

    ReplyDelete
    Replies
    1. Hi Yulanti

      How are you connecting to the server? Are you using the WillowIT OpenERP/Pentaho server or is this a straight PRD to OpenERP connection?

      Try using the client's IP address in stead of localhost if it is a straight PRD to OpenERP connection. Also check that your client's firewall isn't blocking the connection.

      If you are using the WillowIT connection then it is best to contact them to find out why it isn't connecting. It will probably be a setup issue.

      Delete
  12. Hi Pieter,

    I'm using straight PRD to OpenERP Connection. Host in OpenERP Data Access had already used client's IP address and Firewall had already turned off too, but the problem is still occurred.

    Do you have any other solution?

    Thanks,
    Yuli

    ReplyDelete
    Replies
    1. I haven't come across this. Check the log file to confirm that OpenERP is running on the IP address and Port you are trying to connect to.

      Should be something like: openerp.service.wsgi_server: HTTP service (werkzeug) running on 0.0.0.0:8069.

      Then as a test you could telnet to the IP address/Port that you want to connect to. For example: telnet xxx.xxx.xxx.xxx 8069. You should be able to connect and get a prompt. If you don't then you'll have to find out how the server was configured. Maybe it isn't running on the network interface and only on the loopback (localhost). Maybe somebody changed the port number.

      Sorry I couldn't be more helpful, my guess is that it is a server configuration issue as I haven't come across an OpenERP installation that doesn't want to connect.

      Delete
  13. Hi Pieter,

    I am designing a report in pentaho report designer that contain chinese simplified words, when i print preview, the chinese words appeared, but when I preview using PDF the chinese words won't come out. It is the problem of the adobe reader or in pentaho? because I already insatlled adobe reader X and installed a chinese font pack for adobe reader X but it still wont come out. Or is there something i should do after installing font pack. And fyi I use Sim sun font for this.

    Sorry for the bad english Thanks for your help

    ReplyDelete
    Replies
    1. Hi Jenny

      I haven't had this issue before. It may be best to post your question on the Pentaho forum or try IRC irc.freenode.net ##pentaho where you will find community members that may be able to help you.

      Kind Regards,
      Pieter

      Delete
  14. Hi Pieter
    i created an openerp report in Pentaho successfully, then i added this report into openerp, when i click on the link to view the report it gave me this error:

    10061
    No connection could be made because the target machine actively refused it

    (, error(10061, 'No connection could be made because the target machine actively refused it'), )

    can u help me plz?

    ReplyDelete
    Replies
    1. Hi

      The best place to get support for the OpenERP module is from WillowIT. I see there is already an open call in their issue tracker describing your issue. Best is to follow up with them. My guess is that the Pentaho module can't connect to your OpenERP server because of a firewall or incorrect settings (servername/port number etc).

      Kind Regards,
      Pieter

      Delete
  15. How can i display multi language in Pentaho OpenERP Report (i18n support or not)

    ReplyDelete
    Replies
    1. I have never needed to do this, so I'm guessing.

      Have you had a look some posts in the Pentaho wiki or forum?

      Delete
  16. Hi Peter:

    I just downloaded PRD Ver 3.9.1, when trying to add OpenERP DataSource (Data->Add Data Source->Advanced->OpenERP Data Access); I enter my host, port, username and pass but there is no DB to select from; I tried my localhost and remote server and also tried the Test button.
    Thanks a lot!
    Mayte

    ReplyDelete
    Replies
    1. I've just tested it again with OpenERP 7.0 and 6.1 and it works for me.

      Have you disabled the database list when starting OpenERP for example using the --no-database-list option, or setting it in the config file.

      Enter the database name manually and see if that works. You can also monitor the OpenERP logs to see if there is any error while trying to connect.

      Delete
  17. Hi Peter

    My problem is after i install PRD , I cant not have access to Openerp anymore , when i put localhost:8069 it show me Internal Server Error and when I enter my host, port, username and pass but there is no DB to select from

    ReplyDelete
    Replies
    1. I've just tested it again with OpenERP 7.0 and 6.1 and it works for me.

      Have you disabled the database list when starting OpenERP for example using the --no-database-list option, or setting it in the config file.

      Enter the database name manually and see if that works. You can also monitor the OpenERP logs to see if there is any error while trying to connect.

      Delete
    2. Thank you for Answer Peter
      my question is what How i add Pentaho reports into level object , i dont have customization on openerp 7.0,how to embedd the Pentaho report created with the Pentaho report designer directly in OpenERP

      Delete
    3. This article only describes how to setup PRD to have an OpenERP datasource.

      To integrate Pentaho with OpenERP you have to follow the project created by WillowIT here.

      Delete
  18. Hi again Pieter
    After i integrate Pentaho addons with openERP i cant find pentaho report on action field on version 7, and for java_server and .gitignore where i should place them ??

    ReplyDelete
  19. Hello, great information!!

    I have a question, what do yo refer with "the 'lib' directory of your PRD installation", I have downloaded and extracted my PRD in the 'opt' directory, is this equivalent?

    ReplyDelete
    Replies
    1. Hello again Peter, I saw which my mistake was, I have done all the steps but no option menu appears, Im using PRD 3.9.1, do you think I should try with PRD 3.9.0??

      Thanks in advance!!

      Delete
  20. Hello, I made one report using report designer, Now when I print the report it will display all the records report not my selected record..

    Suppose I select 2 employee from list view and than print report than it will print report for all employee..

    ReplyDelete
  21. Good morning.
    Initially clarify that my English is not very good.
    I want to filter a parameter of Pentaho Report Designer (PRD), shops allowed to show to a user, for this I find the solution is to call the session from the OpenERP to PRD and to filter the parameter by idUser session. How I can do this? Is there some other method?. Thanks in advance.

    Buenos días.
    Inicialmente aclarar que mi ingles no es muy bueno.
    Quiero filtrar un parámetro del Pentaho Report Designer (PRD), para mostrar tiendas permitidas a un usuario, para ello la solución que encuentro, es llamar la sesión desde el OpenERP al PRD y poder filtrar el parámetro por el IdUser de la sesión. ¿Cómo puedo hacer esto? ¿Existe algún otro método?. Gracias de antemano.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  22. I am using pentaho report for openerp 7.0 but my problem is I can't call the quotations
    product listing field (add an item) product listing is not working when i call the field it call the entire product list of my Quotation.How can i rectify the same?

    ReplyDelete
  23. Hi Pieter,

    i downloaded openerp data source editor at here : http://ci.pentaho.org/job/datasource-editor-openerp/

    but i not appear menu opnenerp data access in Data->Add Data Source->Advanced->

    what can i do?

    plz answer me..

    ReplyDelete