Discrepancy Report

[Cherrel]

Greetings to All!

I'm creating a Discrepancy Report of Inventory in my customer's warehouse.

I have created two seperate reports that query the database:

The first report (EQUIPMENT) goes out to the DB and groups based on Inventory Item Number (item.itemnum). The report produces a current quantity (invbalnaces.curbal) for each bin number (invbalances.binnum) that the inventory item resides in. In the Group Footer, I did a sum of invbalance to add up all the available items the database has for that item number. And finally, in the Report Footer, I have a sum of all the invbalances for a grand total of inventory items.

REPORT SAMPLE:

ITEM # DESCRIPTION BIN NUM QUANTITY IN BIN
------ ----------- ------- ---------------
00005 TRANSIT CASE 23-10-A 0
00005 TRANSIT CASE 23-10-B 1

---------
TOTAL NUMBER OF ITEMS FOR 00005: 1


00014 TRANSIT CASE-A 20-07-C 1
00014 TRANSIT CASE-B 22-01-A 1

---------
TOTAL NUMBER OF ITEMS FOR 00014: 2

The second report (INVENTORY) goes out to the DB and groups again based on Inventory Item Number (item.itemnum). This time the report tells me all the equipment numbers (equipment.eqnum) that are based on item numbers (this is because in our asset tracking program, "rotating" stock is tracked by item number). I have included the bin number (equipment.binnumber) that each equipment number resides in and in the Group Footer, a count of all equipment. And in the Report Footer, I have a count of all equipment in the database.

REPORT SAMPLE:

ITEM# EQUIPMENT# DESCRIPTION BIN NUMBER
----- ---------- ----------- ----------
00005 C649577 TRANSIT CASE 23-10-B

----------
TOTAL EQUIPMENT PIECES FOR ITEM# 00005 1


00014 C637954 TRANSIT CASE-A 20-07-C
----------
TOTAL EQUIPMENT PIECES FOR ITEM# 00014 1

(The EQUIPMENT REPORT says that there should be 2 equipment pieces when in actuality there is only 1 -- per the INVENTORY REPORT.)

Now, I trust both reports. The problem is the balances from the INVENTORY REPORT should match the EQUIPMENT REPORT and they do not. This is because of poor upkeep on the data by the warehouse people.

What I want is my new report to **ONLY** show me where the discrepancy is. (Where the equipment pieces do not match up to the inventory balances.) Right now, each report produces me a document of about 300+ pages. This is good, because if there is any question, I can refer to each report and see what my inventory and equipment modules are reporting.

Have any of you ever created a report like this? I have no idea where to even start. I have played around with the subreports but I'm not getting anywhere.

ANY ideas that you may have would be appricated. Please save me. I'm tired of looking at these two reports.

Thanks so much!
Cherrel.

 

[Roadkill150]

Just a quick overview, it sounds possibly that you could combine both reports into a single report. Since you are using the same grouping (inventory item number), what about placing both (item.itemnum) and (equipment.eqnum)in the detail sections, in instances there is a discrepancy, you should see the itemnumber twice, but the equipmentnumber only once.. then just use conditional suppression to hide all matching details, leaving only the discrepancies. This might be achieved through a subreport, but to have it in a comparison type role, the subreports would have to probably be in the detail section which means a really really bad performance hit (being that the subreport has to generate for every detail line). The other option is to insert the subreport, use underlay, and have the subreport data display side by with the group information, though that would require you to still look for the discrepancies (though the reports would be side by side and grouped on the same information).
Hope this helps,
-Bruce Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com

 

[Cherrel]

Bruce,

I really do not care about performance. They are only going to run this report at the end of the day to see if any of their transaction did not process or to see if balances somehow got off (they are going to do a complete audit of their inventory and I'm going to spend the weekend fixing the db so on Monday they will run w/o any quantities off).

So I guess a subreport is the way to go. So you would take both reports and create this subreport? I have never done anything thing like this. Can you (or someone out there) hold my hand and give me the step-by-step process?

Thanks,
Cherrel.