View Full Version : How to do a "Top X" list report with WAQR / MQL

01-26-2008, 05:34 AM

What I would like to do is create a "Top X..." report using the ad-hoc query wizard (WAQR). However I cannot find any functionality in the GUI to specify something like that.

So, the next thing I thought of doing was to manually modify the MQL query. So I looked a bit at the specification of MQL here:

but there seems to be no operator or modifier to truncate the resultset to a specified number.

I also looked a bit at the "constraints" section, but these seem to be predicates, i.e. a filter that is applied on the row level to specify which rows should be in the final result. So in order to use that, one would need a pseudo column like "rownum" so you can say rownum <= 10 for a top 10 list.

I am not suggesting the "rownum" trick is the preferred solution - I tend to think of the "Top X" problem more as if it's a special modifier or operator on the resultset, or otherwise maybe a property of the report rather than the query language. But whichever way, my main goal is 'just' to create a "top 10 list" style report.

Can anybody give me some help with that?

thanks in advance and kind regards,

01-29-2008, 06:34 AM
Hi Roland,

I'm sure you can't find the functionality for the simple reason that it's not there yet :-)
That functionality would have to be part of the ad-hoc reporting client and I guess it simply isn't.

As to the question whether or not the SQL generator should generate a "LIMIT X" clause or that this is something for the report generator, I don't know yet. Since Pentaho is doing a scrum (http://wiki.pentaho.org/display/OpenScrum/Home) on the next generation WAQR it makes sense to keep this feature under consideration during one of the sprints.

Thanks for the feedback!!


01-29-2008, 07:14 AM
Hi Matt,

thanks for the verification. I understand and I will await how it develops.

I'll just throw in my suggestion (I know you guys know best - just clarifying my opinion):

I think that first and foremost it should be a feature in of JFreeReport. The rationale is that JFreeReport can get its data from many sources, and some of those might not be able to truncate the resultset natively, in which case JFreeReport would have to do so itself in order to do a "Top X...report". Arguably, "Top X..." is a presentational feature rather than a conceptual one, and it seems to me that the presentation tool should then be able to handle it.

Right now, you can do a Top X report only when you know how to tweak the query, which is currently possible only for SQL. Apart from not being able to use MQL, that means that the user must be aware of the capabilities of the underlying database to actually get that done, at it seems that the report tool could add value here, not just for WAQR but also when using the reportdesigner interface.

The suggestion to push down the requirement and allow the underlying data source to handle the limit was maybe a premature suggestion - it would of course only be a form of optimization of the implementation, that could be advantageous for resource usage and response time. However it is not as important as simply being able to produce a report that shows only a limited number of rows.

Kind regards, and thanks again.

All the best,


01-29-2008, 02:18 PM
Well, the reporting engine has no such facilities (yet). Usually I prefer on the data-preparation steps to solve these problems for me. :)

However, adding this is easy, so the reporting engine will have that in the next release (0.8.10). But it will definitely take longer for the platform and the design tools to pick this feature up after that.

01-29-2008, 02:20 PM
Oh, here's the JIRA case for that: http://jira.pentaho.org/browse/PRE-241