Reporting capabilities in Activiti 5.12

In the Activiti 5.12 release, we added reporting capabilities on top of the Activiti engine, demonstrating the concepts through the Activiti Explorer web application (but of course usable everywhere).

Now, don’t be fooled: since a very long time , the Activiti engine has the capability of gathering historical or audit data when you execute business processes. All this data is stored in the historical database tables and can thus be easily queried. Which means that any reporting tool such as JasperReports, Birt, Crystal Reports etc. can just use the historical tables as a datasource to produce reports in any format you’d like (Word, PDF, …) to get insight how your business is executing its business processes. I’ll probably blog such an example pretty soon.

Eating our own dogfood

But the thing where I’d like to focus on today is the web side of things: web charts/reports which can be combined into a dashboard for example. The first thing we must be able to do is to expose the historical data in a way we can use it to create these charts. But where do you put the logic (the queries and data manipulation) to generate a dataset for the chart and/or the report? Do you embed the SQL in your UI-layer? Of course not. What if multiple applications want to use the data? What if we want to store the generated dataset to get a snapshot of the data at a certain point in time.

When we thought about this problem we first though about it in the traditional way. A new service with reporting capabilities, probably using some sort of DSL to define the dataset generation which are stored in some kind of data store. Anyway, a whole new things and concepts to learn and master. Not to mention extra implementation and maintenance.

But then it hit us. Everything we needed is already available in the Activiti engine. If we use a process to define the logic to create the dataset for the report, we can leverage all the facilities of the engine. The only requirement for this process is that it generates the JSON data which follows a fixed format. Some benefits

  • The process has straight access to the internals of the Activiti engine. It has direct access to the database used by the engine.
  • The dataset that is generated can be stored in the historical tables of Activiti if wanted. So we have a ‘save report data’ mechanism for free.
  • The job executor can be used as for any other process. This means that you can asynchronously generate the process or only execute certain steps asynchronously. It also means you can use timers, eg. to generate the report data on certain points in time.
  • Creating a new report can be done with known tools and known concepts. Also, no new concepts, services or applications are needed. Deploying or uploading a new report is the same as deploying a new process. Generating a report is the same as running a process instance.
  • It allows to use the BPMN 2.0 constructs. This means that all things like parallel steps, do branching based on data or even request user input during the generation are possible out-of-the-box.

Screen Shot 2013-03-22 at 13.22.49

A dash of Javascript

Since the generation of the dataset is done by a process, everything possible in a process can be used. So you can use Java delegate classes or whatever you fancy.

But since the kool kids nowadays are using Javascript, we added some example process to the demo data of Activiti Explorer that use the scripting functionality in the engine. The nice thing about Javascript is that JSON is native to it, and creating a JSON object is really easy. As said above, the only requirement for such a process is that it must generate the JSON dataset following the predefined format.

Screen Shot 2013-03-22 at 10.43.20

For example to generate an overview of all past process instances we could have a process like this:


<?xml version="1.0" encoding="UTF-8"?>
<definitions xmlns="http://www.omg.org/spec/BPMN/20100524/MODEL"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:activiti="http://activiti.org/bpmn"
 xmlns:bpmndi="http://www.omg.org/spec/BPMN/20100524/DI" xmlns:omgdc="http://www.omg.org/spec/DD/20100524/DC"
 xmlns:omgdi="http://www.omg.org/spec/DD/20100524/DI" typeLanguage="http://www.w3.org/2001/XMLSchema"
 expressionLanguage="http://www.w3.org/1999/XPath"
 targetNamespace="activiti-report">

<process id="process-instance-overview-report" name="Process Instance Overview" isExecutable="true">

 <startEvent id="startevent1" name="Start" />
 <sequenceFlow id="flow1" sourceRef="startevent1" targetRef="generateDataset" />

 <scriptTask id="generateDataset" name="Execute script" scriptFormat="JavaScript" activiti:autoStoreVariables="false">
 <script><![CDATA[

  importPackage(java.sql);
  importPackage(java.lang);
  importPackage(org.activiti.explorer.reporting);

  var result = ReportingUtil.executeSelectSqlQuery("SELECT PD.NAME_, PD.VERSION_ , count(*) FROM ACT_HI_PROCINST PI
       inner join ACT_RE_PROCDEF PD on PI.PROC_DEF_ID_ = PD.ID_ group by PROC_DEF_ID_");

  var reportData = {};
  reportData.datasets = [];
  
  // Native json usage
  var dataset = {};
  dataset.type = "pieChart";
  dataset.description = "Process instance overview (" + new java.util.Date() + ")";
  dataset.data = {};

  while (result.next()) { // process results one row at a time
    var name = result.getString(1);
    var version = result.getLong(2)
    var count = result.getLong(3);
    dataset.data[name + " (v" + version + ")"] = count;
  }
  reportData.datasets.push(dataset);

  // Storing the json as process variable
  execution.setVariable("reportData", new java.lang.String(JSON.stringify(reportData)).getBytes("UTF-8"));
 ]]></script>
 </scriptTask>
 <sequenceFlow id="flow3" sourceRef="generateDataset" targetRef="theEnd" />

 <endEvent id="theEnd" />

 </process></em>

</definitions>

The script is pretty easy to understand. All it does is querying the database, creating a json object with the data and storing it as process variable. Since it is stored as a variable, it is basically a snapshot of the dataset. So at any time in the future you can just fetch the json data and look at the report as it was at that point in time.

The json produced by the process above can then easily be used to generate charts, as demonstrated by the Activiti Explorer application:

Screen Shot 2013-03-22 at 13.30.45

 

It is also very easy to see how easy it is to create a dashboard app with the same approach. But that’ll be for a next blogpost.

Thanks for reading!

 

 

9 Comments

  1. Henry Yan March 22, 2013

    So cool, I’ll try do it, thanks.

  2. Florent Puech June 14, 2013

    Hi!
    I tried to make some charts but i encountered some problems. I would like to create two charts in the same process. In fact, my process is working well for the first launching of my tomcat. But if i try to launch it again, the second chart seems to be in front of the first.
    Please can you tell me if you know how to fix this problem.

    Here is the code of my process :

    importPackage(java.sql);
    importPackage(java.lang);
    importPackage(org.activiti.explorer.reporting);

    var task = execution.getVariable(“task”);

    var result = ReportingUtil.executeSelectSqlQuery(“select ASSIGNEE_, avg(DURATION_), COUNT(DURATION_) from ACT_HI_TASKINST where NAME_ = ‘” + task.getName() + “‘ and DURATION_ > 0 and END_TIME_ is not null group by ASSIGNEE_”);
    var nberror = ReportingUtil.executeSelectSqlQuery(“select COUNT(DURATION_) from ACT_HI_TASKINST where NAME_ = ‘” + task.getName() + “‘ and DURATION_ <= 0 “);

    var nbexec = ReportingUtil.executeSelectSqlQuery(“select ASSIGNEE_, COUNT(DURATION_) from ACT_HI_TASKINST where NAME_ = ‘” + task.getName() + “‘ and DURATION_ > 0 and END_TIME_ is not null group by ASSIGNEE_”);

    var reportData = {};
    reportData.datasets = [];

    var datasetbis = {};
    datasetbis.type = “pieChart”;
    datasetbis.description = “Nombres d’execution faites par chaque utilisateur”;
    datasetbis.data = {};

    while (nbexec.next()) {
    // process results one row at a time
    var namebis = nbexec.getString(1);
    var valbis = nbexec.getLong(2);
    datasetbis.data[namebis] = valbis;
    }
    reportData.datasets.push(datasetbis);

    var dataset = {};
    dataset.type = “barChart”;
    dataset.data = {};

    while (nberror.next()) {
    dataset.description = “Average duration (in seconds) for task ” + task.getName() + “. Nombres de taches en erreur : ” + nberror.getLong(1);
    }

    while (result.next()) {
    // process results one row at a time
    var name = result.getString(1);
    var val = result.getLong(2)
    var nb = result.getLong(3);
    dataset.data[name] = val/nb;
    }

    reportData.datasets.push(dataset);

    execution.setVariable(“reportData”, new java.lang.String(JSON.stringify(reportData)).getBytes(“UTF-8”));

    Thanks in advance for your help.

    Florent Puech

  3. Joram Barrez June 17, 2013

    Isn’t this related to the database results coming back in a different order?

    Does the json at the end also has the stuff switched?

  4. Florent Puech June 17, 2013

    It doesn’t seem to be this because, even if i put random data, without the call to the database, the problem still exist.

    Moreover, I have another question.
    At the beginning of the process, I would like to first ask for a process by the type “processDefinition” in a form and after ask for a task which is on the process selected. Can I configure the second list thanks to a parameter recuperate from the first form.

    Sorry for my english.

    Thanks again

  5. Joram Barrez June 18, 2013

    That sounds really strange (and like a bug)… in the explorer there is a report which has a chart and a list … but I never saw it being switched places.

    For your other question: process definition type is already in Explorer, but the task selection is not. You’ll need to write your own form type (see http://www.jorambarrez.be/blog/2013/03/13/creating-a-new-form-property-in-activiti/) which allows you to do that.

  6. Florent Puech June 18, 2013

    Thanks.

    I already did my own form type for a task.
    But, I list, all the tasks present in the database.
    I would like to show tasks from a process selected earlier thanks to the process definition type.
    Maybe, I could use “variable” in the second formproperty in order to pass the processId and use it but I don’t know how I can recuperate it in my taskformpropertyrenderer.

  7. Joram Barrez June 21, 2013

    Well it depends where you want to show that task picker:
    – in the same form: then you’ll probably need to create a composed form type that allows to select process and task altogther so data can be stored
    – different forms: store it as a variable. I believe it is the default of form properties anyway to be stored.

  8. Adam Young February 25, 2015

    Hi Joram,

    I am working with the Alfresco “enterprise” version of Activiti to create a custom report in its web app, “activiti-app”. I believe your example above relates only to building a report in the “community” version’s activiti-explorer app.

    Am I right about that, and if so, do you know how to create a report like this in the Alfresco enterprise version?

    Thank you so much for any guidance you can provide!

    Best wishes,
    Adam

  9. Joram Barrez March 2, 2015

    @Admin: indeed this is only the community version. The report creation tool in BPM Suite is built on top of ElasticSearch. But we haven’t documented it yet, nor build out functionality to expose the new reports (that’s on the roadmap). If you got questions, please go and ask a question on the Activiti forum (http://forums.activiti.org/), as that’s the better place to discuss these things.

Leave a Reply

Your email address will not be published. Required fields are marked *