Skip to main content

Create custom reports in AEM (Component Report)

Image
By
Hey everyone, sometimes the client requirement is to capture a certain report with specific data and export in a certain format.

In this post, we will see how can we create custom reports and export them to an excel file as and when needed. After going through this post, we will get an understanding how Sling Resource Merger helps us to create a custom icon on the AEM start screen and how can we write and download an excel file with the required data.

Goal

The goal of this article is to create a utility that will fetch the list of the pages in our /content folder where a specific component is used. The specific use case of such a utility is for the auditing purpose where we may need a list of the components that are deprecated or going to be.

The list of the components will be exported in an excel file that will have two columns - the path of the page and the number of times a component is used on that page. (You can add more columns as per your need).

Design

We are going to create following to fulfil this task - 
  • An icon on the AEM start page called "Reports" (or "Custom Reports", maybe)
Custom Icon
  • After clicking on this icon, we will get a form where we can fill the details of the report
Reporting form
Here, you can fill in the following details
→ Report name (mandatory) - the name of the report
→ Search path - Path under which we want to search a component in the page. For e.g., /content/we-retail
→ Component path (mandatory) - sling:resourceType value of the component or the path of the component in apps. For e.g. my-project/components/content/text
→ Only include activated pages - if checked, the search will happen only in the pages which are activated.
→ Fetch results - on clicking, an excel file with the details will be downloaded.

Development

So, without further ado, its now time to get our hands dirty with some code. Below are the steps which define how are we going to develop this utility.

Step #1 - Creating a custom icon on the start page

To create a custom icon on the start page, we will be using the Sling Resource Merger concept which provides services to access and merge resources. All the navigation items that are present on the start page live here - /libs/cq/core/content/nav. Therefore, to add a new option on the start page, we need to overlay this path in our /apps folder.

After creating an overlay at - /apps/cq/core/content/nav, create new node reports under nav with the following properties -
<reports
    jcr:primaryType="nt:unstructured"
    jcr:title="Reports"
    href="/reports.html"
    consoleId="reports"
    icon="report"
    order="{Long}200"
 />
where
href - URL of the console. For e.g. http://localhost:4502/reports.html.
id - This is the ID by which we can tie our console to the navigational item.
icon -  Coral UI icon.
order - Relative order of the item.

Step #2 - Design the console

Our console will be a page on which we will render a component which will define the layout of our form. To do that, in our project structure, create a node called reports with the following properties - 

<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:sling="http://sling.apache.org/jcr/sling/1.0" xmlns:cq="http://www.day.com/jcr/cq/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0" xmlns:nt="http://www.jcp.org/jcr/nt/1.0"
    jcr:primaryType="cq:Page">
    <jcr:content
        jcr:mixinTypes="[sling:VanityPath]"
        jcr:primaryType="nt:unstructured"
        jcr:title="Component Report"
        sling:redirect="{Boolean}false"
        sling:resourceType="granite/ui/components/shell/page"
        sling:vanityOrder="{Long}301"
        sling:vanityPath="/reports">
        <head jcr:primaryType="nt:unstructured">
            <viewport
                jcr:primaryType="nt:unstructured"
                sling:resourceType="granite/ui/components/coral/foundation/admin/page/viewport"/>
            <breadcrumbs
                jcr:primaryType="nt:unstructured"
                sling:resourceType="wcm/commons/ui/shell/datasources/breadcrumbs"
                path="${requestPathInfo.suffix}"
                rootPath="/reports"
                rootTitle="Component Report"/>
            <clientlibs
                jcr:primaryType="nt:unstructured"
                sling:resourceType="granite/ui/components/foundation/includeclientlibs"
                categories="[coralui3,granite.ui.coral.foundation,reporting]"/>
        </head>
        <content
            jcr:primaryType="nt:unstructured"
            sling:resourceType="granite/ui/components/coral/foundation/container">
            <items jcr:primaryType="nt:unstructured">
                <reportContent
                    jcr:primaryType="nt:unstructured"
                    sling:resourceType="Reporting/components/content/report/componentReport"/>
            </items>
        </content>
    </jcr:content>
</jcr:root>
This is basically a node of type cq:Page which will open when we will click on the Reports icon from the start page.

This node can be created anywhere in your project structure. I have created this under /apps/my-project/reports.

Some key concepts here are -
  • Clientlibs - The clientlibs declaration in the head allows us to specify any clientlib categories we want to automatically include as part of our console.
  • I'm referring an HTL component using sling:resourceType just as you would anywhere else for dynamically including a component but keep in mind everything up there is leveraging Granite as a thin client to set everything up.
  • I'm using the generic "container" layout as so to include HTL component and takes the whole are in the console.

Step #3 - Creating the layout component

Now, it's time to create an HTL component which will be tied to our console. The properties of the component are below - 
<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:sling="http://sling.apache.org/jcr/sling/1.0" xmlns:cq="http://www.day.com/jcr/cq/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0"
    jcr:description="This component creates a simple form to fetch the list of components used on a page "
    jcr:primaryType="cq:Component"
    jcr:title="Component Report"
    sling:resourceSuperType="wcm/foundation/components/parbase"
    componentGroup="Reporting"/>

Step #4 - Code the component

  • In the HTML file of your component, paste the following code - 
<sly 
    data-sly-use.clientLib="/libs/granite/sightly/templates/clientlib.html" 
    data-sly-call="${clientLib.all @ categories='reporting.componentreport'}" data-sly-unwrap />

<form action="/bin/reports/component" method="post">

  <h1>Component Report</h1>

  <fieldset>
    <legend><span class="number">R</span> Basic report parameters</legend>

    <label>Report Name:*</label>
    <input type="text" id="reportName" name="reportName" required>

    <label>Search Path:</label>
    <input type="text" id="searchPath" name="searchPath">

    <label>Component Path:*</label>
    <input type="text" id="componentPath" name="componentPath" required>

    <label>Only include activated pages</label>
    <input type="checkbox" id="activated" value="true" name="isActivated">

   </fieldset>

  <button type="submit" onClick="window.location.reload();">Fetch results</button>

</form>
This is a simple HTML form in which our form action, we are referring to our backend servlet (which we will create in a later step).
  • Now, create the required clientlib folder with the following properties - 
<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:cq="http://www.day.com/jcr/cq/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0"
    jcr:primaryType="cq:ClientLibraryFolder"
    categories="reporting.componentreport"
    dependencies="[cq.jquery,cq.jquery.ui]"/>
  • Create required script.js as follows - 
$(function() {
 var components = [ "core/wcm/components/text/v2/text" ];

 $("#componentPath").autocomplete({
  source : components
 });
});
Here, components is an array which we will contain the relative paths of the components for which we want to search for in the pages. In the above array, I have put only one component but you can have as many components as you want as per your requirement.
  • Create required style.css for styling the form as follows - 
*, *:before, *:after {
 -moz-box-sizing: border-box;
 -webkit-box-sizing: border-box;
 box-sizing: border-box;
}

body {
 font-family: 'Nunito', sans-serif;
 color: #834047;
}

form {
 max-width: 300px;
 margin: 10px auto;
 padding: 10px 20px;
 background: #1f5ed64d;
 border-radius: 8px;
}

h1 {
 margin: 0 0 30px 0;
 text-align: center;
}

input[type="text"] {
 background: rgba(255, 255, 255, 0.1);
 border: none;
 font-size: 16px;
 height: auto;
 margin: 0;
 outline: 0;
 padding: 15px;
 width: 100%;
 background-color: #edf3f0;
 color: #000000;
 box-shadow: 0 1px 0 rgba(0, 0, 0, 0.03) inset;
 margin-bottom: 30px;
}

input[type="checkbox"] {
 width: 30px; /*Desired width*/
 height: 30px; /*Desired height*/
}

select {
 padding: 6px;
 height: 32px;
 border-radius: 2px;
}

button {
 padding: 19px 39px 18px 39px;
 color: #FFF;
 background-color: #000000;
 font-size: 18px;
 text-align: center;
 font-style: normal;
 border-radius: 5px;
 width: 100%;
 border-width: 1px 1px 3px;
 box-shadow: 0 -1px 0 rgba(255, 255, 255, 0.1) inset;
 margin-bottom: 10px;
}

fieldset {
 margin-bottom: 30px;
 border: none;
}

legend {
 font-size: 1.4em;
 margin-bottom: 10px;
}

label {
 display: block;
 margin-bottom: 8px;
}

label.light {
 font-weight: 300;
 display: inline;
}

.number {
 background-color: #5fcf80;
 color: #fff;
 height: 30px;
 width: 30px;
 display: inline-block;
 font-size: 0.8em;
 margin-right: 4px;
 line-height: 30px;
 text-align: center;
 text-shadow: 0 1px 0 rgba(255, 255, 255, 0.2);
 border-radius: 100%;
}

@media screen and (min-width: 480px) {
 form {
  max-width: 480px;
 }
}
Thus, we are done with the front end part of our reporting tool. Now, its time to create the backend part.

Step #5 - Create the backend Sling servlet

Now, we are going to create a backend servlet that will contain the code for reading values and it will then run a query to find out all the pages which use the specified component. After it's done finding the list of pages, it will write the data into an excel file and will download it.

Below is the code for the servlet - 
package org.redquark.aem.reporting.core.servlets;

import java.io.IOException;
import java.io.OutputStream;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import javax.jcr.RepositoryException;
import javax.jcr.Session;
import javax.jcr.query.Query;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;
import javax.jcr.query.Row;
import javax.jcr.query.RowIterator;
import javax.servlet.Servlet;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.resource.ResourceResolver;
import org.apache.sling.api.servlets.HttpConstants;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.osgi.service.component.annotations.Component;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author Anirudh Sharma
 *
 */
@Component(immediate = true, service = Servlet.class, property = { "sling.servlet.methods=" + HttpConstants.METHOD_POST,
  "sling.servlet.paths=" + "/bin/reports/component" })
public class ComponentReportServlet extends SlingAllMethodsServlet {

 // Generates serial version UID
 private static final long serialVersionUID = -7024907417541933763L;

 // Logger
 private final Logger log = LoggerFactory.getLogger(this.getClass());

 @Override
 protected void doPost(SlingHttpServletRequest request, SlingHttpServletResponse response) {

  log.info("Invoking component report servlet...");

  try {
   String reportName = request.getParameter("reportName");
   String searchPath = request.getParameter("searchPath");
   String componentPath = request.getParameter("componentPath");
   boolean isActivated = Boolean.parseBoolean(request.getParameter("isActivated"));

   if (searchPath.isEmpty() || searchPath == null) {
    searchPath = "/content";
   }

   String queryString = "SELECT * FROM [cq:PageContent] AS parent INNER JOIN [nt:unstructured] AS child ON ISDESCENDANTNODE(child,parent) WHERE ISDESCENDANTNODE(parent,["
     + searchPath + "]) AND child.[sling:resourceType]='" + componentPath + "'";

   if (isActivated) {
    queryString += " AND parent.[cq:lastReplicationAction] = 'Activate'";
   }

   // Getting the ResourceResolver from the current request
   ResourceResolver resourceResolver = request.getResourceResolver();

   // Getting the session instance by adapting ResourceResolver
   Session session = resourceResolver.adaptTo(Session.class);

   QueryManager queryManager = session.getWorkspace().getQueryManager();

   Query query = queryManager.createQuery(queryString, "JCR-SQL2");

   QueryResult queryResult = query.execute();

   RowIterator rowIterator = queryResult.getRows();

   Map<String, String> data = new TreeMap<>();

   // data.put("Page path", "Count");

   while (rowIterator.hasNext()) {
    Row row = rowIterator.nextRow();
    String path = StringUtils.substringBefore(row.getPath("child"), "jcr:content");
    Integer temp = StringUtils.isNotBlank(data.get(path)) ? Integer.parseInt(data.get(path)) : 0;
    data.put(path, (temp == 0) ? String.valueOf(1) : String.valueOf(temp + 1));
   }
   writeDataToFile(reportName, data, response);
   response.getWriter().println(data.toString());
  } catch (RepositoryException | IOException e) {
   log.error(e.getMessage(), e);
   try {
    response.getWriter().println(e.getMessage());
   } catch (IOException e1) {
    log.error(e1.getMessage());
   }
  }
 }

 private void writeDataToFile(String reportName, Map<String, String> data, SlingHttpServletResponse response) {

  // Blank workbook
  XSSFWorkbook workbook = new XSSFWorkbook();

  // Create a blank sheet
  XSSFSheet sheet = workbook.createSheet("Component data");

  Set<String> keyset = data.keySet();

  org.apache.poi.ss.usermodel.Row row = sheet.createRow(0);

  Cell pageTitle = row.createCell(0);
  pageTitle.setCellValue("Page path");

  Cell countTitle = row.createCell(1);
  countTitle.setCellValue("Number of times component appear on a page");

  int rowNum = 1;

  for (String key : keyset) {

   row = sheet.createRow(rowNum++);

   Cell pagePath = row.createCell(0);
   pagePath.setCellValue(key);

   Cell count = row.createCell(1);
   count.setCellValue(data.get(key));
  }

  try {

   response.setContentType("application/ms-excel");
   response.setHeader("Content-Disposition", "attachment; filename=" + reportName + ".xlsx");

   // Write the workbook in file system
   OutputStream out = response.getOutputStream();
   workbook.write(out);
   out.flush();
   out.close();
   workbook.close();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

}
Here, in this servlet, we are reading the values from the form data and are passing them into an SQL2 query which will be as follows -

SELECT * FROM [cq:PageContent] AS parent INNER JOIN [nt:unstructured] AS child ON ISDESCENDANTNODE(child,parent) 
WHERE ISDESCENDANTNODE(parent,[CONTENT_PATH]) AND child.[sling:resourceType]='COMPONENT_PATH' 
AND parent.[cq:lastReplicationAction] = 'Activate'
Here, CONTENT_PATH is the path of pages under which you want to search, for e.g., /content/we-retail while COMPONENT_PATH is the resourceType property of the component which we want to search. You can modify this query as per your need.

After getting the list of pages using the QueryManager API, we are using Apache POI to write the data into the excel file, which then will be downloaded. IT will look something like this -
Sample excel report

Conclusion

Pheww! This is a long post 😫 and required a lot of code and concepts but I am sure you would have enjoyed it. 

If you wish to see the complete code of this project and contribute towards the same with your suggestions and code, then you can find it on my GitHub. Please star it if you find it useful.

I hope this will help you in your projects and speed up your development.

I would love to hear your thoughts on this post and would like to have suggestions from you to make this post better. 

Feel free to befriend me on Facebook, Twitter or Linked In or say Hi by email.

Happy Learning 😊
Labels:

Comments

  1. CmasterdOctober 14, 2020 at 8:01 PM

    Have you every done anything with emailed automated reports with AEM?

    ReplyDelete

Post a Comment

Popular posts from this blog

Day 05: Working with Sling Servlets in AEM

By
AEM Developer Series Day 00: AEM Developer Series Day 01: Introduction to AEM Day 02: AEM Architecture Day 03: Setting up AEM Development Environment Day 04: Developing First OSGi Bundle Day 05: Working with Sling Servlets in AEM Day 06: Playing with Sling Post Servlet Day 07: Creating your first component in AEM Day 08: Dueling with JavaScript Use API Day 09: Dueling with Java User API Day 10: Getting to know Sling Models Day 11: Client Libraries in Action Day 12: Creating your custom OSGi Configuration Day 13: Schedulers in AEM Day 14: Eventing in AEM Day 15: Custom Workflows in AEM Day 16: Creating JMX Beans in AEM Day 17: Working with QueryBuilder API Day 18: Working with Granite Datasources in AEM Day 19: Replication API in Action Day 20: Working with Users and Groups in AEM A Servlet is a class used to extend the capabilities of servers that host applications accessed by means of a request-response programming model. For such applications, Servlet tec
6 comments
Read more

Day 00: AEM Developer Series

By
image source:  https://solutionpartners.adobe.com/ Hello everyone! Welcome to this AEM development series. We can all see the revolution of Digital Marketing today. Companies are dying to be a part of it and they have made this a war for the Digital Marketing tools. Adobe is way ahead in this war and has gained a lot of market capture. They are leaders in the Digital Marketing platforms since 2014-15. One of the flagship product in Adobe's Digital Marketing suite is Adobe Experience Manager (AEM) . Since AEM is in huge demand, the people who know how to develop on AEM are also in huge demand. But developing on AEM is not easy as it is made up of various open-source technologies such as Apache Felix (OSGi), Apache Sling, Apache Oak and Adobe's own technologies like Granite, HTL etc. Learning all these technologies in conjunction can sometimes become confusing and frustrating 😫. When I first started learning AEM in 2016, I was dumbfounded to see there is
24 comments
Read more

Day 12: Creating your Custom OSGi configuration

By
AEM Developer Series Day 00: AEM Developer Series Day 01: Introduction to AEM Day 02: AEM Architecture Day 03: Setting up AEM Development Environment Day 04: Developing First OSGi Bundle Day 05: Working with Sling Servlets in AEM Day 06: Playing with Sling Post Servlet Day 07: Creating your first component in AEM Day 08: Dueling with JavaScript Use API Day 09: Dueling with Java User API Day 10: Getting to know Sling Models Day 11: Client Libraries in Action Day 12: Creating your custom OSGi Configuration Day 13: Schedulers in AEM Day 14: Eventing in AEM Day 15: Custom Workflows in AEM Day 16: Creating JMX Beans in AEM Day 17: Working with QueryBuilder API Day 18: Working with Granite Datasources in AEM Day 19: Replication API in Action Day 20: Working with Users and Groups in AEM We all know that AEM works on Apache Felix  which is an implementation of OSGi . OSGi provides a way to manage bundles and configurations. We saw one example of an OSGi configurati
5 comments
Read more