Right Outer Join

3 February 2012

Collections in reports on MongoDB

Filed under: iReport, JasperReports — Tags: , , , — mdahlman @ 15:25

Collections in Collections

MongoDB stores data in collections which correspond to tables in relational databases. These collections hold data like Strings and Numbers and… Collections. These Collections are generally Arrays. (Always Arrays?) A classic example is orders which have order_line_items. In a normalized relational database you would have one table order_line_items with a foreign key pointing to the table orders. A query might look like this:

     select o.order_id, o.customer_id, oli.order_line_item_id, oli.product_name, oli.quantity
     from orders o
          inner join order_line_item oli on ( oli.order_id = o.order_id )

The result of this query will be a tabular result set which JasperReports or any other report engine will have no trouble processing.

But that same data set is likely to modeled in MongoDB as single collection called orders. Each record will have an order_id, a customer_id, and an array of order_line_items. For many purposes this is ideal. A program can retrieve a few orders, it can iterate through the order_line_items, and it can process them however it likes. But this handling of Arrays within other records poses some pitfalls for many standard reporting scenarios.

Start Simple

In the simplest case, the Arrays have N elements where N is known at design time. Think of the field ‘location’ storing longitude and latitude. We know that the Array ‘location’ will always contain two elements. We know ‘location[0]’ can be treated as the longitude, ‘location[1]’ is the latitude, and we can safely ignore ‘location[17]’ in all of our reports.

The Jaspersoft MongoDB connector handles this case fantastically well. Consider this example.

     db.orders.save({
       "id" : "1001",
       "cust" : "abc",
       "lines" : [
         { "line number" : "line1", "product" : "ProdA" },
         { "line number" : "line2", "product" : "ProdB" }
       ]
     })
MongoDB Query Editor

The fields are automatically determined by the "Fields Provider" mechanism.

I add a few orders following the pattern shown above. Then I query them using the simplest possible query in the iReport query designer:

     { 'collectionName' : 'orders' }

You can see that iReport does a bunch of work for me automatically. After I click “Read Fields” it retrieves all of the  documents. It parses them to find the fields. It sees that ‘lines’ contains an array of items. It creates fields corresponding to these items: ‘lines.0.line number’, ‘lines.1.line number’, etc.

Granted, it’s a simple case. But that’s a solid foundation to start from.

Handle Complexity

This is a good example of why the default behavior is not good enough. At the time I’m designing the report, no order has more than 3 lines. But in the future I will have orders with many more lines. I need the report to handle N order lines correctly where “N” is not known at design time.

The first thing you must do is add another field to the report manually. The “Fields Provider” automatically makes all of the leaf-level nodes in the document available. That includes fields like “lines.0.line number” and “lines.0.product”. But you need to manually tell it you want the whole thing without reaching into its components.

Add a field called “lines” with data type “java.util.Collection”.

The MongoDB connector will now bring back your Array of N order_lines as desired. But how can you display them intelligently in a report?

Complexity via cop-out

I could treat the field ‘lines’ as a Collection, then simply convert it to a String and display this. In certain cases this could be useful. In general… yuck. I get something like this:

     [ { "line number" : "line1" , "product" : "ProdA"} , { "line number" : "line2" , "product" : "ProdB"} , { "line number" : "line3" , "product" : "ProdA"}]

In principle I could do some  parsing in the report itself to extract useful information. Thinking about that too hard makes me queasy. Yuck. Fortunately JasperReports has some mechanisms in place to handle Collections much more easily.

List Component

Create "Table Dataset" and define the relevant fields

The list component is designed to handle simple list of data. This example fits the bill perfectly. The key fact that many users don’t think of is that they can pass any Collection (like ‘lines’) into the component like this:

     new net.sf.jasperreports.engine.data.JRMapCollectionDataSource($F{lines})

This data source exists specifically to handle exactly this type of requirement. It’s perfect.

I created a new dataset in the report. I had to manually add the fields “line number” and “product” to this dataset. This is because there is no query associated with the dataset that iReport could use determine fields automatically.

Once we pass $F{fields} to the dataset, laying out the list is simple. Here each line in my list includes the line number and product name.

Table Component

The table component in JasperReports hasn’t gotten the love it deserves. It’s a great solution to this problem. Dropping a Table component into the report design is simple.

Laying out the table is straightforward. It imposes a bit more structure than the List component. In my simple example here I make the List and the Table look the same. But the Table holds additional features that make it easy to include a header or footer. It also has more semantic meaning because it includes the idea of separate columns.

Subreport

A subreport can take the same data source as a Table. So the key idea behind working with Tables and Subreports is identical. Subreports provide more flexibility than a table to accomodate complex layouts. But there is some corresponding complexity in maintaining a report with a subreport. In my example where I simply want to list out order lines, a subreport is clearly overkill. But in other cases it may be more appropriate.

Custom Java Utility

In some cases you may want a simple way to very specific things with the Collection. I encountered one JasperReports Server customer who simply wanted to create a comma separated list of values based on the contents of an Array coming from his MongoDB data source. It is possible to create a string like this as a variable which is part of the “Order Lines Dataset”. Then we could display the string using a List or a Table component. But this feels like a bit of a hack.

Typically the best way to do a bit a string processing like this is with an external function. I wrote a Java function to handle it. DON’T PANIC. There’s no reason that you need to write Java code to handle collections. I just wanted to include an example of it here for the sake of completeness. Here it is:

     /**
      * Takes a Collection of Maps and a String key
      * Returns a comma separated String of all values corresponding to that key
      */
     public static String concatMapValues(Collection<Map<String,String>> coll, String key) {
       StringBuilder sb = new StringBuilder();
       for(Map<String,String> m : coll) {
         if (sb.length() > 0 && m.containsKey(key)) {
           sb.append(", ");
         }
       sb.append(m.get(key));
       }
       return sb.toString();
     }

Final Result

Now that we have multiple different ways to process the order lines, let’s take a look at the report in action. I used a slightly updated query from the trivial one that I show at the start of this article. This query returns all orders where one of the order lines includes “ProdA”

     { 'collectionName' : 'orders',
       'findQuery' : { "lines.product" : "ProdA" } }

The final report has exactly the data I want. Well… it has it multiple times because I tried every different method. But normally I would just choose one. The report layout is shown as well. Again, it’s needless complex because I show two poor ways and four good ways to do the same thing.

Executed report showing 3 orders along with all order lines for each order (with multiple variations)

iReport in "Designer" mode showing the report layout

Sample Materials

You can try out the report for yourself. The sample data and .jrxml files are in this document. Get the Jaspersoft MongoDB connector separately.

Of course you’ll need iReport as well. JasperReports Server can be used to deploy the reports.

Advertisements

11 Comments »

  1. Hi Matt,
    I found another solution. I was sad to abandon Table Component. I hope to find a way to use the interactive capabilities, i.e. sorting\filtering down the road. 🙂

    Thank you.

    Comment by Renea — 29 May 2012 @ 07:15

  2. Thank you for sharing, that was very useful!

    Comment by Nicolas Beuzeboc — 27 September 2012 @ 13:43

  3. Hi,

    I’m quite interested in how one can handle collections (java.util.collection). In this case if ‘lines’ was a collection, how do you parse the text field to show ‘line number’ & ‘product’ separately? I’m new to JasperReports.

    Thanks

    Comment by Irving Montano — 24 October 2012 @ 20:58

    • I’m not sure what it would mean for lines to be a collection. What would it be a collection of? But perhaps you don’t need that. You can just use dot notation to “reach into” the line to get the fields you want. So you can use lines.line_number and lines.product to get those values.
      Important: If the “Read Fields” button in the query designer doesn’t pick those up for you automatically, you can just add a field called “lines.product” of type “java.lang.String” to refer directly to that value.

      Comment by mdahlman — 24 October 2012 @ 21:11

  4. Hello again, after trying to figure this out for a few hours, one thing I noticed is that in iReport 5.1 I am not seeing the fields returned they way you have. For me it comes back as “lines” with a type of “java.util.List”, whereas in your screen shot you are getting line.0.line number and line.0.product, could that potentially be my problem? I am not sure what I am missing here. I am using the specific example written above, but for me adding the list component and changing the datasource expression for the list dataset to new net.sf.jasperreports.engine.data.JRMapCollectionDataSource($F{lines}) still gives me back the data as a string looking thing like [[line number : line1, product: ProdA],[line number : line2, product: ProdB]], but in your example it parses out nicely into columns and rows.

    Thanks for any help you can give me.

    Comment by Raj Chaudhuri — 14 July 2013 @ 12:21

    • Nevermind, I didn’t notice the attached example files to this post. Once I looked at that, I was able to figure out how you did it. Thanks for the example and post!

      Comment by Raj Chaudhuri — 14 July 2013 @ 21:16

  5. This approach seems to work for one-level of list where the list contains simple data types (String, Integer, etc.). Is there a way to iterate through more than one level where the list contains objects or even other lists?

    Comment by Chris Ulbright — 9 October 2013 @ 10:28

    • Well… when you’re passing the Collection $F{lines}, you can certainly repeat the same type of functionality there. The subreport can take lines.colors and pass it to a subreport which can handle the Collection ‘colors’. If you need exactly 2 or 3 levels then this would be fine. But it’s not particularly pretty. It doesn’t extend easily to the idea of handling an unknown number of nested Collections.
      Depending on the exact requirements I would tend to use a Java utility class to handle it.

      Comment by mdahlman — 11 October 2013 @ 13:46

      • Thank you for taking time to respond to this. So using a subreport, could I then pass down a list and then utilize JRMapCollectionDataSource within the subreport to process the list? This might be sufficient if it could be used for 3 levels. Designing the report might be a challenge for non-programmers, however?

        Comment by Chris Ulbright — 11 October 2013 @ 13:52

      • I can’t think of any reason it wouldn’t work. Yes, you use JRMapCollectionDataSource in the subreport as in the main one. Subreports in subreport are supported. It would be quite a complex report, so you’ll need to keep things well organized. Good luck.

        Comment by mdahlman — 11 October 2013 @ 14:14

  6. Very helpful – thanks for the information.

    Comment by Paul vW — 13 March 2015 @ 03:54


RSS feed for comments on this post. TrackBack URI

Go on... leave a reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: