Total Inventory Report from Report Builder

Yahoo Archive
1
Thanks Troy,
nothing can be easy can it? I also discovered that the
PartWhse.OnHandQty does not
have quantities in non-nettable bins. Another problem to overcome. I live
by the motto
that "what doesn't kill you will make you stronger".

kind regards,
2
Can this be done with report builder? I want to link the PartBin, DMRHead,
RMAHead together to report ALL inventory
in the house. I would like to aggregate on the partnumber and calculate the
average material cost of all parts that have
a balance. My experience thus far has been that when I join the DMRHead
with the PartBin, Report Builder propagates
the PartBin.OnHandQty records for each DMRHead.TotalDiscrepantQty record.
So if I have many DMRHead records,
the sum of my ParBin.OnHandQty is vastly overstated.
EXAMPLE: OnHandQty Bin 1 1500 DMR 1
TotalDiscrepantQty 50 closed
Bin 2 1000 DMR
2 TotalDiscrepantQty 100 open
Bin 3 500 DMR
3 TotalDiscrepantQty 300 closed
DMR
4 TotalDiscrepantQty 50 closed
DMR
5 TotalDiscrepantQty 50 closed
DMR
6 TotalDiscrepantQty 100 closed

In the above example I will get 6 repeats of Bin1, Bin2 and Bin3, tripling
my onhand quantity. Is there a way around
this, or am I spinning my wheels. As always, thanks for any help.




[Non-text portions of this message have been removed]
3
Dick,
In lots of trying, I have never found a way around your situation other than by using a Subreport to get the OnhandQty (which requires Crystal). By eliminating the PartBin table from your main table joins, you will get the one record per part that you need. Then insert the ONHANDQTY subreport on the line where you want the OnHandQty reported (shrunk down to the size of the field). Your subreport will be linked by partnumber to the main report. It will group by Wharehouse code, with a SUM of the OnHandQty in the Report Footer. Suppress all other sections in the Subreport, leaving only the Report Footer showing.

Troy

----- Original Message -----
From: PLAMAN DICK
To: Vantage (vantage@yahoogroups.com)
Sent: Thursday, December 19, 2002 8:56 AM
Subject: [Vantage] Total Inventory Report from Report Builder


Can this be done with report builder? I want to link the PartBin, DMRHead,
RMAHead together to report ALL inventory
in the house. I would like to aggregate on the partnumber and calculate the
average material cost of all parts that have
a balance. My experience thus far has been that when I join the DMRHead
with the PartBin, Report Builder propagates
the PartBin.OnHandQty records for each DMRHead.TotalDiscrepantQty record.
So if I have many DMRHead records,
the sum of my ParBin.OnHandQty is vastly overstated.
EXAMPLE: OnHandQty Bin 1 1500 DMR 1
TotalDiscrepantQty 50 closed
Bin 2 1000 DMR
2 TotalDiscrepantQty 100 open
Bin 3 500 DMR
3 TotalDiscrepantQty 300 closed
DMR
4 TotalDiscrepantQty 50 closed
DMR
5 TotalDiscrepantQty 50 closed
DMR
6 TotalDiscrepantQty 100 closed

In the above example I will get 6 repeats of Bin1, Bin2 and Bin3, tripling
my onhand quantity. Is there a way around
this, or am I spinning my wheels. As always, thanks for any help.




[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]
4
This can be done in Access using ODBC drivers. You create several small
queries to get the different part, bin or warehouse information and then
pull all the small queries into a big query using only the information you
need from each query. I am sure you can do the same type of thing using
subreports in Crystal, but I only use Access. Hope this helps some.

-----Original Message-----
From: PLAMAN DICK [mailto:dick.plaman@...]
Sent: Thursday, December 19, 2002 8:57 AM
To: Vantage (vantage@yahoogroups.com)
Subject: [Vantage] Total Inventory Report from Report Builder


Can this be done with report builder? I want to link the PartBin, DMRHead,
RMAHead together to report ALL inventory
in the house. I would like to aggregate on the partnumber and calculate the
average material cost of all parts that have
a balance. My experience thus far has been that when I join the DMRHead
with the PartBin, Report Builder propagates
the PartBin.OnHandQty records for each DMRHead.TotalDiscrepantQty record.
So if I have many DMRHead records,
the sum of my ParBin.OnHandQty is vastly overstated.
EXAMPLE: OnHandQty Bin 1 1500 DMR 1
TotalDiscrepantQty 50 closed
Bin 2 1000 DMR
2 TotalDiscrepantQty 100 open
Bin 3 500 DMR
3 TotalDiscrepantQty 300 closed
DMR
4 TotalDiscrepantQty 50 closed
DMR
5 TotalDiscrepantQty 50 closed
DMR
6 TotalDiscrepantQty 100 closed

In the above example I will get 6 repeats of Bin1, Bin2 and Bin3, tripling
my onhand quantity. Is there a way around
this, or am I spinning my wheels. As always, thanks for any help.




[Non-text portions of this message have been removed]



Yahoo! Groups Sponsor

ADVERTISEMENT

<http://rd.yahoo.com/M=242716.2759770.4138320.2225243/D=egroupweb/S=17050071
83:HM/A=1321406/R=0/*http://www.e-dealsdaily.com/soundbug/>

<http://us.adserver.yahoo.com/l?M=242716.2759770.4138320.2225243/D=egroupmai
l/S=:HM/A=1321406/rand=818731022>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]
5
Pending what version you are on, the PARTWHSE table was added in 5.0 or 5.1
which version I'm not sure. It holds a OnHandQty field which is the total
parts in inventory for that part in a given warehouse. This would be a much
better choice if your just looking for totals and don't want to add up bin
quantities in a report.

Patrick

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Thursday, December 19, 2002 8:08 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Total Inventory Report from Report Builder


Dick,
In lots of trying, I have never found a way around your situation other
than by using a Subreport to get the OnhandQty (which requires Crystal). By
eliminating the PartBin table from your main table joins, you will get the
one record per part that you need. Then insert the ONHANDQTY subreport on
the line where you want the OnHandQty reported (shrunk down to the size of
the field). Your subreport will be linked by partnumber to the main report.
It will group by Wharehouse code, with a SUM of the OnHandQty in the Report
Footer. Suppress all other sections in the Subreport, leaving only the
Report Footer showing.

Troy

----- Original Message -----
From: PLAMAN DICK
To: Vantage (vantage@yahoogroups.com)
Sent: Thursday, December 19, 2002 8:56 AM
Subject: [Vantage] Total Inventory Report from Report Builder


Can this be done with report builder? I want to link the PartBin,
DMRHead,
RMAHead together to report ALL inventory
in the house. I would like to aggregate on the partnumber and calculate
the
average material cost of all parts that have
a balance. My experience thus far has been that when I join the DMRHead
with the PartBin, Report Builder propagates
the PartBin.OnHandQty records for each DMRHead.TotalDiscrepantQty record.
So if I have many DMRHead records,
the sum of my ParBin.OnHandQty is vastly overstated.
EXAMPLE: OnHandQty Bin 1 1500 DMR 1
TotalDiscrepantQty 50 closed
Bin 2 1000 DMR
2 TotalDiscrepantQty 100 open
Bin 3 500 DMR
3 TotalDiscrepantQty 300 closed
DMR
4 TotalDiscrepantQty 50 closed
DMR
5 TotalDiscrepantQty 50 closed
DMR
6 TotalDiscrepantQty 100 closed

In the above example I will get 6 repeats of Bin1, Bin2 and Bin3, tripling
my onhand quantity. Is there a way around
this, or am I spinning my wheels. As always, thanks for any help.




[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
6
Thank you Patrick, Troy and Joanne.
Your advice to use the Part.partwhse table did the trick. (had to get to
only one record)
You guys are the best!

thanks again,
7
Dick,
Using the PartWhse will work as long as your part is only in one warehouse. However, if you have quantities in CentralWhse, and also quantities for the same part in Whse2, then your OnhandQty's for your report will not be accurate because you will only see the OnHandQty for the first warehouse that happens to come up on that record. If you only use one Warehouse you should be fine.

Just thought you might want to double-check.

Troy Funte
Liberty Electronics

----- Original Message -----
From: PLAMAN DICK
To: 'vantage@yahoogroups.com'
Sent: Thursday, December 19, 2002 9:45 AM
Subject: RE: [Vantage] Total Inventory Report from Report Builder


Thank you Patrick, Troy and Joanne.
Your advice to use the Part.partwhse table did the trick. (had to get to
only one record)
You guys are the best!

thanks again,


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]