Efficient Report Design help required

[SteveFairclough]

I need to write a report that shows parent records with totals calculated from child records from two different tables. The basic scenario is as follows :-

Claims Sales Purchases
ClaimID ClaimID ClaimID
Name SalesItem PurchaseItem
Address SalesValue PurchaseValue
PostCode

That's just a sample of the tables involved. The relationships are
Claims.ClaimID -> Sales.ClaimID (one to many)
Claims.ClaimID -> Purchases.ClaimID (one to many).

I want to be able to display a simple report showing one Claims record per line with the total of SalesValue and PurchaseValue child records.

There are not always child records in either table. If there was just one child table eg Sales, then I could write the report using a group by ClaimID with sub-totals in the Group Footer. However, as there are two child tables then I'm not sure exactly how to approach the report design. I have tried using sub-reports and shared variables to provide the summary value of SalesValue and PurchaseValue for each Claims record but this makes the report inefficient. I would like to produce these results without using Sub-Reports.

Anybody got any tips on the correct and efficient way to design such a report ?

Thanks in advance for any help provided. Steve Fairclough
I.T. Manager

 

[kenhamady]

You will need a linked subreport.
Create the one-table report as you desicribed. Then create a separete report using the other table. Put the second report into the GH of the first report, and create a link based on claim ID. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[IdoMillet]

Steve,

Sounds like you are trying to avoid subreports,
so here are 2 alternative approaches:

1. Create a view that returns Claim_ID and the sum
of Purchase Value from the Purchases table.
Something like:
-----------
Select claim_id, sum(purchase_value) from
Purchases Group By Claim_Id
-----------
Then outer join the claim to sales
and claims to the view above.

To simplify things and sidestep outer
join limitations in some environments, you
could create 2 summary views (one for
purchases and one for sales) and use
those as the basis for your report.

2. Do all of this in one SQL query by returning
each summary view information as a subquery within a
single SELECT statement. You would need
to use the SQL Designer for that (unless you create
the whole thing as a View in the DBMS).

Cheers,
- Ido
ixm7@psu.edu

 

[MikeTom]

Create a stored procedure to return all the data with a column type for Sales if a sale Purchase if a purchase. then create your report on the stored procedure.You can then group by claimid and type in the report.

Create table #TempTable(ClaimID int,
Name Varchar(30),
Adress Varchar (200),
Postode Varchar (6),
Type varchar (10),
Item varchar (30),
Value decimal(8,2))

Insert into #TempTable
select Claims.ClaimID,
Claims.Address,
Claims.Name,
Claims.PostCode,
'Sales',
Sale.SalePrice,
Sale.SalesValue
from Claims
Inner join Sales on Claim.ClaimID = Sales.ClaimID

Insert into #TempTable
select Claims.ClaimID,
Claims.Address,
Claims.Name,
Claims.PostCode,
'Purchase',
Purchase.PurchasePrice,
Purchase.Purchase.Value
from Claims
Inner join Purchase on claim.ClaimID = Purchase.ClaimID

Select * from #TempTable
Drop Table #TempTable

 

[SteveFairclough]

Many thanks for the suggestions.

I'm going to test the theories suggested. I'll let you know how it goes.

Thanks again
Steve Fairclough
I.T. Manager