开发者

Groups to show many-to-one relationships

开发者 https://www.devze.com 2023-02-16 11:24 出处:网络
I need to create a JasperReport in iReport that lists employees info including degree and certification information.

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:

  1. Add a Group band to the main report.
  2. Group By the employee ID.
  3. Include the Certificates subreport in the Group band.
  4. Include the Degrees subreport in the Group band.
  5. Pass the employee ID from the main report to the subreports.
  6. Have each subreport query the requisite information based on the employee ID parameter that they receive.
0

精彩评论

暂无评论...
验证码 换一张
取 消