Sums from different tables

[BadPenguin]

Here is the situation:

I am attempting to design a report that will show the products on order as well as the quantity on hand. I have 3 table I am working with: Products Table, LineItems Table, and Inventory Transactions (this is where our on hand comes from) table.

I need to sum the quantity ordered in the lineitems table and sum the transactions for each product on a single report. I can sum each individually, but when i put them together, the quantity ordered is incorrect (i think it is summing every order rather than open orders). Any thoughts, ideas, or help would be greatly appreciated.

 

[kskid]

It would be very helpful to post more technical information

- Crystal Version
- Database and Connectivity
- Sample Data
- Expected Output
- Any formulas/subreports that pertains to the problem you are addressing

As it is now, there is very little to go on from here other than you have three tables and assume that the Inventory and Line Item tables are outer joined to the product table.

 

[BadPenguin]

Crystal Version: Visual Studio.Net Package
Datebase: MSDE (SQL Server basically)
Sample Data: small numbers, just adding 1 + 2 + -1, etc
Expected output

SKU On Order On Hand
Product 1 4 5
Product 2 1 0

There currently arent any subreports or formulas other than summing by group.

 

[kskid]

On the sample data, I meant a sample input data layout of your product, lineitem and inventory file. Are they all one to one relationship or one to many relationship (which can account for the discrepancies in the summaries)?

That should tell use where the on-order and on-hand values reside? How does the transaction file come into play?

 

[BadPenguin]

The products table is the master table for SKUs

Table: Inventory Products LineItems
Field SKU many - one SKU one - many SKU
Field quantity quantity

 

[synapsevampire]

It sounds like standard row inflation.

Insert a group by the part number, then create a subreport which links by the part number for the lineitems (ordered table), and either pass the sum back, or display from within the subreport.

The following is an example:

Right click the part number group header and select insert section below.

In the Group Header A section use a formula to reset the ordered qty:

whileprintingrecords;
shared numbervar OrderedQty:=0

Place the lineitem subreport in the Group Header B, and shrink it down to a tiny little thing.

In the subreport create a formula of:

whileprintingrecords;
shared numbervar OrderedQty:= sum({table.qty})

Now after that section within the main report you can reference the value using a formula:

whileprintingrecords;
shared numbervar OrderedQty

-k

 

[synapsevampire]

I should have mentioned that I'd prefer to do this sort of thing within a Database View or Stored Procedure, subreports are slow.

-k

 

[BadPenguin]

Worked like a charm, thanks so much.

BP