Show Details of Two Filtered Tables

[btgroup]

Hello,

I'm using CR9 with SQL Server 2000. Without creating a view, can someone tell me how I can show details of two different tables that are both filtered separately? I pass parameters to the report to limit the records by account number and date.

For instance, table one is filtered by an account number and also linked by a project number to another table (a third table). Table 2 is only filtered by account number, but also has a project number which is not linked to the third table.

In the table 1 case, no details should be shown when the project number cannot be linked to the third table, while table 2 should show every record.

My grouping on the report is by account number, date, and finally project number.

Thanks in advance for your reply.

 

[kskid]

Need to post more information.

A data sample of the tables would be helpful along with a brief description of the the need for the table. Fro example although table 1 and table 2 have account numbers, what kind of accounts are in these tables (banking, customer, sales, etc)

If you are grouping by account, does that mean the account number from table 1 is the same as the account number of table 2. Is it a one to one relationship or one to many relationship.

Table 3 cane be left outer joined to table one so no problem there

-LW

 

[btgroup]

Hi kskid. Thanks for your help.

The following is a sample of the data (this is customer account data):
Table1
acctno date projectno other_fields...
111 4/21/05 9999 ...
222 4/19/05 8888 ...
111 4/12/05 9999 ...
111 4/13/05 7777 ...

Table2
acctno date projectno other_fields...
111 4/20/05 5555 ...
222 4/03/05 4444 ...
111 4/20/05 9999 ...

Table3 (only for linking with table 1)
projectno description other_fields...
9999 Sample Desc ...
8888 Sample2 Desc ...

The grouping by acctno would be the same for table 1 and table 2 (so in the example above only records for acctno 111 need to be displayed). I filter this out by passing the acctno as a parameter and use it in the Record selection formula (the begin date and end date is also passed).

My end result for the example above would need to show the following if I passed acctno 111:
acctno date projectno other_fields...
111 4/21/05 9999 ...
111 4/12/05 9999 ...
111 4/20/05 5555 ...
111 4/20/05 9999 ...

The record in table 1 with acctno 111 and projectno 7777 is not displayed because the project is not found in table 3. Every record for acctno 111 is shown from table 2.

Hope that helps narrow it down. I greatly appreciate the help!

 

[kskid]

OK. Try this.

In database visual linking,

Table1.acctno -> equal join -> table2.acctno
Table1.projectno -> equal join -> table3.projectno

Assuming you have an acctno parameter, then your record selection formula would have

{table1.acctno} = {?acctno}

Depending on what you are reporting, you might want 2 detail sections. Detail section a to display fields from table 1 and detail section b to display fields from table b.

When refreshed, the report will prompt user to enter an account number, which in this case is 111

This would select account 111 from table1 AND table2 AND
the matching Table1.projectno from table3


-LW

 

[btgroup]

Thanks kskid! This is great! I have one last level of complexity for the report (and then I'll leave you alone...I promise!).

I also need to sort by projectno and perform subtotals on the change of each group. Since table 1 and table 2 may or may not have the same projectno, I'm not sure how to group them.

Ideally, I'd like to get the following result:
111 Customer 111 (report header)

Project 5555 - Dample Desc2 (page header)
4/20/05 ...other_fields
Subtotal on one of the other fields (group footer)

Project 9999 - Sample Desc (new page header)
4/12/05 ...other fields
4/20/05 ...other_fields
4/21/05 ...other_fields
Subtotal on one of the other fields (group footer)

Grand total of all subtotals (report footer)

The information you provided so far has allowed me to display the records I want, but I am not sure how to go about organizing them a little better. Thanks again!

 

[kskid]

Hmmmm.... that does complicate it a little. SInce I can't recreate your scenario, you will have to do it and post the results

Insert a group on Table1.AcctNo, does detail section b still show the data for table2?

-LW