Need report showing stock, sales, purchases 1

[peamkay]

I am using Crystal 10 with a SQL database and need to figure out how to create a report from the following 4 tables: Items, Stock Area, Orders, Purchase Orders that shows my total on hand (adding up records in the Stock Area table), total on open sales orders and total on open purchase orders. The Itemcode is a 1-to-many link from the Items table to all other tables but when I try to do multiple groups or multiple detail lines I get duplicated data (it adds the stock qty each time there is a PO record, etc). I only want to show items where on hand + PO - SO < 0. It seems like it should be a basic report but I can't figure it out. Any help is greatly appreciated.

 

[hilfy]

Please post some more info about your table structures.

Also, look at grouping your data and putting the fields in a group header instead of the details line.

Look at turning on "Select Distinct Records" on the Database menu. (This may or may not help - depends on your data structure.)

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[peamkay]

There are 4 tables.
Item table contains the ITEMNO, DESC
Stock table contains BIN, ITEMNO, QTY
Sales table contains DOCNO, ITEMNO, ORD_QTY
Purchase table contains PONO, ITEMNO, PURCH_QTY

ITEMNO DESC
Z1 Widgit
Z2 Blue Widgit
-----------------------------------
BIN ITEMNO Qty
B1 Z1 10
B1 Z2 5
B2 Z2 6
B3 Z2 3
---------------------------------------
DOCNO ITEMNO Qty
SO100 Z1 5
SO200 Z1 12
---------------------------------------------
PONO ITEMNO Qty
PO100 Z2 100
PO101 Z1 50


Desired Result:
On Hand SO PO
Z1 Widgit 10 17 50
Z2 Blue Widgit 14 0 100

I linked the Item table to the Stock table, Item table to the Sales table and Item table to the Purchase table (via ITEMNO). But because they are 3 independent tables I can't figure out how to put the fields into the report.

What would I group by besides the item number?

 

[hilfy]

I can think of two ways to do this, but one requires sub-reports, so we don't really want to go there...

Create a formula for each quantity that will have a 0 quantity if the data is a duplicate of the previous record. These formulas will be summed to get your quantities that you're going to display. They'll look something like this:

{@UniqueStockQty}
If ({stock.bin} + {stock.itemno}) = (previous({stock.bin}) + previous({stock.itemno}) then 0
else {stock.qty}

{@UniqueSalesQty}
If ({sales.docno} + {sales.itemno}) = (previous({sales.docno}) + previous({sales.itemno}) then 0
else {sales.ord_qty}

{@UniquePurchQty}
If ({purchase.pono} + {purchase.itemno}) = (previous({purchase.pono}) + previous({purchase.itemno}) then 0
else {purchase.purch_qty}

You can then put a sum for each of these formulas at the item level on your report and put the data in the ItemNo group header or footer.

To display only the records that meet your criteria, you might be able to put something like the following in the suppress formula for the section:

(sum({@UniqueStockQty}, {stock.itemno}) + sum({@UniquePurchQty}, {stock.itemno}) - sum({@UniqueSalesQty}, {stock.itemno})) > 0

(This assumes you're grouping on {stock.itemno})

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[peamkay]

Great! I'll give it a shot.
I tried the subreport route but ended up with a bunch of blank space on the report because I was trying to suppress the footer when on hand + po - so > 0 so I got blank "detail" lines for all the items that didn't meet my criteria.

 

[lbass]

I think you could simplify this by using a command to creat a union all statement, as in:

Select 'Stock' as Type, Item.`ItemNo` as Item, Item.`Desc`, Stock.`Bin`, Stock.`ItemNo`,Stock.`Qty`
From Item inner join Stock on
Item.`ItemNo` = Stock.`ItemNo`
Union All
Select 'Sales' as Type, Item.`ItemNo` as Item, Item.`Desc`, Sales.`DocNo`,Sales.`ItemNo`,Sales.`Qty`
From Item inner join Sales on
Item.`ItemNo` = Sales.`ItemNo`
Union All
Select 'Purchase' as Type, Item.`ItemNo` as Item, Item.`Desc`, Purchase.`PONo`,Purchase.`ItemNo`,Purchase.`Qty`
From Item inner join Purchase on
Item.`ItemNo` = Purchase.`ItemNo`

Then you could insert a crosstab that uses {command.ItemNo} as the row field, {command.type} as the column field, and the sum of this formula {@qty} as the summary field:

if {command.type} = "Sales" then = -{command.qty} else
{command.qty}

Also insert a group on {command.item} and then go to report->selection formula->group and enter:

sum({@qty},{command.item}) < 0

-LB

 

[peamkay]

lbass - I had never used a command before but this worked great. Thanks!