Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Group by based on user input parameter

  1. #1
    Join Date
    Aug 2009
    Posts
    15

    Default Group by based on user input parameter

    Hi All,

    Just wondering if anybody have done this before. I have a simple query like below;

    Code:
    SELECT * FROM users GROUP BY ${group1},${group2}
    In the report designer (3.6), I created group1 & group2 param so that I can dynamically pass in any column i need for grouping. However, when i pass in say column 'age' for group1 and ic_no for group2, my query looks like below which is not as what i want;

    Code:
    SELECT * FROM users GROUP BY 'age','ic_no'
    Is there any way of removing those quotation mark ?

    Q2. Lets say my query above works well and I want to list all the users age between 10 to 50. But I want my list like below;

    age from 12 to 20
    1. user_name,...,...,..
    2. .
    3. .
    age from 20 to 30
    1. .
    2. .
    3. .
    4. .
    age from 30 to 40
    1. .
    2. .
    age from 40 to 50
    ......
    .....

    can something like above be done in Pentaho report designer ? Thanks very much in advance.

    best regards,
    Gompuok.

  2. #2
    Join Date
    Oct 2007
    Posts
    235

    Default

    For Q1 I think you need to use the advanced version of the jdbc query, which lets you use a formula to define the sql to be run.

    For Q2 Add a column to your sql with some thing like:
    Code:
    case when age between 12 and 20 then 'age from 12 to 20'
            when age between 21 and 30 then 'age from 20 to 30'
    ... and so on ...
    Then use that as a group by column in report designer, just make sure you sort by that column first

    Good luck

    Wil
    SQL: as much of a standard as the English language

  3. #3
    Join Date
    Aug 2009
    Posts
    15

    Smile

    What a good day. Thanks Will. You help me a lot

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

    Default

    @gompuok: I'm very surprised that your JDBC driver actually accepted parameters at the group-by position. The JDBC standard is very strict on static that only values, but never structural elements (like column names) can be parametrized via prepared-statements. Seems your driver has a rather loose interpretation of what the standard means.
    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.