You can use REST interface of QueryBuilder or create an OSGi service using QueryBuilder API to create a custom report.
-
Data used in custom reports must be available in Process Reporting. To ensure the availability of data, schedule a cron job or use Sync option on the Process Reporting UI.
-
The URL request (encapsulating the desired query) must return an appropriate query result object. To create a query, you can use REST interface of QueryBuilder to create an OSGi service using QueryBuilder API. You can create dynamic or static queries.
CRX QueryBuilder REST interface exposes the functionality of the Asset Share Query Builder through a Java API and a REST API. Learn how to use CRX QueryBuilder REST interface, before performing the following steps:
-
You can specify optional parameters to specify offset, limit, hits, and properties. You can hardcode the arguments for static reports and fetch the parameters from UI for dynamic reports.
To fetch all the process names, the query is:
http://[Server]:[Port]/lc/bin/querybuilder.json?exact=false&p.hits=selective&p.properties=pmProcessTitle&path=%2fcontent%2freporting%2fpm&property=pmNodeType&property.operation=equals&property.value=ProcessType&type=sling%3aFolder
Note:
In every query, the path parameter points to the crx storage location and the characters are escaped according to the URL standard.
The prerequisite to creating a service using Query builder API are creating and deploying CQ OSGI bundle and using Query Builder API.
-
Add predicates to the newly created predicateGroup. A few useful predicate constructs are JcrBoolPropertyPredicateEvaluator, JcrPropertyPredicateEvaluator, RangePropertyPredicateEvaluator, DateRangePredicateEvaluator, and TypePredicateEvaluator.
For static reports hardcode the predicates, whereas for dynamic reports, fetch the predicates from the request.
Sample code to get all the instances of a process is:
Predicate predicate; //Add the path Constraint predicate = new Predicate(PathPredicateEvaluator.PATH); predicate.set(PathPredicateEvaluator.PATH, "/content/reporting/pm"); // should point to the crx path being used to store data predicate.set(PathPredicateEvaluator.EXACT, "false"); predicateGroup.add(predicate); //type nt:unstructured predicate = new Predicate(TypePredicateEvaluator.TYPE); predicate.set(TypePredicateEvaluator.TYPE, "nt:unstructured"); predicateGroup.add(predicate); //NodeType: Process Instance predicate = new Predicate(JcrPropertyPredicateEvaluator.PROPERTY); predicate.set(JcrPropertyPredicateEvaluator.PROPERTY, "pmNodeType"); predicate.set(JcrPropertyPredicateEvaluator.OPERATION, JcrPropertyPredicateEvaluator.OP_EQUALS); predicate.set(JcrPropertyPredicateEvaluator.VALUE, "ProcessInstance"); predicateGroup.add(predicate); //processName predicate = new Predicate(JcrPropertyPredicateEvaluator.PROPERTY); predicate.set(JcrPropertyPredicateEvaluator.PROPERTY, "pmProcessName"); predicate.set(JcrPropertyPredicateEvaluator.OPERATION, JcrPropertyPredicateEvaluator.OP_EQUALS); predicate.set(JcrPropertyPredicateEvaluator.VALUE, processName); //processName variable stores the name of the process whose instances need to be searched predicateGroup.add(predicate);
-
Iterate on the result and transform the results to desired format. Code to send the results in CSV format is:
Iterator<Node> iter = searchResult.getNodes(); while(iter.hasNext()) { Node node = iter.next(); row = new StringBuilder(); for (String property : includeProperties) { // the properties of the node which needs to be returned, or one can return all the properties too. try { row.append(node.getProperties(property).nextProperty().getString() + COMMA_SEPARATOR); } catch (NoSuchElementException e) { //Adding separator for no value row.append(COMMA_SEPARATOR); } catch (RepositoryException e) { e.printStackTrace(); } } row.deleteCharAt(row.lastIndexOf(COMMA_SEPARATOR)); row.append(NEW_LINE); out.write(row.toString().getBytes());
The following service example counts instances of a process that is in RUNNING and COMPLETE state at end of every month, quarter, and year.
package custom.reporting.service; import java.text.DateFormatSymbols; import java.util.Calendar; import java.util.HashMap; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.Map; import java.util.SortedSet; import java.util.TreeSet; import javax.jcr.Node; import javax.jcr.Session; import org.apache.felix.scr.annotations.Component; import org.apache.felix.scr.annotations.Reference; import org.apache.felix.scr.annotations.Service; import com.day.cq.search.Predicate; import com.day.cq.search.PredicateGroup; import com.day.cq.search.Query; import com.day.cq.search.QueryBuilder; import com.day.cq.search.eval.JcrPropertyPredicateEvaluator; import com.day.cq.search.eval.PathPredicateEvaluator; import com.day.cq.search.eval.TypePredicateEvaluator; import com.day.cq.search.result.SearchResult; @Component(metatype = true, immediate = true, label = "PeriodicProcessVolume", description = "Service for supporting cutom reports pluggable to Process Reporting.") @Service(value = PeriodicProcessVolume.class) public class PeriodicProcessVolume { private static String[] monthNameList = new DateFormatSymbols().getMonths(); private static String[] quaterNameList = { "I", "II", "III", "IV" }; private final Map<Integer, Map<Integer, Long[]>> monthly = new HashMap<Integer, Map<Integer, Long[]>>(); private final Map<Integer, Map<Integer, Long[]>> quaterly = new HashMap<Integer, Map<Integer, Long[]>>(); private final Map<Integer, Long[]> yearly = new HashMap<Integer, Long[]>(); @Reference(referenceInterface = QueryBuilder.class) private QueryBuilder queryBuilder; private void addConstraints(PredicateGroup predicateGroup, String processName) { Predicate predicate; //Add the path Constraint predicate = new Predicate(PathPredicateEvaluator.PATH); predicate.set(PathPredicateEvaluator.PATH, "/content/reporting/pm"); predicate.set(PathPredicateEvaluator.EXACT, "false"); predicateGroup.add(predicate); //type nt:unstructured predicate = new Predicate(TypePredicateEvaluator.TYPE); predicate.set(TypePredicateEvaluator.TYPE, "nt:unstructured"); predicateGroup.add(predicate); //NodeType: Process Instance predicate = new Predicate(JcrPropertyPredicateEvaluator.PROPERTY); predicate.set(JcrPropertyPredicateEvaluator.PROPERTY, "pmNodeType"); predicate.set(JcrPropertyPredicateEvaluator.OPERATION, JcrPropertyPredicateEvaluator.OP_EQUALS); predicate.set(JcrPropertyPredicateEvaluator.VALUE, "ProcessInstance"); predicateGroup.add(predicate); //processName if (processName != null) { predicate = new Predicate(JcrPropertyPredicateEvaluator.PROPERTY); predicate.set(JcrPropertyPredicateEvaluator.PROPERTY, "pmProcessName"); predicate.set(JcrPropertyPredicateEvaluator.OPERATION, JcrPropertyPredicateEvaluator.OP_EQUALS); predicate.set(JcrPropertyPredicateEvaluator.VALUE, processName); predicateGroup.add(predicate); } } private Long[] setFrequency(Long[] frequency, int index) { if (frequency == null) { frequency = new Long[2]; frequency[0] = 0L; frequency[1] = 0L; } frequency[index] = frequency[index] + 1L; return frequency; } public void populateValues(Session session, String processName) { PredicateGroup predicateGroup = new PredicateGroup(); predicateGroup.setAllRequired(true); try { addConstraints(predicateGroup, processName); long batchSize = 10000L; long start = 0l; while (true) { Query query = queryBuilder.createQuery(predicateGroup, session); query.setStart(start); query.setHitsPerPage(batchSize); SearchResult searchResult = query.getResult(); Iterator<Node> itr = searchResult.getNodes(); long length = 0; while (itr.hasNext()) { length++; Node n = itr.next(); Calendar calender = n.getProperty("pmCreateTime").getDate(); String status = n.getProperty("pmStatus").getString(); int index = 0; if ("COMPLETE".equals(status)) { index = 1; } else if ("RUNNING".equals(status)) { index = 0; } else { continue; } int month = calender.get(Calendar.MONTH); int year = calender.get(Calendar.YEAR); int quater; if (month < 3) { quater = 1; } else if (month < 6) { quater = 2; } else if (month < 9) { quater = 3; } else { quater = 4; } Long frequency[]; Map<Integer, Long[]> yearMonthMap = this.monthly.get(year); if (yearMonthMap == null) { yearMonthMap = new HashMap<Integer, Long[]>(); } frequency = yearMonthMap.get(month); frequency = setFrequency(frequency, index); yearMonthMap.put(month, frequency); this.monthly.put(year, yearMonthMap); Map<Integer, Long[]> yearQuaterMap = this.quaterly.get(year); if (yearQuaterMap == null) { yearQuaterMap = new HashMap<Integer, Long[]>(); } frequency = yearQuaterMap.get(quater); frequency = setFrequency(frequency, index); yearQuaterMap.put(quater, frequency); this.quaterly.put(year, yearQuaterMap); frequency = this.yearly.get(year); frequency = setFrequency(frequency, index); this.yearly.put(year, frequency); } if (length < batchSize) { break; } else { start = start + batchSize; } } } catch (Exception e) { e.printStackTrace(); } } public Map<String, Long[]> getMonthly() { Map<String, Long[]> result = new LinkedHashMap<String, Long[]>(); SortedSet<Integer> years = new TreeSet<Integer>(monthly.keySet()); for (Integer year : years) { Map<Integer, Long[]> yearMonthMap = monthly.get(year); SortedSet<Integer> months = new TreeSet<Integer>(yearMonthMap.keySet()); for (Integer month : months) { String str = monthNameList[month] + " " + year; result.put(str, yearMonthMap.get(month)); } } return result; } public Map<String, Long[]> getQuaterly() { Map<String, Long[]> result = new LinkedHashMap<String, Long[]>(); SortedSet<Integer> years = new TreeSet<Integer>(quaterly.keySet()); for (Integer year : years) { Map<Integer, Long[]> quaterMonthMap = quaterly.get(year); SortedSet<Integer> quaters = new TreeSet<Integer>(quaterMonthMap.keySet()); for (Integer quater : quaters) { String str = quaterNameList[quater - 1] + " " + year; result.put(str, quaterMonthMap.get(quater)); } } return result; } public Map<Integer, Long[]> getYearly() { return yearly; } }
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <!-- ====================================================================== --> <!-- P R O J E C T D E S C R I P T I O N --> <!-- ====================================================================== --> <groupId>com.custom</groupId> <artifactId>sample-report-core</artifactId> <packaging>bundle</packaging> <name>PR Sample Report</name> <description>Bundle providing support for a custom report pluggable to process reporting.</description> <version>1</version> <!-- ====================================================================== --> <!-- B U I L D D E F I N I T I O N --> <!-- ====================================================================== --> <build> <plugins> <plugin> <groupId>org.apache.felix</groupId> <artifactId>maven-bundle-plugin</artifactId> <version>2.3.7</version> <extensions>true</extensions> <configuration> <instructions> <Bundle-Category>sample-report</Bundle-Category> <Export-Package> custom.reporting.service.*; </Export-Package> </instructions> </configuration> </plugin> <plugin> <groupId>org.apache.felix</groupId> <artifactId>maven-scr-plugin</artifactId> <version>1.11.0</version> <executions> <execution> <id>generate-scr-scrdescriptor</id> <goals> <goal>scr</goal> </goals> <configuration> <!-- Private service properties for all services. --> <properties> <service.vendor>Sample Report</service.vendor> </properties> </configuration> </execution> </executions> </plugin> </plugins> </build> <!-- ====================================================================== --> <!-- D E P E N D E N C I E S --> <!-- ====================================================================== --> <dependencies> <dependency> <groupId>com.day.cq</groupId> <artifactId>cq-search</artifactId> <version>5.6.4</version> </dependency> <dependency> <groupId>javax.jcr</groupId> <artifactId>jcr</artifactId> <version>2.0</version> </dependency> <dependency> <groupId>org.apache.felix</groupId> <artifactId>org.apache.felix.scr.annotations</artifactId> <version>1.9.0</version> </dependency> </dependencies> </project>
The prerequisites to creating a separate UI for displaying results are Sling Basics, Creating a CRX Node and providing appropriate access privileges.
<%@taglib prefix="sling" uri="http://sling.apache.org/taglibs/sling/1.0"%> <%request.setAttribute("silentAuthor", new Boolean(true));%> <%@include file="/libs/foundation/global.jsp"%> <%@ page import="java.util.Map, java.util.Set, com.adobe.idp.dsc.registry.service.ServiceRegistry, javax.jcr.Session, org.apache.sling.api.resource.ResourceResolver, custom.reporting.service.PeriodicProcessVolume"%> <% response.setContentType("text/html"); response.setCharacterEncoding("utf-8"); %><!DOCTYPE HTML> <html> <head> <meta charset="UTF-8"> <link rel="stylesheet" href="/lc/apps/sample-report-process-reporting/custom-reports/periodicProcessVolume/style.css"> <title>REPORT Monthly / Qaterly / Yearly</title> <script type="text/javascript"> <% slingResponse.setCharacterEncoding("utf-8"); ResourceResolver resolver = slingRequest.getResourceResolver(); String processName = slingRequest.getParameter("processName"); Session session = resolver.adaptTo(Session.class); custom.reporting.service.PeriodicProcessVolume periodicProcessVolume = sling.getService(custom.reporting.service.PeriodicProcessVolume.class); periodicProcessVolume.populateValues(session, processName); if (processName == null) { processName = "All"; } %> var lineSeprator = "<td class='seprator'>----------------</td>"; var tableEnder = "<tr>" + lineSeprator + lineSeprator + lineSeprator + "</tr>"; var tableColHeader = "<td class='colHead colNum'>Running</td>"; tableColHeader += "<td class='colHead colNum'>Complete</td></tr>"; tableColHeader += tableEnder; var monthly = "<table><tr><td class='colHead colStr'>Month</td>"; monthly += tableColHeader; <% Map<String, Long[]> monthlyMap = periodicProcessVolume.getMonthly(); Set<String> monthKeys = monthlyMap.keySet(); for (String key: monthKeys) { Long[] frequencies = monthlyMap.get(key); %> monthly += "<tr><td class='colStr'> <%= key %> </td>"; monthly += "<td class='colNum'> <%= frequencies[0] %> </td>"; monthly += "<td class='colNum'> <%= frequencies[1] %> </td></tr>"; <% } %> monthly += tableEnder; var quaterly = "<table><tr><td class='colHead colStr'>Quater</td>"; quaterly += tableColHeader; <% Map<String, Long[]> quaterMap = periodicProcessVolume.getQuaterly(); Set<String> quaterKeys = quaterMap.keySet(); for (String key: quaterKeys) { Long[] frequencies = quaterMap.get(key); %> quaterly += "<tr><td class='colStr'> <%= key %> </td>"; quaterly += "<td class='colNum'> <%= frequencies[0] %> </td>"; quaterly += "<td class='colNum'> <%= frequencies[1] %> </td></tr>"; <% } %> quaterly += tableEnder; var yearly = "<table><tr><td class='colHead colStr'>Year</td>"; yearly += tableColHeader; <% Map<Integer, Long[]> yearMap = periodicProcessVolume.getYearly(); Set<Integer> yearKeys = yearMap.keySet(); for (Integer key: yearKeys) { Long[] frequencies = yearMap.get(key); %> yearly += "<tr><td class='colStr'> <%= key %> </td>"; yearly += "<td class='colNum'> <%= frequencies[0] %> </td>"; yearly += "<td class='colNum'> <%= frequencies[1] %> </td></tr>"; <% } %> yearly += tableEnder; function reloadFrame(value) { if (value === '-1') { window.location = "/lc/content/process-reporting-runtime/custom-reports/periodicProcessVolume.html"; } else { window.location = "/lc/content/process-reporting-runtime/custom-reports/periodicProcessVolume.html?processName=" + value; } } function populateTable(selection) { if (selection === 0) { document.getElementById('tableHeading').innerHTML = 'Monthly'; document.getElementById('volumeTable').innerHTML = monthly; } else if (selection === 1) { document.getElementById('tableHeading').innerHTML = 'Quaterly'; document.getElementById('volumeTable').innerHTML = quaterly; } else { document.getElementById('tableHeading').innerHTML = 'Yearly'; document.getElementById('volumeTable').innerHTML = yearly; } } function fetchProcesses() { var xmlhttp = new XMLHttpRequest(), request = ''; xmlhttp.onreadystatechange = function() { if (xmlhttp.readyState === 4 && xmlhttp.status === 200) { var responseText, response, items, hits = [], responseSize = 0, processName, selectedIndex = 0, comboBox; responseText = xmlhttp.responseText; if (responseText !== undefined && responseText !== null) { response = JSON.parse(responseText); responseSize = response.results; hits = response.hits; } items = "<option value='-1'>All</option>"; for(var i = 0; i < responseSize; i++) { processName = hits[i].pmProcessTitle; if (processName === '<%= processName %>') { selectedIndex = i + 1; } items += "<option value='" + processName + "'>" + processName + "</option>" } comboBox = document.getElementById('processSelection'); comboBox.innerHTML = items; comboBox.selectedIndex = selectedIndex; } }; request = "/lc/bin/querybuilder.json?"; request += "exact=false&"; request += "p.hits=selective&"; request += "p.properties=pmProcessTitle&"; request += "path=%2fcontent%2freporting%2fpm&"; request += "property=pmNodeType&"; request += "property.operation=equals&"; request += "property.value=ProcessType&"; request += "type=sling%3aFolder"; xmlhttp.open("POST", request, true); xmlhttp.setRequestHeader("Content-type","application/json"); xmlhttp.send(); } </script> </head> <body onLoad="fetchProcesses();populateTable(0);"> Process: <select id="processSelection" onchange="reloadFrame(this.value);"></select>     Period Interval: <select name="periodSelection" onchange="populateTable(this.selectedIndex);"> <option value="1">Monthly</option> <option value="2">Quaterly</option> <option value="3">Yearly</option> </select> <br> <br> <br> <br> <div class="inline"> Process:   <b><%= processName %></b>     Period:   </div> <b> <div id="tableHeading" class="inline"> </div> </b> <br><br> <div id="volumeTable"> </div> </body> </html>
The prerequisites to creating a separate UI for displaying results are Sling Basics, Creating a CRX Node and providing appropriate access privileges.
-
Create a separate UI as described in Creating a separate UI section.
-
Create a child nt:unstructured node at the /content/process-reporting-runtime/custom-reports node for every pluggable report.
- id - Specifies unique identification number of the report.
- name - Specifies the name of the report. The name is displayed in the UI.
- link - Specifies relative link to the renderer of the separate UI. The link is created Step 1.
- description - Specifies the one line description the report. You can leave the description field empty.
- icon - Specifies the image to pictorially represent the report. You can leave the icon field empty.
Import the sample-report-pkg-1.zip package to integrate custom reports and UI discussed in the article to the Process management UI.
Download