Groups to show many-to-one relationships

Question

I need to create a JasperReport in iReport that lists employees info including degree and certification information. My query returns all the data I need (i think) but I can't figure out how to do the groups and/or details in IReport so that it displays like I need.

Here is my query:

select e.eid, e.first_name, e.last_name, 
       d.degree_date, d.degree_name, 
       c.cert_date, c.cert_name
from employees e, degrees d, certifications c
where e.eid = d.eid
and e.eid = c.eid

Here are results:

1234, John, Smith, 01/01/2010, MCSE, 4/4/2001, B.S. Information Systems
1234, John, Smith, 01/01/2010, MCSE, 5/5/2004, M.S. Comupter Science
1234, John, Smith, 03/01/2010, CISSP, 4/4/2001, B.S. Information Systems 
1234, John, Smith, 03/01/2010, CISSP, 5/5/2004, M.S. Comupter Science
1235, John, Rolph, 01/01/2010, Pottery Instruction, 6/6/2001, B.A. Fine Arts 
1235, John, Rolph, 01/01/2010, Pottery Instruction, 7/7/2004, M.S. Education
1235, John, Rolph, 03/01/2010, Adv Pottery, 6/6/2001, B.A. Fine Arts
1235, John, Rolph, 03/01/2010, Adv Pottery, 7/7/2004, M.S. Education

I need the report to display like this:

Employee ID Employee Name
1234    John Smith
        Certifications:
        01/01/2010 MCSE
        03/01/2010 CISSP
        Degrees:
        4/4/2001 B.S. Information Systems
        5/5/2004 M.S. Comupter Science

1235    John Rolph
        Certifications:
        01/01/2010 Pottery Instruction
        03/01/2010 Adv Pottery
        Degrees:
        6/6/2001 B.S. B.A. Fine Arts
        7/7/2004 M.S. Education     

Let me know if you have any suggestions.

Answer 1

To achieve the desired report format, you will need to create two groups in iReport. The first group will be on the Employee ID and will be used to group the records by the employee. The second group will be on the certification and degree information, which will be displayed under each employee group. Here are the steps to create the report in iReport:

1. Create a new report in iReport and add a connection to your database.

2. Create a new query in iReport with the SQL query you provided.

3. Drag a Table component onto the report design surface.

4. Drag the Employee ID, Employee Name, Certification Name, Certification Date, Degree Name, and Degree Date fields from the query onto the Table component.

5. Right-click on the Employee ID field in the Table component and select "Add Group".

6. In the "Group By" section, select "Employee ID".

7. In the "Group Header" section, add a Text Field component and enter "Employee ID" as the text.

8. Add another Text Field component next to the Employee ID field and enter the Employee Name as the text.

9. Right-click on the Employee ID field again and select "Add Group" to create a second group.

10. In the "Group By" section, select "Certification Name" and "Degree Name".

11. In the "Group Header" section, add a Text Field component and enter "Certifications:" or "Degrees:" as the text.

12. In the "Detail" section, add a Text Field component for each of the Certification and Degree fields.

13. Click on the "Preview" tab to see the report.

You may need to adjust the formatting of the report to get it to display exactly as you want it, but this should provide a good starting point.