How to group multiple result sets in a report?

Question

I am using ActiveReports with ASP.NET but I think answer for any similar reporting component will do.

I have two resultset to merge and show in a single report, like:

Table 1:

Name Job              Start End
Jack Some service     1992  1997
Jack Some Sales Exp   1998  2007
Jane Some programming 2000  2003

Table 2:

Name Training
Jack Shiny French Certificate
Jane Crappy database certificate
Jane Some courses in management

And the report should look like:

Jack
  Job History:
    Some Corp, 1992-1997
    Some Sales Exp, 1998-2007
  Training History:
    Shiny French Certificate
Jane
  Job History:
    Some programming, 2000-2003
  Training History:
    Crappy database certificate
    Some courses in management

How should I merge the two tables and how should I design the layout to achieve the report in the given example?

Update:

As you may notice, I am not trying to do this with a single select. I have two datatables as source and I can merge them by hand to get a single data source. I am trying to use grouping but I need two kind of groups for each employee. One for the jobs, and one for the trainings. How can I use groups or sub-reports feature to bind this kind of data (and how should I process the data if necessary)?

Answer 1

In general you cannot do it in a single SELECT statement. Most reporting tools offer some kind of "subreports" or "inner sections" that run on a different SQL question and have some parameters passed from the main report. You could use two subreports and one master report.

Answer 2

You should select this two tables with "left-join", create a "GroupHeader/Footer" section in report and set "DataField" on "GroupHeader" section to field, which should be used as grouping.

Look at the samples from ActiveReports, they surely has a sample for grouping.

Answer 3

Use subreports... Create a main report that will have two subreports. One subreport for job history and one subreport for training history. The main report will need a query that will return a list of people. Then for each Person in the detail of the main report, set a parameter on each of the child subreports that will refine the query in those two to list the Job History or Training History for only the current person being displayed in the parent report.

A detailed walkthrough that explains how to do this step by step is on the Data Dynamics website here. Some overview information is also here