Groups to show many-to-one relationships

0 votes

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.

Apr 19, 2023 in Cyber Security & Ethical Hacking by anish
• 400 points
174 views

1 answer to this question.

0 votes

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.

answered Apr 19, 2023 by Edureka
• 12,690 points

Related Questions In Cyber Security & Ethical Hacking

+1 vote
1 answer

Not able to use nmap in python.

nmap module doesn’t have PortScanner attribute. The ...READ MORE

answered Jan 28, 2019 in Cyber Security & Ethical Hacking by Omkar
• 69,230 points
2,958 views
0 votes
1 answer

Not able to run nmap scan on router.

Might not be the actual router address. ...READ MORE

answered Jan 31, 2019 in Cyber Security & Ethical Hacking by Omkar
• 69,230 points
1,632 views
+1 vote
1 answer
+1 vote
3 answers

Not able to install arpspoof

There is no package called arpspoof. To use the arpspoof command, ...READ MORE

answered Feb 9, 2019 in Cyber Security & Ethical Hacking by Omkar
• 69,230 points
60,147 views
0 votes
0 answers
0 votes
1 answer

Wordpress SEO : Semrush audit report

actually autoptimize, and Wp Rocket are the ...READ MORE

answered Mar 1, 2022 in Digital Marketing by narikkadan
• 63,700 points
378 views
0 votes
0 answers

Azure DevOps PDF Export Pulgin Suggestion

I built a job using Jenkin and ...READ MORE

Mar 15, 2022 in DevOps Tools by Kichu
• 19,050 points
631 views
0 votes
1 answer

How to get the JasperReports into Excel format with proper alignment

Remove at least one parameter: exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN,Boolean.TRUE) Note that JRXlsExportParameter is deprecated the correct ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,700 points
3,263 views
0 votes
1 answer

how to start a career in cyber security?

Many of us are familiar with the ...READ MORE

answered Dec 14, 2021 in Cyber Security & Ethical Hacking by Edureka
• 12,690 points
406 views
0 votes
1 answer

how to become cyber security expert in india

The professionals in responsible for designing and ...READ MORE

answered Jan 31, 2022 in Cyber Security & Ethical Hacking by Edureka
• 12,690 points
428 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP