?
Solved

Inventory query

Posted on 2006-04-06
6
Medium Priority
?
579 Views
Last Modified: 2008-03-03
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.
0
Comment
Question by:mbeckman
  • 3
  • 2
6 Comments
 
LVL 5

Expert Comment

by:usarian
ID: 16396500
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 16397272
Agree.  How did you add the table?

mlmcc
0
 
LVL 2

Author Comment

by:mbeckman
ID: 16400761
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
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
LVL 2

Author Comment

by:mbeckman
ID: 16400835
Update: After checking more values, some items do not actually show up correctly on the inventory count.
0
 
LVL 5

Accepted Solution

by:
usarian earned 1000 total points
ID: 16400966
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
 
LVL 2

Author Comment

by:mbeckman
ID: 16438190
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question