Posted in Workflow Automation

How to Create Custom Reports With Form Data Using Banana

Data Visualization is incredibly powerful.

Think about a corporate performance report. Connecticut, South Carolina, Minnesota were the Top 3 performs. Wyoming, New Hampshire and New York were the Bottom 3. There were X, Y and Z trends. Revenue and profit grew A% and B% respectively. You could go on and on for pages.

Or, you could show charts like this.

Some chart options available with Banana.

It wouldn’t surprise me if this is the first sentence you read in this article. That’s because the visualization immediately captures our attention. If you’ve automated workflows with frevvo, your data is already digital. Data visualization is the best way to present your data.

Analysis and Optimization Reports

In version 7.0, we introduced reporting on submissions. However, we designed these reports for workflow analysis and optimization of your operations.

They included dashboards with pre-defined charts and tables that allowed administrators to monitor forms, and workflows. A submissions report also allows you to search for submissions using configured searchable form fields. [Update: In, Version 9 we upgraded this reporting so it’s now accessible to more users.]

To be sure, this type of workflow analysis software is crucial to improving efficiency, customer satisfaction and the competitiveness of the business itself.

Reporting on Business Data

What if you needed to create your own operational report or one that uses the specific data from your form or workflow? Since frevvo doesn’t directly provide this functionality at present, you would need to rely on a third party system such as Google Data Studio.

It’s a powerful option; however, it means that you must send the submission data to an external data source such as a Google Sheet or a SQL database. To do that, you have to use either a document action or CSV export.

However, none of these extracts provide all the data. While a document action provides form data but not the metadata that you see in operational reports, the CSV export provides form data and only the basic metadata.

Enter Banana!

Fortunately there is a solution. Under the covers, frevvo uses Solr (Insight Server) to store and index submission data. We can access all that data by installing a third party reporting product called Banana in frevvo.

But, don’t be fooled by the odd name. Banana has an Apache license, which means it’s free even for commercial use. It allows you create custom dashboards over the data stored in Solr (insight server).

This documentation describes the generic installation steps but let’s apply them to frevvo.

Install the Dashboard Builder in frevvo

  1. Use this link to download the latest release of Banana.
  2. Using the Insight Server documentation for your frevvo installation, navigate to the frevvo/solr-x.x.x/server/solr-webapp/webapp directory.
  3. Extract the contents of the zip file into a directory named ‘banana’.
  4. Start the insight server and browse (assuming default location) http://<server-name>:8983/solr/banana/src/index.html. This should display the default dashboard. Replace <server-name> with the hostname of the server on which you have installed frevvo .
  5. In order to save custom dashboards inside the server, create a core by visiting this URL in the browser: http://<server-name>:8983/solr/admin/cores?action=CREATE&name=banana-int&configSet=frevvo_submission

Create the Data

Let’s create a form so we get some data that we can then visualize on a dashboard. Instead of creating one from scratch, we just download the simple purchase order process form from the frevvo website.

Create a new application in frevvo and upload this flow to it. Add Item and Subtotal as searchable fields. Run this flow a few times to create sufficient data. While a purchase order can be about any product, I chose office supplies for this demonstration. My office supplies consists of Coffee filter, Ballpoint pen, Note pad, Photocopy paper, and Trash bag. It’s no accident that it all starts with the coffee filter and ends in the trash bag 🙂

PO

Test the flow a few times, each time entering various combination of items in the order information table and then saving the form.

At this point, we have the data, and we want to know which items are more popular than the others. Before we build that dashboard, here’s a quick reminder that a simple workflow solution like frevvo is easy to use and comes with many powerful and important capabilities.

Build the Dashboard

1. Browse to the same URL from Step 4 of the installation process (http://<server-name>:8983/solr/banana/src/index.html) and click the ‘New’ icon. Select ‘Non time-series dashboard’.
2. In the dialog that pops up, select your tenant name in the ‘collection’ field.  Click Create. You will get a dashboard listing the fields and a page of submissions.

Banana Dashboard - Select Tenant

3. These are all the submissions in the tenant and we want to filter those that belong to the PO flow. The ‘typeid’ field is what we need to filter on. So let’s check the ‘typeid’ field and also the ‘name’ field as that provides the actual flow name.
4. Click on any row with name field as ‘Basic Purchase Order Process’. Click on the magnifying glass icon against the typeid field as shown below.

Banana Dashboard - Select Field

5. We now see only the PO flow submissions. We want to count occurrences of item name. As the sample above shows, the field name is ‘d_T22MpsF2EeaqVK_SBf5hPA_ss’. This is a generated name and yours will be different (these are some of the unpleasant details that frevvo shields you from but this is where the training wheels come off). Let’s check that field as well in the field list.

6. Now for some visualization. Click on the plus sign next to the filter panel in the 2nd row and a dialog opens up. In the ‘General’ table give the panel a title. In the ‘Add Panel’ tab, Choose ‘bar’ as the panel type. More fields appear. Type the item name field (the generated name from above). Click ‘Add Panel’ and voila!

Banana Dashboard - Add Panel

7. If the chart appears scrunched, you can increase the height (in pixels). Now click on the plus sign and add another panel. This time we pick the panel type as ‘terms’. More fields appear and we choose style as ‘pie’. Pie is good … but donuts are better! So let’s check the donut box. Type the item name field (generated name), and click ‘Add Panel’.

Banana Dashboard - Add Donut.

8. Click the save icon on the toolbar and a dialog pops up. Give the dashboard a name and click the save icon next to it. The dashboard refreshes with the new name on the tool bar. You can now use the load icon on the toolbar to access this dashboard by name.

Final Thoughts

That was a whirlwind tour of some of the features of Banana. There are many other charts as you probably noticed in the panel type list. The Banana product documentation describes their usage.

Limitations

What if we wanted to ask a different question – what is the distribution of items by cost? We see the item field (d_T22MpsF2EeaqVK_SBf5hPA_ss) and the subtotal field (d_W5zu59mpEeWNW4YwpNdrog_ds) in the figure above, there is no correlation between the values. So we cannot answer this question without changing the structure of the data. The solution is to create a child document for each row in the Order Information table, rather than a multi-valued field for each column as we have today.

Unfortunately, this requires an enhancement to frevvo. The enhancement will not only allow for more detailed analysis but also bring the submission view export in line with the legacy submission view export. Talk about knocking out two features with one banana!

Eventually, we could even provide frevvo with Banana pre-installed and ready to go. We’re working on that – stay tuned for more information. In the meantime, we’d love to show you everything you can do today with frevvo.

Request a demo.