The 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.
- Use the default reporting configuration
- Create a basic report (this blog)
- Create a parameterized report
- Create a single report with subreports
- Create a site-based report with subreports
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.
- Create a new report definition
- Create a new JDBC DataSource.
- 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:
- Double-click in the tab Data – Data Sets – JDBC: alfrescoReporting
- In the pop-up, create a new query, give it a name and edit the query
- 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:
SELECT `document`.`site`, count(*) as occurences FROM `document` WHERE NOT `document`.`site` ='' AND `document`.`isLatest`=1 AND `document`.`cm_modified` > `document`.`cm_created` AND `document`.`sys_store_protocol`='workspace' GROUP BY `document`.`site` ORDER BY occurences DESC
- In the right panel (Data), extend the datasets – JDBC: alfrescoReporting – queryname. Now you can see the properties retrieved from the query.
- 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.
- Minimize the height of the report (it defines the height of the resulting report rows).
- 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.
- 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.
- Update the properties of your ReportDefinition
- Trigger report execution by hand, or wait until the hour is over.
- Enjoy the result