Custom Inventory Report GP 2010

(0) Share

Report

Posted on by Community Member Microsoft Employee

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]

/****** Object: StoredProcedure [dbo].[xxx_InventoryReport] Script Date: 12/02/2013 16:46:09 ******/

SET

ANSI_NULLS ON

SET

QUOTED_IDENTIFIER ON

ALTER

procedure [dbo].[tmc_InventoryReport]

@ITEMNMBR

varchar(31) = '%'

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

All responses (9)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Custom Inventory Report GP 2010

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.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Custom Inventory Report GP 2010

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.
Frank Hamelly | MVP... 4,029 Super User 2024 Season 2 on at

Like (0)

Report

RE: Custom Inventory Report GP 2010

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.
Frank Hamelly | MVP... 4,029 Super User 2024 Season 2 on at

Like (0)

Report

RE: Custom Inventory Report GP 2010

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?
Community Member Microsoft Employee on at

Like (0)

Report

RE: Custom Inventory Report GP 2010

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.
Frank Hamelly | MVP... 4,029 Super User 2024 Season 2 on at

Like (0)

Report

RE: Custom Inventory Report GP 2010

Rudy, can you post a screenshot of the report itself? Would like to see the columns, subtotals, totals, etc.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Custom Inventory Report GP 2010

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.
Frank Hamelly | MVP... 4,029 Super User 2024 Season 2 on at

Like (0)

Report

RE: Custom Inventory Report GP 2010

I agree with Richard. What is the nature of the report? SSRS should be able to handle almost any requirement you throw at it.
Suggested answer

Richard Whaley 25,195 on at

Like (0)

Report

RE: Custom Inventory Report GP 2010

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.