How to create a parameterized 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. In this blog I will show how you can create  parameterized report, and drive the report from within Alfresco.

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.

In this blog I create a report showing sites where an external person is member of the group SiteMangers. Lets assume we can divide users by email address. Internal users have a particular domainname, external users have another domain name. In this sample, there are people with username@example.com, and username@alfresco.com. By defining the internal email domain, we know all others are external. Lets roll.

  1. Create a new report
  2. Select the JNDI datasource
  3. Create a new query
  4. Select the tables ‘person’ and ‘siteperson’
  5. Drag the username of one over the username of the other table (create a sql-join)
  6. Select the colums you need to display in the report
  7. Test the report (show all site members of all groups for a start)
  8. Now make the query parameterized; add the following lines
    WHERE
    `siteperson`.`siteRole` = 'SiteManager'
    AND `person`.`cm_email` NOT LIKE ${internalEmail}
    
  1. the first WHERE statement shows SiteManager persons only, the second restricts to people having an email address containing the value of the parameter named ‘internalEmail’. If this parameter contains ‘%@alfresco.com’, all users having another email address will show up.
    The entire SQL statement looks like:

    SELECT
    `person`.`cm_telephone`,
    `person`.`cm_userName`,
    `person`.`cm_lastName`,
    `person`.`cm_mobile`,
    `person`.`cm_firstName`,
    `person`.`cm_email`,
    `siteperson`.`siteRole`,
    `siteperson`.`siteName`
    FROM
    `siteperson` INNER JOIN `person` ON `siteperson`.`userName` = `person`.`cm_userName`
    WHERE
    `siteperson`.`siteRole` = 'SiteManager'
    AND `person`.`cm_email` NOT LIKE ${internalEmail}
    ORDER BY
    `siteperson`.`siteName`,
    `siteperson`.`userName`
    
  2. Now tell the report definition to listen to an input parameter. In the Data tab on the right, in the bottom of that panel, is an entry ‘Parameters’. Right click this entry and add a parameter. Name it ‘internalEmail’ and configure that it is of type ‘String’.
  3. Test-drive the report, and notice the entry field for ‘internalEmail’. Play with the value to test if the behaviour is as expected.
  4. Save the report, you’re done.

Up to here, it looks like this:

Next step is to upload the report into Alfresco.

  1. Navigate to ‘Data Dictionary/Reports/…’ and pick the execution container of choice. Do you need the report to be updated hourly, daily, weekly or monthly? Upload the report definition in the container of your choice. (I pick hourly.)
  2. Edit the property details,
    • Parameter substitution: key-value pair of the parameters you want to feed to your report definition. In this case: internalEmail=’%alfresco.com’. (Also consider ‘%@alfresco.com’ to filter the smart-asses using alfreso.com as a username instead of a domain)
    • targetPath: What is the path relative to the targetNode. This can be a folder name/path, but it can contain date/time elements like ${yyyy}, ${MM} and ${dd} (and all other stuff that Java’s SimpleDateFormat supports)
    • targetNode: What is the base folder  where you want to store the resulting output report. The targetPath will be calculated relative to the targetNode
  3. Wait fot the hour or manually trigger your report.
  4. Enjoy the result

This is captured in this screencam:

Advertisements