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  

Management Reporter Installation Checklist 2.0

I have noticed numerous SOLID blog posts, which walk through the installation steps for Management Reporter!  The Dynamics Community does not disappoint; the level of sharing and collaboration frankly astounds me. These blog posts include screen shots, and detailed instructions. 

Here is an especially good example, which includes links to other related topics Azure Curve How to Install Management Reporter Server

Management Reporter installation woes still seem to be the source of many posts on the community forums.  The reason, the DEVIL is in the DETAILS.

I originally posted the checklist below to address the numerous community posted related to Management Reporter Installation issues. I have since installed Management Reporter CU13 on SQL 2014 connected to Dynamics GP 2015 using my original checklist, and found a few wrinkles.

What follows is a revised and extended checklist.  

Make sure install user is BOTH a Domain and Machine Administrator

Add Server Role - Web Server to target server

Add Server Role - Application Server to target server

Enable .Net and ASP.Net (appropriate versions)

Enable IIS 6 Metabase Compatibility Feature for IIS 7 (and tools)

Enable WCF HTTP Activation (not to be confused with TCP/IP activation)

Disable User Account Control on target server

Reboot Machine - this is an optional step but should be done if issues occur

Run installation from a folder with short path-name (something just off root)

Run installation as Administrator (elevated privileges are necessary)

Do NOT select NT AUTHORITY accounts - use an Active Directory Account 
Ensure the user you have running the application pool has "log on as batch job" rights in the local security policy of the machine.
Remember to Register Management Reporter - default installation is Evaluation Copy, no reminder to register, until the trial period expires!

Set refresh cycle for Management Reporter to something better than every second

Set up ISO codes and currency access rights for Multicurrency in all companies

Set GL Integration flag in all companies

Deploy Management Report Integration in Report Settings 

Here are a few more links to related posts to get you through the final steps

Microsoft Knowledge Base Article 861642 - Currency Issues

Batch File to Restart Management Reporter Services

Enable Dynamics GP 2015 Management Reporter Integration
Change Management Reporter Refresh Cycle