Inventory query

Hi all,

I have an existing report I am attempting to modify to include a new field. The report currently contains the Average Unit Sales per day. It is sorted by SKU. The SKU's are loaded from the "products" table, and the "sold" field is calculated using the "orderLineItems" table (we use both an orderHeader and an orderLineItems table for orders). So it will do a SUM of all line items during a specific date range and return how many were sold. Then a simple average function over the date range returns the average sold per day.

What I'm trying to add is a field to show information from an additional table, notably "inventory". The inventory table is basically every line item with a negative qty (inventory going out) as well as PO orders where we receive product with positive qty (inventory coming in). The sum of these for each SKU should be the total amount of product we have in stock.

The group is sorted by products.SKU in ascending order. The Select Expert formula is

{orderHeader.orderStatus} = "CLOSED" and
{orderHeader.orderDate} = {?date}

The group fields are:
SKU | Description | Sold | Average Sold | Inventory

I tried adding a formula for the inventory such as:

SUM({inventory.qty}, {products.SKU})

But this made the sold and average sold be huge numbers. For instance, when I did this, sold would jump from 4 or 5 on a specific SKU over a period of a couple days to around 1600.

I'm pretty new to Crystal...so maybe I'm just missing the bigger picture...but I could really use some ideas!! thanks.
LVL 2
mbeckmanAsked:
Who is Participating?
 
usarianOwnerCommented:
Ok, I think that your inventory is pulling every matching record for the SKU every time an order is placed for the product.  You need to narrow down the Inventory match to just entries that correspond to the exact particular sale.

In doing this, however, you will probably be excluding increases to your stock (positive inventory quantities), unless each time you receive stock it is always designated for a particular customer order going out as soon as you get it in (like a just-in-time system).

This is looking like a job for a subreport.
0
 
usarianOwnerCommented:
Did you add the inventory table to the existing report, or was it already there?  I'm thinking that the inventory table is not linked properly causing things to repeat for each row in the inventory table.

Make sure your Inventory table is ONLY linked to the SKU.  Alot of automatic linking will connect things like RowID (or whatever auto-increment column the database developer created for the primary key).

If the Inventory table also has, say a date column that matches the sale date of the product with the same qty of product depleted from inventory, you'll want to match that with a left outer join.

If this just doesn't match the organization of your database tables, you may need to use a subreport, which a whole different matter completely.

Usarian M. Skiff
0
 
mlmccCommented:
Agree.  How did you add the table?

mlmcc
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mbeckmanAuthor Commented:
The inventory table had been added by myself after the existing report already worked fine. So, I'm thinking you are correct in that there is a problem with the way I had linked it. Let me try to explain how it was before I added my table, and get your opinion on how I should link it.

Before I added Inventory, there were 3 tables: orderHeader, orderLineItems, and products. Each link was an INNER JOIN going from orderLineItems to each other table:

orderLineItems.SKU -> products.SKU
Join: INNER JOIN
Link: =

orderLineItems.orderNumber -> orderHeader.orderNumber
Join: INNER JOIN
Link: =

orderLineItems.orderGroup -> orderHeader.orderGroup
Join: INNER JOIN
Link: =

So that WORKS and returns the result set needed. When I link my Inventory table with the following link, the "Sold" and "Average Sold" values increase dramatically.

orderLineItems.SKU -> inventory.SKU
Join: INNER JOIN
Link: =

I just ran a query on the database to check the inventory values and they do all seem to be correct. So, maybe something is getting tossed into the aggregate functions of the other fields?

Many thanks...

Matt

0
 
mbeckmanAuthor Commented:
Update: After checking more values, some items do not actually show up correctly on the inventory count.
0
 
mbeckmanAuthor Commented:
Ok, so I decided to go with a subreport. What you said got me thinking that I should try it that way. Maybe not as efficient, but seems to work. I sent off the report to the GM to see if it's accurate or not. I'll let you know.

Thanks,

Matt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.