How to create a basic Pentaho report for Alfresco Business Reporting

Pentaho ReportingThe recently released new version of Alfresco Business Reporting makes life easy for you as business users. Usually there is some SQL knowledge around to create some nice reports fulfilling your specific reporting needs. And this blog is about how to create your first report using Pentaho Report Designer.

This blog is part of a series of how-to’s about Alfresco Business Reporting; how to create a report in Pentaho Business Reporting, and how to configure the report in Alfresco.

I assume you already got Alfresco Business Reporting installed in your Alfresco instance. Next, download the Report Designer from the Pentaho website (actually Sourceforge. I created the tool when version 3.7 was still hot and happening. In the mean time the tool evolved into version 3.9.1. I have seen reports from version 3.8 working in Alfresco Business Reporting. Never tried any more recent version yet, your miles may vary. (I know, that’s an enhancement). Configure JNDI for Pentaho Reporting as described in the Wiki.

  1. Create a new report definition
  2. Create a new JDBC DataSource.
  3. Add a new Connection – select JNDI connection, add a JNDI name and a Connection Name. Test the connection, done

Now you have a nice starting configuration. And ready to go. Let’s add a quey:

  1. Double-click in the tab Data – Data Sets – JDBC: alfrescoReporting
  2. In the pop-up, create a new query, give it a name and edit the query
  3. The tables that were created are in the left panel. Double click ‘document’. In the right panel you see the columns available. See the video for some possibilities. Eventually, put the query in the right panel. Remind that the Alfresco add-on brings you columns like ‘site’ (and ‘path’, ‘mimetype’, ‘noderef’, ‘parent_noderef’, ‘child_noderef’, ‘size’) and isLatest. site contains the site-name, isLatest (boolean) will indicate if it is the latest version. Documents can have older versions, as well as Folders. If the add-on records an object that is different from the last run, it will create a new version in the reporting database. The columns validFrom and validTo will help you indicate when the particular row was valid. The query could look like:
     count(*) as occurences
     NOT `document`.`site` =''
     AND `document`.`isLatest`=1
     AND `document`.`cm_modified` > `document`.`cm_created`
     AND `document`.`sys_store_protocol`='workspace'
     occurences DESC
  1. In the right panel (Data), extend the  datasets – JDBC: alfrescoReporting – queryname. Now you can see the properties retrieved from the query.
  2. In the band called ‘Details’, drag the properties ‘site’ and ‘occurences’. Align them on the top of the band. In the band ‘Report header’ add the column header labels. In the Page header, name the report.
  3. Minimize the height of the report (it defines the height of the resulting report rows).
  4. Test run your report by either call menu View – preview or by selecting the green triangle. If it looks acceptable, save the definition to disk.

  1. If you’re a die-hard Alfrescian, save over webdav or CIFS directly into Alfresco. Otherwise, navigate to Data Dictionary/Reporting/hourly and put your prpt file in there.
  2. Update the properties of your ReportDefinition
  3. Trigger report execution by hand, or wait until the hour is over.

  1. Enjoy the result

2 Responses to “How to create a basic Pentaho report for Alfresco Business Reporting”

  1. 1 Michael April 3, 2013 at 23:30

    How do you do this with MongoDB? There is no JDBC driver for MongoDB currently. I’m using Groovy right now to get around this but would like to use JDBC on the server side for report creation as well.

    Report Validation error will happen on server side even though it is successful on client side via PDI using embedded ETL.

    • 2 Tjarda Peelen April 4, 2013 at 10:49

      Hi Michael,

      Thanks for your response. Right now I support MySQL only as the reporting database. I am working to get this multi-vendor, and plan for Postgresql. Maybe MSSQL and Oracle, not sure if there is demand fr that.

      I did not consider no-sql databases, because I do not believe that ‘the business’ will be able to deal with that. And at this point in time I use some MySQL native statements, I doubt if you are able to work around that…

Comments are currently closed.