You’re offline. This is a read only version of the page.
Hello,
Some time ago we had a vendor create a custom inventory report for us using SQL 2008, SQL Server Business Intelligence Development Studio, and SRSS. This report has never worked correctly. This has become a high priority item for us, so I could sure use some help. The SQL code is posted below:
USE
[UNCHM]
GO
/****** Object: StoredProcedure [dbo].[xxx_InventoryReport] Script Date: 12/02/2013 16:46:09 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
ALTER
procedure [dbo].[tmc_InventoryReport]
@ITEMNMBR
varchar(31) = '%'
as
select
rtrim(im.ITEMNMBR) as ITEMNMBR,
rtrim(im.ITEMDESC) as ITEMDESC,
rtrim(im.USCATVLS_2) as USCATVLS_2,
rtrim(im.ITMGEDSC) as ITMGEDSC,
rtrim(case when len(iq.LOCNCODE) > 0 then iq.LOCNCODE else 'All Sites' end) as LOCNCODE,
iq.QTYONHND,
iq.ATYALLOC,
(iq.QTYONHND - iq.ATYALLOC) as QTYAVAIL,
iq.QTYONORD,
rtrim(isnull(il.LOTNUMBR,'')) as LOTNUMBR,
(isnull(il.QTYRECVD,0) - isnull(il.QTYSOLD,0)) as LOTQTYONHND,
isnull(il.ATYALLOC,0) as LOTATYALLOC,
isnull(il.QTYTYPE,1) as QTYTYPE
from
IV00101 as im
left outer join IV00102 as iq on im.ITEMNMBR = iq.ITEMNMBR
left outer join IV00300 as il on im.ITEMNMBR = il.ITEMNMBR and iq.LOCNCODE = il.LOCNCODE
where
iq.QTYONHND <> 0 and
im.ITEMNMBR like @ITEMNMBR and
il.QTYTYPE <> 5
order by
ITEMNMBR,
LOCNCODE
If you run this query as is, there will be multiple rows of the same PID with the same lot number, but with different quantities. In some cases the quantity avialable will be double the actual quantity. My knowledge of T-SQL is pretty limited, so any help would be appreciated.
*This post is locked for comments
I have been working with Victoria on this and made some progress. I've run Inventory Reconcile on individual PIDs and that fixed some items. We also noticed that the SQL query, for some reason, was showing mismatched ATYALLOC and LOTATYALLOC, even though they should be the same? This appears to be what is screwing the report up.
I will try and run Inventory Reconcile on all the PIDS and see what happens.
Well,
The data is pulled form GP/SQL tables via the SQL query from my first post. Then BIDS is used for the report format with a couple simple operations (subraction) to arrive at the final product.
I am familiar Victoria and have used her site on several occasions.
BTW, there is nobody better at report development than Victoria Yudin. If you want this report fixed quickly and correctly, it would pay you to reach out to her at victoria@flex-solutions.com.
Looks like a pretty simple report, especially given we can ignore the hyperlinks. Are the data issues with the main report? I assume there are subreports that provide the detail data?
Frank,
Here you go. Ignore the requirements for hyperlinking. I also forgot to mention, the report format looks good in SRSS, just the data in the report is bad.
Rudy, can you post a screenshot of the report itself? Would like to see the columns, subtotals, totals, etc.
Thanks for the replies.
Richard, a contractor wrote the SQL, BIDS, and SRSS code, and you're probably correct that this all could have been done in SRSS. It sure would have been simpler and less complicated. I believe the contractor was trying to show off his mad skills to the noob. So I guess the bottom line here is I don't have the expertise, or time to correct the problem, since this was pretty much dropped in my lap a short time ago, and needs to be completed by the 14th of Dec. Lol. I will check out the book.
Frank, the generated report will be used to replace a hand jammed excel report, that will be used by our sales team. It's not a complex report at all. I could try to post the template.
I suspect I may have to look for another coder to get this done correctly. Any suggestions would be appreciated.
I agree with Richard. What is the nature of the report? SSRS should be able to handle almost any requirement you throw at it.
This is a lot of code in SQL for a report. Is there something special in the requirements that demand all of this? SSRS is very powerful and the only time I have seen external stored procedures is when a developer wrote the report. Why can't you simply use SSRS?
BTW..we have a book that teaches the use of the SSRS reporting tool. It is available from our web site.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (