JasperReports Tutorial: Dynamic Drill-Down Reports with MongoDB (Part 1 of 2) - ...

:

In this article, I will demonstrate by means of a step-by-step tutorial, how one can create an interactive report, using only the community editions (i.e. open source versions) of the JasperReports tools without having to learn the intricacies of how to query the Big Data-storage at hand.

Let’s get our hands dirty!

Not another ‘Hello World’, PLEASE!

Don’t worry.

We are going to create a report on a survey conducted in January ’13 by Princeton Survey Research Associates International (PSRAI), questioning US-Americans as to their usage of mobile phones while shopping during the holiday shopping season of 2012 (‘holiday season’, for us Europeans, is the politically correct expression for ‘Christmas season’).

To make things interesting, this report will feature dynamic drill-downs so that we will be able to filter the results shown depending on the answers given by the respondents. Filtering will be accomplished by the user clicking into the corresponding pie of a pie-chart. Here are a few screenshots showing what the resulting report will look like:

Screen 1

Screen 2

Screen 3

All three screenshots depict the answers to the same question; however, the last two screens show the answers depending on whether the respondent was male or currently not employed.

Ingredients

First things first; you’ll need the following software components installed and running in order to follow the tutorial:

  • MongoDB.
    We want to explore JasperReports connectivity to Big Data, after all. MongoDB is, in my opinion, one of those Big Data storages that are easier to get started with than the others available.
    You can download a version for your operating system of choice directly from their web site http://www.mongodb.org and follow these instructstions to install http://docs.mongodb.org/manual/installation/ (don’t worry, it’s easy).
    I employed version 2.4.3 while writing this tutorial.
  • JasperReports Server and iReport Designer
    Since our report features dynamic drill-down we need an environment where this can be executed – the easiest way is to use JasperReports Server. Of course, it is possible to implement this execution environment yourself using just the JasperReports library which features a bare-bone servlet for report presentation to get you started. That however is beyond the scope of this tutorial.
    The iReport Designer is essential in efficiently creating reports – even though the reports are written in XML. Directly working with this XML is beyond the scope of this tutorial. Please note that JasperSoft (the company behind JasperReports) is trying to place Jaspersoft Studio as the primary means for creating reports; in my opinion that software is not as robust and feature complete as iReport yet, which is why we will stick to iReport.
    You can access all required packages from the community download page http://community.jaspersoft.com/download.
    I employed version 5.1.0 of each package.

After you have installed (I chose to use the defaults wherever possible, keeping the installation as simple as possible) and started the software, we will continue by importing the data into the MongoDB instance.

HowTo

Step 1: The data

The raw data of the survey can be accessed on the pages of the Pew Research Center’s Internet & American Life Project:

http://www.pewinternet.org/Shared-Content/Data-Sets/2013/January-2013–Mobile-Shopping-%28omnibus%29.aspx

The data is available in CSV format, which you can import into MongoDB using the command mongoimport.

However, you’ll need to rework the data a bit because it is provided so as to be put directly into a table-based RDBMS – which we don’t want.

Therefore and for your convenience, I have provided a dump of the data I used which you can download from here (in 7-Zip-format):

https://public.centerdevice.de/62d2696d-5a37-4976-a422-c68ad30d03f5

After you have extraced it (will create directories dump/test) you can import this into your MongoDB instance using the following command
mongorestore <path to dump-directory>
which will restore a collection called ‘omnibus’ in the db ‘test’ of the MongoDB instance accessible on localhost using the default port 27017. If you do not want that to happen and, for example, use another db or collection, you’ll have to provide the corresponding parameters.

Please see http://docs.mongodb.org/manual/reference/program/mongorestore/ for further details.

Let’s have a look at the data: open a shell to your MongoDB with the command mongo and issue the command db.omnibus.find().limit(5). The resulting output should look like this:

> db.omnibus.find().limit(5)

{ "_id" : ObjectId("51936e78bf24cbed9348fe83"), "respondent_id" : 100002, "qnr" : 0, "question" : "Do you have a cell phone?", "anr" : 2, "answer" : "No" }

{ "_id" : ObjectId("51936e78bf24cbed9348fe84"), "respondent_id" : 100002, "qnr" : 1, "question" : "Do you have a handheld device made primarily for e-book reading, such as a Nook or Kindle e-reader?", "anr" : 2, "answer" : "No" }

{ "_id" : ObjectId("51936e78bf24cbed9348fe85"), "respondent_id" : 100002, "qnr" : 2, "question" : "Do you have a tablet computer such as an iPad, Samsung Galaxy Tab, Google Nexus or Kindle Fire?", "anr" : 2, "answer" : "No" }

{ "_id" : ObjectId("51936e78bf24cbed9348fe86"), "respondent_id" : 100002, "qnr" : 3, "question" : "Do you have an iPod or other MP3 player?", "anr" : 2, "answer" : "No" }

{ "_id" : ObjectId("51936e78bf24cbed9348fe87"), "respondent_id" : 100002, "qnr" : 4, "question" : "Do you have a game console like an Xbox or Play Station?", "anr" : 2, "answer" :"No" }

As you can see, the data is very structured with all the documents having the same one:

  • _id: the unique identifier of the document. Generated by MongoDB.
  • respondent_id: the ID of the respondent giving the answers.
  • qnr: the number of the question.
  • question: the question itself.
  • anr: the number of the answer the respondent gave to the question.
  • answer: the answer that the respondent gave to the question.

I’ll admit that the example is contrived: in real life, you would store the survey data in a table-based RDBMS and never worry about Big Data. The available questions and answers for each would be stored in separate tables and you would link the respondent ID to the answer ID given for each available question ID.

However, what if you had different surveys that featured some similar questions and you needed to analyze them together? How would you go about that with a table-based RDBMS? Not so easy, isn’t it?

With a document based system like MongoDB it becomes easy: you pick the fields that are available in most collections and copy that data into a new collection (no joins in MongoDB) and then start to extract information.

The point to make is that with NoSQL-systems you get a flexibility that is not available with the same kind of ease in traditional table-based RDBMS, yet. In general, you have to carefully design your data model upfront and even then you run the risk of having to invest a lot of work if circumstances change in unexpected ways. Not so with NoSQL, where you have less to worry about to get things started.

On a side note, you can access more background information and interpretation of the survey data on the Pew Internet site at http://www.pewinternet.org/Reports/2013/in-store-mobile-commerce.aspx.

Step 2: The report

Now that we have the data available, we can create the reports; you actually need two to make drill-down available.
Let’s continue by starting iReport Designer (MongoDB should be kept running).

Step 2.1: The datasource

In general, the first step in creating a JasperReport with iReport is creating and/or selecting a datasource to use.
Click on the “Report datasource”-button (marked by red borders in the screenshot):

Screen 4

This opens a dialogue -window in which you can modify and select one of the available datasources, delete one or create a new one.

Click on the “New” button to do the latter.

Select “MongoDB Connection” from the list of available connection types and click on “Next”.

In the following dialogue you have to provide a name for the datasource as well as the actual connection data. If, during the installation of MongoDB, you chose to use the defaults (like me), all you have to enter for connection data is host (i.e. ‘localhost’) and db-name (i.e. ‘test’):

Screen 5

Hit “Test” to check if the connection works and then “Save” to store the datasource. Return to the main screen and make sure that the datasource you just created is the active one:
Screen 6

Step 2.2: The query

With the datasource available, we can begin creating the report.
Select “File” -> “New” and choose “Launch Report Wizard” in the “Report” subsection in the following dialogue:

Screen 7

In the next screen you need to enter a name for your report (I used “omnibus_main”) and select the location where it should be stored. Do that and hit “Next”.

Now, it gets interesting: we need to provide the query with which JasperReports is to obtain the data.

In case of MongoDB, the query language to use is the Jaspersoft MongoDB Query Language, which is a JSON-style declarative language; you can find all the details here:

http://community.jaspersoft.com/wiki/jaspersoft-mongodb-query-language

The resulting queries look almost identical to the queries one would write in the Javascript shell of MongoDB. Because I want to demonstrate how JasperReports can help if one is not an expert in the query language of the given storage system, I will not get into too much detail.

We only need to define one mandatory parameter, collectionName, and that is exactly what we’ll do:

Screen 8

A query like this results in all the documents of the specified collection to be returned. In our example, that is not a problem because our collection is small. However, it is called Big Data for a reason and even though JasperReports Server is capable of handling terabytes of data, it will require resources to do that. Besides, when designing a report with iReport you want to preview the report regularly to check if all the components fit into place as desired and then you do not want to wait too long for the preview to be created.

Therefore, even though we will not be using it, I strongly suggest keeping the following additional query-parameter in mind:

limit: this limits the amount of documents returned by the query

Click “Next” to proceed.

Now iReport executes the query and scans the documents returned by it for the fields contained in each. This is necessary, because the structure of the documents in a MongoDB collection can be very different from document to document. iReport makes the fields found available to you to work with.

By default, iReport scans only the first 5 documents returned by the query. If you have a collection with very disparate documents that amount may not be enough. Therefore, JasperReports offers an additional query-parameter that is not available in MongoDB:

rowsToProcess: sets the number of rows that will be processed to determine the list of fields.

In our case, the results of the scan are as follows:

Screen 9

We will be working with all the fields so click “>>” to select them all followed by “Next” to continue.

This next screen allows us to group the data by one or more fields (up to 4). We want to group our data by the questions and corresponding answers given by each respondent. Therefore, select question as Group 1 and answer as Group 2:

Screen 10

Click “Next” followed by “Finish” to complete the wizard.

Step 2.3: The report (this time for real)

Behold, your first JasperReport connecting to MongoDB!

It is fully functional as you can check out by clicking the “Preview”-button in the report view:

Screen 11

Of course, since we haven’t placed anything on the report yet, it will be blank. Even so, the preview will take some noticeable time to generate and will be more than 3000 pages long!

The reason lies in the band-based design of JasperReports: each band follows a specific function. For example, the detail band (marked red in the following screen shot) works on each row of the query’s resultset and, if it is present, causes something to be print for each one. By ‘something’ I mean that which was designed for the detail band in the design view; JasperReports tries to honor the space the detail band takes up in the design view when generating the report – based on our current design, around 4 detail bands fit on one page, so we get the more than 3000 pages, but not 15868 which would be the total size of our collection.

You can see what is happening by dragging one of the fields unto the detail band (marked green) and hitting “Preview” again:

Screen 12

The resulting report should look like this:

Screen 13

Now, let’s get rid of the detail band (and some others as well)!

For our report, we are mainly interested in presenting the grouped data and the other bands will only get in the way. Therefore, right-click on each of the following bands in the Report Inspector and select “Delete Band”:

  • Column Header
  • Detail 1
  • Column Footer
  • Summary

Correspondingly, right-click on each of the following bands in the Report Inspector and select “Add Band”:

  • question Group Header 1
  • answer Group Footer 1
  • question Group Footer 1

The resulting report should look like this:

Screen 14

If you haven’t done so already, now’s the time to save your work.

We will now work our way from the title-band down to the page footer.

“Title”-band

This band contains two labels. If you haven’t done so, open the palette by selecting “Window” -> “Palette”. This brings up a menu with all available report elements. Select the element “Static Text” and then drag two of it unto the title band. Now, if the properties window is not open, select “Window” -> “Properties” to open it:

Screen 15

The first label should have the following properties set:

  • Text: “In-store Mobile Commerce During the 2012 Holiday Shopping Season”
  • Size: 26
  • Bold: true
  • Horizontal Alignment: Center

The second one the following properties:

  • Text: “This omnibus survey contains questions about device ownership and cell phone usage during purchase decisions.”
  • Horizontal Alignment: Center

You will need to resize and rearrange the two label elements to fit them unto the report as desired. Also, you will find that the orientation of the report needs to be changed, because the heading will not fit entirely into the band.

This can be accomplished by right-clicking on the report-name in the Report Inspector (‘omnibus_main’), selecting “Properties” and changing the orientation to “Landscape”:

Screen 16

After that, your title band should look like this:

Screen 17

When you hit “Preview”, you will see that this title is only printed on the first page.

“Page Header”-band

This band only contains one label, with the following properties:

  • Text: “In-store Mobile Commerce During the 2012 Holiday Shopping Season”
  • Size: 16
  • Bold: true

The resulting band should look like this:

Screen 18

However, when you preview the report, you will find that the first page will also contain this header:

Screen 19

Looks odd, doesn’t it?

Fortunately, this can be remedied by using an expression to prevent the page header to be rendered on the first page.

In the Report Inspector, select “Page Header” and then click the button marked “…” in the property named “Print When Expression” to open the expression editor:

Screen 20

Our goal is to prevent the page header from being print on the first page. Luckily for us, JasperReports provides a set of variables that allow us to access the state of the report execution. Among others, the current page number is accessible.

In the expression editor select “Variables” from the list of available categories and then double-click the variable named “PAGE_NUMBER”. By default, JasperReports expressions are written in Groovy (can be changed to Java or JavaScript) and generally need to return a boolean value (true/false).

On the first page, the variable “PAGE_NUMBER” will have the value 1. So, to prevent the page header from being print on the first page, we change our expression to evaluate to true when the page number is greater than 1:

Screen 21

Click “OK”, save your work and hit “Preview” to check if that change worked; the first page of the report should now look like this:

Screen 22

While the subsequent pages should look thus:

Screen 23

“question Group Header 1”-band

Finally, we actually get to show some data (and not just static text)!

This band consists of a vertical line functioning as a separator and a text field presenting the current question.

First, select the element called “Line” from the palette and drag it into the band. Then lengthen it so that it encompasses the whole band width.

Next, select the element called “Text Field” from the palette and place it beneath the line. Its properties should be configured as follows:

  • Text Field Expression: use the expression editor to print the text ‘Question: ‘, followed by the value of the field ‘question’ (“Question: “+ $F{question}).
  • Stretch With Overflow: true. This ensures, that the text will be printed even if it does not fit into the box.
  • Size: 14
  • Bold: true

The resulting band should look like this:

Screen 24

If you preview your report, you should get something like this (5000+ pages!):

Screen 25

First of all, we want each question to be printed on a separate page.

In the Report Inspector, select “question Group Header 1” and then enable the property named “Start on new page”:

Screen 26

By the way, as you can see, you can always edit the expression used to calculate the group using the property “Group Expression”.

The preview should now show each question on a new page. However, it should also be around 15000 pages long!

What’s wrong here? It looks like grouping is not working at all!

Yes and no – yes, it apparently does not work, but no, it’s not because the grouping feature is broken.

The reason is our query: currently it is returning the documents sorted by respondent_id because that is the order the data is stored in the collection. This means that JasperReports gets to see all questions for respondent A before seeing the questions for respondent B and so on – under that circumstance, JasperReport cannot group the data by question and answer.

Therefore, we need to change the order the documents are returned by the query. This can be accomplished using the sort query-parameter:

sort: Specifies the fields and order of each one of the them that will be used to sort; 1 for natural order, -1 for reverse order.

In the Report Inspector, right-click on the report name and select “Edit Query” to open the query editor. Then, change the query to sort the documents first by question number followed by the answer number:

Screen 27

If we now generate a new preview, we will get the desired result, that is: 17 pages for 17 questions in all.

“answer Group Footer 1”-band

This band contains two text fields to show the answers given for each question and the corresponding count for each.

The first text field should have the following properties set:

  • Forecolor: [0,153,153]
  • Text Field Expression: $F{answer}+”: “
  • Stretch With Overflow: true
  • Size: 12
  • Bold: true
  • Horizontal Alignment: Right

The second one, which is to be located right beside the first, should have the following properties set:

  • Text Field Expression: $V{answer_COUNT}. JasperReports provides a COUNT-variable for each group defined holding the count of elements in that particular group.
  • Size: 12

When you create a preview, it should look thus:

Screen 28

Screen 29

We are getting close….but the biggest part is yet to come.

“question Group Footer 1”-

Finally, the pie chart!

Grab the “Chart” element from the palette and drag it into the band. This opens a dialogue letting you chose from various diagram types. We want a simple, flat pie chart which is incidentally the first option available. Select it and press “OK” to continue.

In the following wizard we need to provide the data the pie chart will use to render itself. First, we need to specify the dataset, which should be “Main report dataset”; this will almost always be the same, the only exception being subreports which can specify their own datasets:

Screen 30

Click “Next” to continue.

Now we need to provide a unique identifier to be used for each pie slice as well as the corresponding numeric value. This wizard page may be sized too small so that you won’t see the corresponding fields – if so, resize the dialogue window until you can see the fields.

Since we want to show the statistics of the answers given for each question, we select the answer-field as the unique identifier and the answer_COUNT-variable as the numeric value for each slice:

Screen 31

Click “Next” followed by “Finish” to close the wizard.

You’ll probably need to resize the pie chart, after which your band should look like this:

Screen 32

And the preview report like…. what is this? That’s not right:

Screen 33

I’ve highlighted the wrong values with red borders.

The problem is that currently the pie chart does not know that it is rendering different groups. This causes all group values to be added on each subsequent page resulting in the following “thing” on the last page:

Screen 34

To remedy this problem, right-click into the pie chart and select “Chart Data”.

In the tab “Dataset” of the following dialogue, select “group” under “Reset type” and “question” under “Reset group”:

Screen 35

Save your work and recreate the preview.

I’ll skip a screenshot this time, because we are very close to the finish line now.

“Page Footer”-band

Almost there!

This band is supposed to contain a text showing the current page and the total number of pages in the style “Page X of Y”.

For convenience, iReport provides a predefined element to render such a text: it is located in the palette in the section called “Tools” and is named… “Page X of Y” (what did you expect?)

Drag it into the band at the rightmost position and…

Screen 36
Screen 37

… we are done!

You will probably need to fiddle around a bit with the positions and sizes of the various bands and elements to get each question, its answers and the pie chart to fit on one page (actually, there are two questions that have so many possible answers that the components don’t fit completely on one page; that’s why my report has 19 pages instead of 17).

(Don’t forget to save your work).

Not done! No sir!

That’s right; I said that the report will feature drill-downs to filter the answers which will be executed on JasperReports Server.
On to part two!