Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: sql problem

  1. #1
    Join Date
    Oct 2012
    Posts
    116

    Default sql problem

    Hello there.

    I am working on report where I am loading data from a sql database like this.
    ID Name Surname
    1 peter brown
    2 hans miles
    3 lukas wazovski
    4 bob roston
    I want to have every person on a one (or more, depends on the next table) page. This is not a problem. I made a report where it works perfectly.

    Then I have another table (again sql database) like this one:
    ID activity
    1 football
    1 hockey
    1 poker
    2 fishing
    2 hockey
    3 football
    4 tennis
    4 football
    4 poker

    There are activities for every person. I would like to add all activities to the page in the report which belongs to the person above. It should look like this.

    FINAL REPORT id: 1
    name: peter surname: brown
    he likes: football
    hockey
    poker



    Well I can do some SQL operations like JOIN but that does not solve my problem. Because when I do JOIN operation, I recieve many rows and in every row there is only one activity so for every person I have more than one report with only one activity.

    Can someone help me?
    Thank you

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

    Default

    You can do a join, then group by person. give that group a name.

    Put your name and surname into the group header of that group. Then put your likes into details. Add the label for "He likes:" there as well. To only show it in the first row, add an item-count function with the group-propery set to the name of the group, so that you can detect the first row of that group. Then add a visible style expression to the label with the formula set to "=[name of group-count-function] = 1".

    Done. Your report will show.

    For more complex issues, you can always fall back to subreports that query the likes for each person. This way the subreport treats your list of likes as extra element and expands as necessary. However subreports will fire one extra query per person to get that person's likes.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Oct 2012
    Posts
    116

    Default

    Thank you very much.
    I have tried the solution with groups and it worked.
    But like you said, my report will be more complex and I would like to use subreport.
    I was trying to set up the subreport but the result was always the same. On each page with one name there are all activities from all persons.
    How should I set subreport to get the right result?
    Thank you

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

    Default

    Use parameters and a properly written where clause. Setup parameters in the data-tab of your subreport and pass the person-id from the master into the subreport.

    So in your subreport use "SELECT * FROM activities WHERE id=${person-id}"
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  5. #5
    Join Date
    Oct 2012
    Posts
    116

    Default

    Thank you, i really appreciate your help.

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.