A->B, A->C relationships without using subreports

Hi all:

This is simplified version of my problem:

There are 3 tables A, B, and C. The relationships are: A(one)->B(many), A(one)->C(many). (there is no direct relationship between B and C)

I want to create a report to list each record in A, followed by records associated to the A’s record in B, then records associated to the A’s record in C. Both B, C records should be in a table format. Can I do it without using subreports? I have performance problems with subreports in a large report(thousand records in table A). RS documentation suggests replacing subreport with data region will help the performance.

Thanks in advance!

Here is an example:

Table definitions: (Pet table and Dependant table have not direct relationship)

Employee Table has column: EmpNo

Dependant Table has 2 columns:

EmpNo

DependantName

Pet Table has 2 columns:

EmpNo

PetName

Relationships: Employee(one) -> Dependant(Many)

Employee(one) -> Pet (Many)

Report Format:

EmpNo: ###

(this is a Reporting Service table)

Dependant Name 1 for EmpNo ###

Dependant Name 2 for EmpNo ###

Dependant Name 3 for EmpNo ###

Dependant Name 4 for EmpNo ###

(this is a Reporting Service table)

Pet Name 1 for EmpNo ###

Pet Name 2 for EmpNo ###

Not sure I completely understand your situation, but in order to use a nested data region you have to (unfortunately) use the same dataset as the parent data region, which means that you will have to combine the datasets for the report and subreport into one dataset/query. If you NEED to use a separate dataset you will have to use a subreport.

If you have performance problems using the dataset region method because of a large volume of records, then programming that can be a little tricky. I had a similar situation where, for performance reasons, I HAD to filter/parameterize the dataset before joining it because of the number of records (300,000+) in one of the tables. To pull this off I used a (sql server 2000) table type user-defined function (kinda a view that allows parameters) and joined that in the report. You will also have to rewrite your query to bring all the data together in one dataset.