Sales Reporting for Vendor Items

From time-to-time, key vendors request sales figures on items they supply. Below is a SQL Query, which creates a view that can be used as a foundation for a SmartList or SSRS Report to support this effort.

USE [COMPANYID]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE View [dbo].[_Vendor_Item_Sales]

as

Select

ivv.VNDITNUM Vendor_Item,

ivv.VENDORID Vendor_ID,

pmv.VENDNAME Vendor_Name,

SHL.ITEMNMBR Item_ID,

case shl.soptype

       when 1 then 'Quote'

       when 2 then 'Order'

       when 3 then 'Invoice'

       when 4 then 'Return'

       when 5 then 'Back Order'

       when 6 then 'Fulfillment Order'

       else 'ERROR'

End Document_Type,

shl.SOPNUMBE Document_Number,

(shl.LNITMSEQ/16384) Line_Number,

SHL.ITEMDESC Item_Description,

shl.UOFM Unit_of_Measure,

convert(varchar(10),shh.DOCDATE,101) Document_Date,

CONVERT(varchar(10),shh.INVODATE,101) Invoice_Date,

shl.CNTCPRSN Ship_to_Contact,

shl.ShipToName Ship_to_Name,

shl.ADDRESS1 Address_1,

shl.ADDRESS2 Address_2,

shl.ADDRESS3 Address_3,

shl.CITY Ship_City,

shl.STATE Ship_State,

shl.ZIPCODE Ship_Zip,

shl.QUANTITY Quantity,

shl.QTYTOINV Invoice_Quantity,

cast(shl.UNITPRCE as money) Unit_Price,

cast(shl.XTNDPRCE as money) Extended_Price,

cast(shl.UNITCOST as money) Unit_Cost,

cast(shl.EXTDCOST as money) Extended_Cost

from SOP30200 SHH

left join SOP30300 SHL on SHH.SOPTYPE = SHL.SOPTYPE and SHH.SOPNUMBE = SHL.SOPNUMBE

left join IV00101 IVI on shl.ITEMNMBR = IVI.ITEMNMBR

left join IV00103 IVV on ivi.ITEMNMBR = ivv.ITEMNMBR

left join PM00200 PMV on ivv.VENDORID = pmv.VENDORID

GO

GRANT SELECT ON _Vendor_Item_Sales TO DYNGRP

GO

Engineering Change Request View SQL Script

Since Dynamics GP Manufacturing originally began its life as a third party product, some of the core features supporting Manufacturing modules never made it into the product. For instance, Quality Assurance, Engineering Change Management, Routings, Forecasting and Job Costing do not have any out-of-the-box SmartLists.

I typically wind up using SQL Views, SmartList Builder and now SmartList Designer to create custom built SmartLists for these modules. In this post, I have included a SQL Script, which can be used to create a SQL View as the foundation for SmartLists or SQL Server Reporting Services (SSRS) Reports for Engineering Change Requests. Here it is:

Create VIEW Engineering_Change_Request

AS

select

DATEENTERED_I Date_of_Request,

CASE WHEN CUST.CUSTNMBR is not null then CUST.CUSTNAME

       Else  ''

End Customer_Name,

CASE WHEN CUST.CUSTNMBR is not null then CUST.CUSTNMBR

       ELSE ''

End Customer_ID,

ECMH.ENDDATE ECR_Complete_by_Date,

ECMH.ITEMNMBR Part_Number,

ECMH.ITEMDESC Part_Description,

'' Old_Rev,

ECMH.REVISIONLEVEL_I New_Rev,

ECMH.ECM_Short_Description ECR_Description,

DoEC.text1 BOM_Changes,

RFC.text2 Reason_for_Change,

NCO.text3 Notify_Customer,

EI.text4 Expected_Impact,

ECML.DISPOSITIONNOTES_I WIP_Instructions

from EC010031 ECMH

inner join EC050031 ECML on ECMH.ECNumber = ECML.ECNumber

left join RM00101 CUST on ECMH.CUSTNMBR = CUST.CUSTNMBR

left join EC010100 DoEC on ECMH.ECNumber = DoEC.ECNumber

left join EC010200 RFC on ECMH.ECNumber = RFC.ECNumber

left join EC010300 NCO on ECMH.ECNumber = NCO.ECNumber

left join EC010400 EI on ECMH.ECNumber = EI.ECNumber

GO

Grant Select on Engineering_Change_Request to DYNGRP  

SQL Views, NULL values and SmartList tools

I discovered something some time ago, and could have sworn I wrote a blog article about it, because I thought it was a tip that should be shared with the world. Today, I stumbled across the same problem again, and went to look up my post... yes I refer back to my own posts to refresh my recollection from time to time.

Anyway, I discovered I had neglected to write an article about it, but found the issue mentioned in a broader article written by luminary Dave Musgrave called Understanding how Dynamics GP works with SQL

In this article Dave explains that since Dynamics GP grew up on databases like Btieve and Ctree, and these databases typically avoided the occurrence of NULL values in the data set by initializing number and currency fields with Zero and String fields with blanks. Consequently, Dynamics GP doesn't intuitively know how to deal with NULL values.

This is important for users who are working with SmartList tools like SmartList Builder or Designer.

One of the best inherent features in SmartLists is the ability to search fields based on values or the lack thereof. If a NULL value is returned in a SmartList field, then there is no way to search/restrict your results based on the return of a NULL value.

The easiest way to resolve this issue is during the creation of the view your SmartList is built on. If you have a field returning NULL values, it is best to transform these null values into blanks instead. 

Here is an example of how to make this work.

Select 
CASE WHEN TABLE.FIELD IS NULL THEN ''
            ELSE TABLE.FIELD
END 'Field Label'
FROM TABLE

This will result in SQL and ultimately your SmartList column returning either nothing or the actual value in the field. The value nothing can be returned by searching for blanks in the field.

Failure to do this will both clutter your SmartList and limit the its functionality, so I highly recommend taking this into account when building SQL Queries, Views and SmartLists. 

NOTE: I did in fact write a blog post about this last October, and apparently mislaid it. Here is a link.

No filters on Null Values in SmartLists

eOne Announces new features for SmartList Builder

eOne, the original makers of SmartLists and SmartList Builders have recently announced the release of new features in SmartList Builder.

I am super excited about Two of these features!

1. Easy modification of EXISTING SmartLists... Quite possibly the most requested feature in SmartList history.  The ability to use existing SmartLists as a foundation for modification is revolutionary.

2. SQL data pulls on the fly.  If you've ever used SQL to create a view and then deployed a new SmartList using the view, you'll understand why I am so excited about the addition of this feature.

Check out this blog post from eOne to get description of all the new features of SmarList Builder:

http://blog.eonesolutions.com/2013/12/look-whats-coming-to-smartlist-builder.html