Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: How to do a "Top X" list report with WAQR / MQL

  1. #1
    Join Date
    May 2006
    Posts
    151

    Default How to do a "Top X" list report with WAQR / MQL

    Hi!

    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:
    http://wiki.pentaho.org/display/stud...ata+MQL+Schema

    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,
    kind regards,

    Roland Bouman
    http://rpbouman.blogspot.com/
    @rolandbouman on twitter
    Author of "Pentaho Solutions" (Wiley, ISBN: 978-0-470-48432-6)
    Author of "Pentaho Kettle Solutions" (Wiley, ISBN: 978-0-470-63517-9)

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    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 on the next generation WAQR it makes sense to keep this feature under consideration during one of the sprints.

    Thanks for the feedback!!

    Matt

  3. #3
    Join Date
    May 2006
    Posts
    151

    Default

    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,

    Roland.
    kind regards,

    Roland Bouman
    http://rpbouman.blogspot.com/
    @rolandbouman on twitter
    Author of "Pentaho Solutions" (Wiley, ISBN: 978-0-470-48432-6)
    Author of "Pentaho Kettle Solutions" (Wiley, ISBN: 978-0-470-63517-9)

  4. #4
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    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.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  5. #5
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Oh, here's the JIRA case for that: http://jira.pentaho.org/browse/PRE-241
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.