Need advice on how to get correct summary in Group Header. 1

[egundrum]

Hi All - I've been stumped on this problem for a while. I can solve it using 2 SQL COMMANDS, but the performance is horrendous, so I'm hoping someone here can give me some pointers on how to get what I'm looking for. Here is a sample of the data I'm working with:

Table1: Summary
Acct PO Expend Allot
1000-1 PO1 100 125

Table2: Detail
Acct PO Contract Expend
1000-1 PO1 C01 50
1000-1 PO1 C02 25
1000-1 PO1 C03 25

Groups
Group1 Account
Group2 PO
Group3 Contract
Detail

My intended output would be similar to following:

GH1:
Expend = 100 Allot = 125
GF3:
Contract C01 PO1 Expend = 50
Contract C02 PO1 Expend = 25
Contract C03 PO1 Expend = 25

I've also got a SubReport placed in the DETAIL section that drills-down to the individual Vouchers assocaiated with the Contracts.

My problem is that I'm trying to summarize the Expend and Allot amounts in the Group Header for Account. Since Allot doesn't exist in the Detail Table, I get multiple records when I join on Account & PO. I can use a RT to get the correct amount in the Group Footer, but can't use in the Group Header. I've also tried a Subreport, but I need to link to the existing Sub-Report (i.e. Voucher) from both Table1 and Table2, so can't have another subreport.

Any help on this issue would be greatly appreciated. I'm using Crystal 10 off a Sybase Server. Let me know if I need to provide more info.

THANKS!
-Ed

 

[lbass]

From your description of table fields, it looks like you could place {Summary.expend} and {summary.allot} directly into GH#1.

-LB

 

[egundrum]

thanks LB, my bad on the example data. Here is a revised set:

Table1:  Summary            
Acct    PO    Expend    Allot
1000-1    PO1    100    125
1000-1    PO2    50     50

Table2: Detail            
Acct    PO    Contract    Expend
1000-1    PO1    C01             50
1000-1    PO1    C02             25
1000-1    PO1    C03             25
1000-1    PO2   C01             25
1000-1    PO2    C02             25

Desired Output

GH1:  (Account)
Expend = 150  Allot = 175
GF3:  (PO #)
Contract C01  PO1 Expend = 50
Contract C02  PO1 Expend = 25
Contract C03  PO1 Expend = 25
Contract C01  PO2 Expend = 25
Contract C02  PO2 Expend = 25

thanks!

 

[lbass]

I think the subreport is the way to go. You can add multiple subreports accessing the same table to the same main report--you just can't nest subreports within subreports. I would use the summary table in the subreport, link it by Acct # only to Acct# in the main report, and place it in GH#1.

-LB

 

[egundrum]

Thanks LB!!! I had tried using the subreport before, but wasn't getting the results I needed because I also linked on PO. It was causing the report to only pull in the first Allot Amount and not the sum. It just didn't occur to me that I didn't need to link on ALL of the relational fields between the main and sub?!?!?! Thanks so much for for getting me unstuck from the mud on this one and opening my eyes to linking main reports to subreports!!!!!

-Ed