Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

Custom Inventory Report GP 2010

Posted on by 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]

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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 4,029 Super User 2024 Season 2 on at
    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, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 4,029 Super User 2024 Season 2 on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 4,029 Super User 2024 Season 2 on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 4,029 Super User 2024 Season 2 on at
    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 Profile Picture
    Richard Whaley 25,195 on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans