How to create a Site based Pentaho report (with sub-report) for Alfresco Business Reporting

alfresco business reporting logoThe 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. In this blog I will show how you can create  a report with content for a specific site only. The report will be stored back into that site. Your bonus is a subreports.

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 have the Alfresco Business Reporting add-in configured and installed in your Alfresco instance.

A particular use case for the type of report in this blog could be that the site members (or just the SiteManagers?) have knowledge of that particular facet of the site. Think: particular list of documents. Or a team list of all members with email + phone numbers etc. This same structure can be used to create user centered reports, that will be stored in (actually: relative to) the UserHome folder.

This report is a mixture of the parameterized report and the single report with subreports. Extended with a much more complex and powerful Alfresco Configuration.

Pentaho

The steps for this report:

  1. Create a new report. 
  2. Go to Ádd DataSource and add a JDBC datasource
  3. Select the JNDI datasource ‘alcfrescoReporting’
  4. Create a first query to retrieve the Site (site name will be driven by Alfresco)
    1. This query initially retrieves all Sites, and the columns you want to display. I use the reporting database table ‘site’.
      You can either use the query editor of Pentaho, or create your own SQL. Remember to test-drive your report frequently.
    2. Remind to use the isLatest=1 construct to assure you get the metadata of the latest version of the Site. The reporting tool captures all changes it can, so it probably has your old title and description too. The LIMIT 1; is probably not needed at all. I ran in issues in my test, but hen I forgot the isLatest thingy…
    3. As a last step, add the statement to limit the results to the Site defined by Alfresco in the parameter ${sitename}.
      For the main report this query was used:
      SELECT
       `site`.`cm_name`,
       `site`.`cm_description`,
       `site`.`cm_title`,
       `site`.`st_siteVisibility`
      FROM
       `site`
      WHERE
       `site`.`isLatest` =1
       and `site`.`cm_name` = ${sitename}
       LIMIT 1;
      

       

  1. Create a second query. This second query will retrieve only those documents that have a modified date later then their creation date.
    1. Select the columns you need from the reporting database table ‘document’.
      You can either use the query editor of Pentaho, or create your own SQL.
    2. Remind to add the isLatest=1 construct. Each document has multiple versions. You usually want to display the latest version only. The reporting tool adds and maintains this column to each object in the reporting database.
    3. Remember to add the restriction on ${sitename} to this subreport. Each document that is harvested and belongs to a site, gets the value of the site-name in the column ‘site’ in the reporting database table(s).
    4. In this sample I used the following SQL:
      SELECT
      `document`.`cm_created`,
      `document`.`cm_modified`,
      `document`.`cm_name`
      FROM
      `document`
      WHERE
      `document`.`cm_modified` > `document`.`cm_created`
      AND `document`.`site` = ${sitename}
      AND `document`.`isLatest` = 1
      ORDER BY
      `document`.`cm_name` ASC
      
  2. Now, the core of the work is done. But the hard works just begins 🙂 Reporting!
    In the main report be sure to add the Master Report Parameters. These are the parameters that drive the report, and will be provided by Alfresco Business Reporting. Add a parameter called ‘sitename’. It is the very parameter that is referenced in SQL by ${sitename}. make it a String (although Object will work fine.)
  3. In the main report, navigate to te queries (data tab), and actively ‘select’ the query valid for the main report.
  4. Drag some of the fields from the resultset onto the main report. I drag them into the ‘Report Header’-band. This header is visited only once. The ‘Details’ band is revisited for each and every result row in the resultset.
  5. Next, drag a subreport into the header. Make it a ‘banded sub report’ (spanning the full width). Be wise and name the sub report meaningfull. It will also be the name visible in the tabs on top of the editing panels.
  6. Activate the sub report in the editing panel. Navigate to the queries (data tab) and actively ‘select’ the query valid for the subreport.
  7. Drag the fields from the resultset of the query into the Details band. This wil be repeated for each result of the sub query.
  8. Make the subreport aware of the current site. In the ‘Edit sub-report parameters’ section you can edit the sub-report parameters. The parameter ${sitename} needs a value. Give it a reference  equal to the main report. both sitename and cm_name are good (and equal) candidates in this particular report.
  9. Done! (Feel free to enhance the look and feel of the report. ) Don’t forget to save the report!

Alfresco configuration

Now the easy stuff was done. Now it is Alfresco configuration time! First a short lecture. The ReportingRoot is the top-level object of the report definitions. It contains the ReportingContainers (hourly, daily, monthly etc.) who contain te ReportingTemplates (the Pentaho report defintions).  The ReportingRoot can define zero or more ‘base object queries’ (target queries). These queries define the resultset that the generic report definition will be executed against. The label ‘site’ refers to each of the results of that query.  (The same for ‘person’.)

reportingRoot-configuration

A Reporting Template contains the Pentaho report. The Reporting Template can parameterize the report execution by the field ‘Parameter Substitution’. This contains a comma seperated list of key=valu pairs. In this examle: sitename=cm:name means that the reporting parameter ‘sitename’ will be filled with value of cm:name of the base object. In our case, that is the Site (thus, the site name). (You can also use for example email=cm:email if your base object one of type Person).

Reporting Template

The Target Path is the path where the resulting report is stored. It makes sense to store the reports relative to the actual base-object. In this sample the report is stored in the documentlibrary of the Site, and then in a sub folder structure. The Alfresco Reporting tool will create the structure if it does not exist. Remind that you can use Java’s SimpleDateFormat syntax to include all kind of actual date/time details in the folderpath. Actually, it will also work if your include it in the filename. (You just cannot export it in an ACP.)

This combination of the base object query (on the ReportingRoot), and the Target Path (on the ReportingTemplate) makes this a powerful tool.

 

 

2013-04-24: Update project logo to comply with Alfresco’s trademark guidelines and policies

Advertisements