Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: How to properly use Group By in Report designer?

  1. #1
    Join Date
    Dec 2011
    Posts
    16

    Default How to properly use Group By in Report designer?

    I have a simple task: Select some columns from tables and group them by category, as the figure below shows. Here, I use the country as category. But the results are not as expected: some patients from USA are included by different group. Any hints? ThanksName:  groupby_pentaho.jpg
Views: 42
Size:  29.0 KB

  2. #2
    Join Date
    Dec 2011
    Posts
    16

    Default

    Why is it so strange that the records have not been grouped into the same group since there is only one group....

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

    Default

    First, is your query sorted by "home country"? And if so, is your data clean enough? After all "USA" and "USA " (note the space at the end) are two different values and thus end in two different group instances.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  4. #4
    Join Date
    Dec 2011
    Posts
    16

    Default

    Hi Taqua,

    Thanks for your quick reply. I did consider these. "home country" is already sorted. I validated "USA" by querying distinct value from the table and I just got single "USA"(the query is cheating?...). I also tried to do group with other columns but have the same issue.

    Thanks a lot!

  5. #5
    Join Date
    Dec 2011
    Posts
    16

    Default

    Hi,

    You are right Taqua! My data is not clean. Some items have white spaces at the end checked using Datacleaner.

    Thanks

    Quote Originally Posted by Taqua View Post
    First, is your query sorted by "home country"? And if so, is your data clean enough? After all "USA" and "USA " (note the space at the end) are two different values and thus end in two different group instances.

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

    Default

    Wow, so your group-by is cheating. Out of curiosity, which database are you using that it auto-cleans on a group-by?
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  7. #7
    Join Date
    Dec 2011
    Posts
    16

    Default

    It's MS SQL Server 2005. Seems it always omits the space at the end of a string...

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.