Using JasperReports for Reporting Against MongoDB

Jaspersoft is an open-source Business Intelligence platform similar to Pentaho or Business Objects. Unlike Business Objects, the community edition is free to use. There is also a commercial version of the product that offers many more features but requires licenses to be purchased.

I had earlier done a proof-of-concept with Pentaho, another open source solution. Although I was successfully able to get it to work against MongoDB and REST data sources, it  didn't lend itself well to basic reporting needs like JasperReports does.

Installing JasperServer is fairly straightforward. My Windows installation was a wizard-driven process where I accepted all the default settings except for the web server port. It ran right out of the box and no further work was required. There are ample resources available online for this so I shan't be documenting the server install here. Instead I'll be focussing on how to create reports and on using the design and web interface for end users.

There are two development clients available for download - Jaspersoft Studio is an Eclipse-based add-in and iReport Designer is the stand-alone report designer. All new development is done. They both work well but for the purposes of this article, I'm going to focus on the iReport Designer. I am using the latest version as of this writing, version 5.1.0.

The installation is straightforward as it is a Java-based application (I don't think admin rights are required to install it, but I haven't tested that). Once it installed, you can launch it from the Windows Start menu.

• Add a new connection to the JasperServer by clicking on the 'Add Server' icon in the Repository Navigator

• Set the fields as follows an click 'Save' -
  • ID:JasperServer (can be anything)
  • JasperReports Server URL: http://xxx.xxx.xxx.xxx:9080/jasperserver/services/repository
  • Username: jasperadmin
  • Password: jasperadmin

• You should see the server with all the subfolders. If you expand 'Data Sources', you should see that I have already created the connection to the MongoDB data source on the server

• To create a new report select 'File -> New...'. This will bring up the template dialog box. Click on 'Launch Report Wizard'

• Give your report a meaningful name and click 'Next'

• Click on 'New' to create a local connection to the MongoDB data source

• Select 'MongoDB Connection'

• Enter the connection information for the MongoDB data source and click 'Save'. Our MongoDb database is called 'html5_charts' -
  • Name: DS_mongodb_html5_charts (I use the convention 'DS' for data source, then server name and finally database name separated by underscores)
  • Mongo URI: mongodb://xxx.xxx.xxx.xxx:27017/html5_charts
  • Leave the Username and Password blank

• You must also enter a basic query for the connection. This will be a JavaScript query instead of a SQL query as MongoDB is a JSON store. We only have one collection in our database and the collection name is 'tests'. 'Collections' in MongoDb are a little bit like tables in relational databases. Click 'Next'

• You'll be presented with a list of fields from the database. Select all the fields except '__v' and '_id' and click on the '>' button to move them to the right-hand side. Then click 'Next'

• On the next screen don't Group anything, just click 'Next' again.

• Finally, click 'Finish'

• You should see the following screen -
  • To the right is the 'Report Inspector'. This is where you manage the report template, the data sources, calculations and fields
  • To the left of the Report Inspector is the minimized 'Repository Navigator' where we created the connection to the server earlier
  • In the middle is the actual report template
  • Above the report template is the data source that we created above and which the current report is associated with
  • To the right of the report template is the 'Palette'. This is where you find all the report widgets such as charts, tables and other visual elements
  • To the right of the Palette is the collapsed 'Properties' window. This is where you control properties for every element in the report

• We're going to create a simple summary report in this example, so we need to get rid of all the report sections except fro the 'Title' and the 'Summary'
• In the Report Inspector, right-click each of the following and select 'Delete Band' -
  • Page Header
  • Column Header
  • Detail
  • Column Footer
  • Page Footer
  • Background
• Click and drag the blue border at the bottom of the 'Summary' section down to give yourself more room to add widgets
• From the Palette click-and-drag the static text widget and add it to the center of the 'Title' section in the template
  • Make sure it's selected and click on the 'Properties' tab on the far right
  • Change the 'Text' property under 'Static Text' to 'Dashboard'
  • Just beneath the 'Static Text' property are the 'Text Properties'. Change the font to Arial, select bold and change the horizontal and vertical alignment to 'Center' and Middle'

• In the Report Inspector right-click on the name of the report you're working on and select 'Add Dataset'

• We're going to repeat the same steps we followed before for the report. That is because we're now creating a separate dataset for summary charts. Give the data set a name (html5_charts) and click 'Next'

• Select the data source you created earlier (if it isn't already displayed) and re-enter the query we used before.

•   As before, select all the fields we want, click 'Next', *don't* selct a grouping and click 'Finish' -

• In the Report Inspector, expand the data set you just created, right-click 'Variables' and select Add Variable' -

• Click on the variable you just created (by default it will be named 'Variable1' and click on the Properties tab and modify the following properties -
  • Name: status_count
  • Variable class: select 'java.lang.Integer' from the dropdown
  • Calculation: select 'Count' from the dropdown
  • Variable expression: Click on the button and double click on 'Status' under 'Fields' and click 'OK'

• Add another variable to derive the length of time it took for each script to run from the starttime and endtime -
  • Name: time_elapsed
  • Variable class: java.lang.Number
  • Variable Expression: ($F{endtime}.getTime() - $F{starttime}.getTime())/60000

• Now click and drag a table widget from the palette to the bottom half of the Summary section. This will launch the table wizard. Change the number of columns to 7 and click 'Next'

• Add the fields in the order you want them and click 'Next' -
  • status
  • component
  • starttime
  • endtime
  • sprint
  • developer

• Accept the default setting of 'Use the same connection used to fill the master report'

• Select 'Use alternated detail rows background'
• Deselect 'Add table header', 'Add table footer' and 'Add column footer' and click 'Finish'

• You'll be presented with a table template

• Click and drag the variable for time_elapsed into the last column of the second row. This will create a split row.
• Right-click $F{developer} and select 'Delete'
• Click the blue column header for 'developer' and click on 'Properties' and change the text from 'developer' to 'time_elapsed'

• Go to the 'Format' menu and select 'Page format...' and change the paper size to 11x17 and the page orientation to landscape

• Click 'Preview' to run the report

• You probably want to format the chart a little more so that the fields are centered, the sprint always shows up in upper-case and the time_elapsed only gives you 2 decimal places. Go back to 'Designer' mode and  -
  • Select all the fields in the table designer and go to 'Properties'
  • Select Arial as the Font
  • Set the left indent to '10'
  • Now select just the column headers and center them vertically and horizontally
  • Click on the content cell for 'Sprint' and select properties and add '.toUpperCase() to the 'Text field expression' to capitalize all sprint values

• Select the XML view for the document and navigate to the bottom and find the section for 'time_elapsed'

• Add 'pattern="##,##0.00"' to the 'textField' element so that it reads -

• Now your report should look like this -

• Click and drag a chart wizard from the palette to the top half of the report and follow the wizard.
• Select the first pie chart

• Under data set select the data set you created earlier from the dropdown list (html5_charts) and click 'Next'

• For the unique identifier, select the 'status' field

• For the numeric value select the 'status_count' variable and click 'Finish'

• Right-click the chart and select 'Chart Data'

• Under the dataset tab change Coonection/Dataset Expression to 'Use connection expression' -

• Under the details tab change the Label expression to $F{status} + ":" + $V{status_count}.toString(). Then click Apply/close

• Select the chart and click on the 'Properties' window
  • Uncheck 'Show Legend'
  • Add the 'Series Colors' that you want

• Copy and paste the chart. Position them so that both charts are alongside each other and above the table

• Right-click the second chart and select 'Chart data' and select the 'Details' tab
• Change the 'Key expression' to $F{component}
• Replace $F{status} with $F{component} in the 'Label expression' section and click 'Close'
• Adjust the charts to make them bigger - move the blue guides to give yourself more space and move the table lower if you need to

• Your preview should now look like this -

• To publish the report to the server, go to the 'Repository Navigator'
• Expand the folders under 'Reports' and find the 'html5_charts' sub-folder
• Right-click on html5_charts and select 'Add -> JasperServer Report'

• On the wizard, enter the Name and ID for the report and click 'Next'

• Click on 'Get source from current open report' to automatically fill in the current report (or you can browse to select the jrxml file for the report). CLick 'Next'

• Under 'Locate the data source' select 'From the repository' and select the 'DS_mongodb_html5_charts' data source from the dropdown and click 'Finish'

• Your report should now be available on the server
• Use your browser to navigate to http://xx.xx.xx.xx:9080/jasperserver
• Type in your User ID and password (jasperadmin/jasperadmin)
• Navigate to the html5_charts subfolder under 'Reports' and you should see the report you just uploaded
• Click on it to open it

• You should see the following screen -

• To sort the table, click on any table header and use the arrows pop-up menu to sort ascending or descending

• To filter by a particular value, click on the column header and select the filter icon

• Select the entry you want to filter on and click 'OK'

• You'll see only the values you want
• Notice that when you filter the table, the pie charts change as well - this is because they are all linked to the same data source -

• You have many filtering options -

• You can refresh your report with the latest data by clicking on the 'refresh button above
• You can undo any of the changes above (filters, sorts, etc.) by clicking on the 'Undo All' button on the top right

• You can also highlight rows using the conditional formatting option available by clicking on any of the column headers of the table -

• Select 'Formatting...' and click on the 'Conditional Formatting' tab and click on 'Add'

• We want to find any scripts that took more than 10 seconds to run, so select -
  • Operator: Greater than
  • Condition: 10
  • Format: Red background with yellow text
  • Click 'OK'

• This is what you should see -

Helpful Links -

iReport Designer Tutorials & Help | Jaspersoft Community

MongoDB | Jaspersoft Community

Jaspersoft MongoDB Query Language | Jaspersoft Community