Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: How to get only top n items in ad-hoc report

  1. #1

    Default How to get only top n items in ad-hoc report

    How to limit data in ad-hoc report, for example how to report only the top 100 best sold products?

  2. #2

    Default

    Is limiting the number of result rows in ad-hoc reports possible?

  3. #3
    pstoellberger Guest

    Default

    TOP N functionality is not available in ad-hoc reporting right now

    many people ask for it, so maybe pentaho will implement it soon

    ad-hoc reporting needs a re-write anyway

    -Paul

  4. #4
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    Rumour has it adhoc was getting re-written after Sugar. Not sure if thats still the case or not though?

  5. #5

    Default oracle top N query

    Oracle lets you do some stuff in the query. Depending on how you plan to use the ad-hoc, I thought this query might be helpful.

    Possible work around in Oracle (not verified with Adhoc but since the query is doing some of the heavy lifting maybe you can make it work, I use it with alot of parameters in a pprt):


    In SQL Query for top 25.


    --select the columns you want
    Select Product, ProductSales, SalesRank
    From
    (
    --- Get them Ranked
    Select product, productSales,
    RANK() OVER (ORDER BY PRODUCTSALES DESC) AS Salesrank

    From
    (
    -- Get your product sales
    Select product, sum(Sales) over partition by product as ProductSales
    From
    invoiceline
    )
    ORDER BY Productsales
    )
    WHERE Salesrank <=25

    There is likely a cleaner/better way than this but its how I perform a popularity report. You can also use sum(productsales) OVER (ORDER BY Productsale DESC) to give you the cumulative sales. I am doing most of this post from memory...
    Last edited by Sreh; 04-20-2010 at 05:33 PM. Reason: grumble grumble.... another typo! the instead of there

  6. #6
    Join Date
    Mar 2010
    Posts
    159

    Default

    LIMIT might be of use in MySQL query...

  7. #7
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Except that in Ad-Hoc reporting in the BIServer, you have no access to SQL, so using a LIMIT command won't work.

    Until the feature is added to the Ad-Hoc interface (Submit a Jira for it!), then it will not work.

    PDI already understands Limit, so the SQL Dialects (MSSQL, MySQL, etc) that PDI speaks should provide a starting point for the limit, but it won't solve all of it.

  8. #8
    Join Date
    Mar 2010
    Posts
    159

    Default

    Ahh... thanks gutlez. I'm still ramping into things not ready to ask for new reporting features yet. Just throwing out my two cents of ignorance.

    Regards,
    Jeremy

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.