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开发者_如何学Go 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.
As DaDaDom suggested, use one report and two subreports:
Report 1 - Person
1234 John Smith
Subreport 1.1 - Certifications
01/01/2010 MCSE
03/01/2010 CISSP
Subreport 1.2 - Degrees
4/4/2001 B.S. Information Systems
5/5/2004 M.S. Comupter Science
Combined Results
Then:
- Add a Group band to the main report.
Group By
the employee ID.- Include the Certificates subreport in the Group band.
- Include the Degrees subreport in the Group band.
- Pass the employee ID from the main report to the subreports.
- Have each subreport query the requisite information based on the employee ID parameter that they receive.
精彩评论