Displaying several many to one tables

[Juice05]

I have an employee table that has numerous many to one relationships. i.e. Dependents, ContinuingEducation, Evaluations, etc.

I would like to display the report like this:

[b]First Name[/b]    Jose
[b]Last Name[/b]     Deleon
[b]Address[/b]       XXX Street
[b]Dependents[/b]    Sally DeLeon
              Victor DeLeon
              Jessica DeLeon
[b]Education[/b]     Certificate of greatness
              Customer service training
[b]Evaluations[/b]   01/08/2006 - Great
              07/08/2006 - Even Better

I have read many posts and most dealt with one related table, which is solved by a variable that is concatenated during "whileprintingrecords;" placed in the footer, but I couldn't find one that had numerous tables.

Would a subreport for each many-to-one be best?

Any help would be great, also I would love to keep as much in the detail section as possible so my end users can export to excel with minimal formatting issues.

Thanks in advance for your help.

 

[groggle]

Yes I believe it would.

Subreports can at least "quarantine" the dependent data and stop data duplication.

With careful, precise formatting, a successful export to excel should be possible as well.

 

[Madawc]

Crystal works by 'rows' or detail lines. So if Table A points to both Table B and Table C, and there is no relationship between B and C, it's tricky to get a useful 'row'.

The drawback with subreports is that each subreport does a separate access of the database. The main report does this just once, at the start. Subreports at the level of detail lines slow down processing: the report will be enormously slow. If you are going to use subreports, I suggest you develope the report with a small range of employees, just to get output in reasonable time.

Another possible approach is to do multi-layer grouping. Even though B and C have no relationship, they could be linked B to C via the employee ID and grouped by their own values. To avoid showing values of C for each value of B, put them in the footer, or count. This approach will be tricky to develop but produce a report that runs faster. You decide whether this is worth it.

A third possibility is to use SQL to assemble the data using a Stored Procedure. Assuming you have SQL and can work out how to write it, this would be the best solution. It could assemble records from different sources and put them in the same table.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Madawc]

PS - if you do use subreports, note that they can be placed side by side. This might export better to Excel.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Juice05]

groggle,

Thanks...I was afraid of the sub report answer. It works though.

Madawc,

I appreciate the time you spent and the ideas you offered.

Speed is always a priority, if I find that the sub reports are causing speed issues, I will reevaluate the situation.

Thanks again to both of you.