Jaspersoft OLAP is a powerful multi-dimensional analysis tool. OLAP views can be created and accessed directly from JasperReports Server from the Ad-Hoc designer. That method allows a business analyst to gain insight into their data by slicing, dicing and filtering the data however they want.

There is another way to unlock the power of your OLAP data, via iReport. iReport can consume multi-dimensional data served up by JasperReports Server by means of MDX through the XML/A interface built into the server. This document will guide a user into creating a report from iReport using the OLAP tables. The benefit of using iReport is the ability to use the built in chart types, the drill-through functionality and others. What you loose is the automatic drill down to the data.

Why would you want to do this? OLAP is typically used for two reasons:

• You have a very large dataset
• The SQL queries you're writing are getting far too complicated

Jaspersoft OLAP solves these two inhibitors by providing smart aggregations and queries not in SQL but in MDX, which is easier to use than SQL for analysis.

Connections

Server

You'll need to expose an XML/A connection on the server. This is explained in Section 3.5 of the OLAP user guide. Essentially you need to create a new Mondrian XML/A Source connection.

For the purposes of this example, I'll be using the Foodmart XML/A definition, typically located here on the server:

iReport

To define the XML/A Connection in iReport to your JasperReports Server instance you'll need to define a new XMLA Server connection.

Query

Once you've defined the connection, start a new report and you'll have to write a query. Choose the query language MDX

• Note: DO NOT use the XMLA-MDX query language..it is deprecated.
• Note: Do NOT leave the Automatically Retrieve Fields box checked, it will cause problems. There is a bug filed for this, however easy to work around.

You may now use the Query Designer to write an MDX query. Drag fields from the left into the template in the middle.

• Note: ALWAYS put your measures in Columns and your Dimensions in Rows like my example below. If you don't you'll likely get errors about Tuples not found.

Once you're happy with your query, press finish. Back at the Query screen, you'll see Columns and Rows on the right, simply double click on each, then select fields and click "Add Field" to map them. You will have to press Clear between each operation. For Dimensions you'll have to assign a Field Name manually.

Make sure to assign a type to your measures, in this case Profit and Store Sales are Type Numeric.

You should end up with something like this. Note that the preview data screen doesn't work in this screen, only in the Query Designer.

Report

Now you can write a report like any other. In this example, I create a graph to show Profit and Sales by Product family. The JRXML is attached here:
Mdx_report.jrxml

The MDX query can easily be parametrized, you can create drill-through reports and other compelling BI views just like the rest of the product suite.

Sub Datasets

Sub datasets with OLAP are a bit different, you will need to set a parameter called $P{MONDRIAN_CONNECTION} and map to MONDRIAN_CONNECTION - this is similar to REPORT_CONNECTION when you're using JDBC. You can set to use an empty datasource. This will allow you to use the table element, etc.

Notes

• Delete fields manually from the field list of the report to remove from the Query editor
• All fields are automatically mapped as strings, change the Java type in the field list

See also...

• Getting Started with Jaspersoft OLAP.