How to create a single Pentaho report with subreports for Alfresco Business Reporting

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. In this blog I will show how you can create a report with subreports, 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. For setting up JNDI and such, see the ‘basic report’ blog.

A particular use case for these kind of reports is an overview report over all sites (or all users, or a subset of sites or other concepts in your repository). If you want overview over concepts with detailed information per concept, this is the type of report you want to consider. The report is ‘layered’, there is a main query, and for each result of that main query, a second query is executed, collecting more detail. In this blog I create a report of all Sites. Per site I display some main properties like name, title, visibility en description. For each Site I want a set of details in my report. This can be numbers of documents, existence of particular document types or structures, or like in this sample, how many users are part of the Site_Groups in Share. The latter is done in a subreport, driven by a parameter of the main report. (If you feel like having a good day, you can also create sub-sub-sub reports.)

Pentaho

Start a new report. Go to “Add DataSources” and select the ‘alfrescoReporting’ JNDI source.
Create a new query. This first query will return all sites. Use the query builder of Pentaho, or hand-craft your query yourself. But always test-drive.
Remind to add the isLatest=1 clause in the WHERE section. This makes sure only the latest versions of the Site are relevant. Otherwise a single Site will show up more than once, if you ever changed any of the properties of that site. Your users will not get that. Include as many details of the site (columns) if you think is relevant.
```
SELECT
`site`.`cm_description`,
`site`.`cm_name`,
`site`.`cm_title`,
`site`.`st_siteVisibility`
FROM
`site`
WHERE
`site`.`isLatest`= 1
```
Create a second query. This query determines some kind of detail for the given Site. In this blog case it counts the number of members per SiteRole. E.g. 12 SiteConsumers and 1 SiteManger. Use the Pentaho query builder, or hand-craft your query. Eventually you need to tweak it by hand anyway. Add the count(*) clause, and the GROUP BY and ORDER BY. Test Drive, it is your last possibility.
Make the query specific for the Site at hand by adding `siteperson`.`siteName` = ${sitename}. In one of the next steps we will find a value for ${sitename}.
```
SELECT
`siteperson`.`siteRole` as role,
count(*) as amount
FROM
`siteperson`
WHERE `siteperson`.`siteName` = ${sitename}
GROUP BY role
ORDER BY role
```
Now your SQL magic is done. Next: Lay-out time, and some time to connect the dots.
In your Main Report, in the ‘Details’ band (the reporting band that is revisited for each row in the resultset), drag some of the fields from your resulting columns.
In your Main Report, in the ‘Details’ band, drag a sub-report, make it ‘banded’ (full width). Double click the sub report. It will open in a new tab.
In the right ‘data’ panel, navigate to ‘our’ second query, and actively ‘select’ it (right click). Drag’n’drop some of the fields from the resulting columns onto the ‘Details’ band.
In the right top of the Report Designer, select ‘Add Sub-report Parameters’. In here you connect ‘something’ from the Main Report to ‘something’ from the sub-report. In this example, connect cm_name from the Main Report to our ‘sitename’ parameter in the subreport.
Your challenge will be to make it look good. But from a functionality point of view: Done!

Alfresco Reporting

From a configuration point of view, this report is no rocket science.

Upload the report in the right ReportingContainer so it gets executed as frequently as needed.
In the properties you need to fill in the targetNode. This is the folder where the report is supposed to be stored. Or… If you need to you can make it the ‘base node’ of the path the report should be stored. You can configure the Target Path to extend the targetNode. This Target Path can contain variables from Java’s SimpleDateFormat to make folders ‘date/time-aware’. Think of adding ${yyyy} for the 4-digit year, or ${MM} for the 2-digit month. That is about all you need to make this sample work!

Open Source ECM