JPivot with colors


Mondrian Version : mondrian-

This is a further sample which querying Sales Cube using JPivot interface. The cube data is derived from MySQL foodmart sample data which we can generate from Mondrian's zip bundle. The sales cube schema is defined in Foodmart.xml file.

The page shown will by default show 1 dimension (Product) and 3 measures (ROI, Store Cost and Store Sales). ROI iiself is a calculated measure based on Store Cost and Store Sales (ROI = (Store Sales -Store Cost) / (Store Cost) * 100).

This sample show an indicator with green and red colors depending on ROI measure with condition :
  • If ROI is less than 150, the particular row shows a red color
  • If ROI is greater than 150, the particular row shows a green color
  • If ROI is exactly equal to 150, the particular row shows no color indicator
This time the sample demonstrate an image visualization feature to give us a better insight on our OLAP data.


Prerequisites Articles

File Configuration

Open and edit arrows.jsp file - which should be located in your [TOMCAT_HOME]/webapps/mondrian/WEB-INF/queries folder - with your favorite text editor,  and edit <jp:mondrianQuery> with these following configurations :


with member [Measures].[ROI] as '(([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost])', format_string = IIf((((([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost]) * 100.0) > 150.0), "|#.00%|style='green'", IIf((((([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost]) * 100.0) < 150.0), "|#.00%|style='red'", "#.00%"))
select {[Measures].[ROI], [Measures].[Store Cost], [Measures].[Store Sales]} ON columns,
  {[Product].[All Products]} ON rows
from [Sales]
where [Time].[1997]

Note : [TOMCAT_HOME] is the folder where your Apache Tomcat installed. For example : C:\apache-tomcat-6.0.16


Open your Mondrian web application and click on the sixth sample from the index page (JPivot with colors). You should see an OLAP data table with a JPivot toolbar. Drill through some of the dimension level nodes.

For example, my web application is located at http://localhost:8080/mondrian and my "JPivot with colors" sample url ishttp://localhost:8080/mondrian/testpage.jsp?query=colors. Screenshot below shows the page that have been drill through at some nodes.

Still Getting Errors ?

If you already following all the instructions and still got some errors or you have other issues that has to do with other database sources then you can post your problem to our Mondrian user group at Or you can send a blank email to to join the group. 

Note that you must get approval first before you can post.

Mondrian Training

For those of you  in Indonesia we have a regular training on OLAP and data warehousing subject using Pentaho Data Integration and Mondrian.

If you want to enroll for the session please feel the form at and we will send you the training schedule.

And for those who like to have a remote training instead using Webex please contact us by email to