SQL View to Return Sales By Customer By Fiscal Year

Microsoft Dynamics GPThe last post was a SQL view to return purchases by vendor by fiscal year in an update of a prior script which only returned the information by the calendar year; I did a similar view at the time for the Sales series which also returned information for the calendar year. This post is an update of that one to return the information linking into the Financial Calendar setup to return the sales by customer by financial year.

IF OBJECT_ID (N'uv_AZRCRV_SalesByCustomerByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesByCustomerByYear
GO
CREATE VIEW uv_AZRCRV_SalesByCustomerByYear AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Receivables Transactions'].CUSTNMBR AS 'Customer ID'
	,['RM Debtor MSTR'].CUSTNAME AS 'Customer Name'
	,['RM Debtor MSTR'].CUSTCLAS AS 'Class ID'
	,['Receivables Transactions'].DOCDATE AS 'Year'
	,CASE WHEN ['RM Debtor MSTR'].INACTIVE = 1 THEN
		'Inactive'
	ELSE
		'Active'
	END AS 'Status'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].SLSAMNT ELSE ['Receivables Transactions'].SLSAMNT * -1 END) AS 'Purchases Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TRDISAMT * -1 ELSE ['Receivables Transactions'].TRDISAMT END) AS 'Trade Discount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].FRTAMNT ELSE ['Receivables Transactions'].FRTAMNT * -1 END) AS 'Freight'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].MISCAMNT ELSE ['Receivables Transactions'].MISCAMNT * -1 END) AS 'Miscalleneous'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TAXAMNT ELSE ['Receivables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].DOCAMNT ELSE ['Receivables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
	(SELECT
		CUSTNMBR
		,YEAR1 AS DOCDATE
		,GLPOSTDT
		,DOCNUMBR
		,RMDTYPAL
		,SLSAMNT
		,TRDISAMT
		,FRTAMNT
		,MISCAMNT
		,TAXAMNT
		,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
	FROM
		RM20101
	INNER JOIN
		SY40101
			ON
				DOCDATE BETWEEN FSTFSCDY AND LSTFSCDY
	WHERE
		VOIDSTTS >= 0
	AND
		RMDTYPAL IN (1,3,4,7,8)
	UNION ALL
		SELECT
			CUSTNMBR
			,YEAR1 AS DOCDATE
			,GLPOSTDT
			,DOCNUMBR
			,RMDTYPAL
			,SLSAMNT
			,TRDISAMT
			,FRTAMNT
			,MISCAMNT
			,TAXAMNT
			,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
		FROM
			RM30101
		INNER JOIN
			SY40101
				ON
					DOCDATE BETWEEN FSTFSCDY AND LSTFSCDY
		WHERE
			VOIDSTTS >= 0
		AND
			RMDTYPAL IN (1,3,4,7,8)
	) AS ['Receivables Transactions']
LEFT JOIN
	RM00101 AS ['RM Debtor MSTR']
		ON
			['Receivables Transactions'].CUSTNMBR >= ['RM Debtor MSTR'].CUSTNMBR
GROUP BY
	['Receivables Transactions'].CUSTNMBR
	,['RM Debtor MSTR'].CUSTNAME
	,['RM Debtor MSTR'].CUSTCLAS
	,['RM Debtor MSTR'].INACTIVE
	,['Receivables Transactions'].DOCDATE
GO
GRANT SELECT ON uv_AZRCRV_SalesByCustomerByYear TO DYNGRP
GO

The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.

SQL View to Return Purchases By Vendor By Fiscal Year

Microsoft Dynamics GPI did a post a few weeks ago which included a SQL view which could be used to return a list of purchases by creditors/vendors by year. I wrote the view for a client who operates a financial year which is the same as the calendar year.

However, most of my clients use different financial years, so I have also created a script which returns the same information, but links to the Financial Calendar to determine which financial, rather than calendar, year a transaction is within:

IF OBJECT_ID (N'uv_AZRCRV_PurchasesByVendorByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_PurchasesByVendorByYear
GO
CREATE VIEW uv_AZRCRV_PurchasesByVendorByYear AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Payables Transactions'].VENDORID AS 'Vendor ID'
	,['PM Vendor Master'].VENDNAME AS 'Vendor Name'
	,['PM Vendor Master'].VNDCLSID AS 'Class ID'
	,['Payables Transactions'].DOCDATE AS 'Year'
	,CASE WHEN ['PM Vendor Master'].VENDSTTS >= 1 THEN
		'Active'
	WHEN ['PM Vendor Master'].VENDSTTS >= 2 THEN
		'Inctive'
	ELSE
		'Temporary'
	END AS 'Status'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].PRCHAMNT ELSE ['Payables Transactions'].PRCHAMNT * -1 END) AS 'Purchases Amount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TRDISAMT * -1 ELSE ['Payables Transactions'].TRDISAMT END) AS 'Trade Discount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].FRTAMNT ELSE ['Payables Transactions'].FRTAMNT * -1 END) AS 'Freight'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].MSCCHAMT ELSE ['Payables Transactions'].MSCCHAMT * -1 END) AS 'Miscalleneous'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TAXAMNT ELSE ['Payables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].DOCAMNT ELSE ['Payables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
	(SELECT
		['PM Transaction OPEN File'].VENDORID
		,['Period Header'].YEAR1 AS DOCDATE
		,['PM Transaction OPEN File'].PSTGDATE
		,['PM Transaction OPEN File'].DOCNUMBR
		,['PM Transaction OPEN File'].DOCTYPE
		,['PM Transaction OPEN File'].PRCHAMNT
		,['PM Transaction OPEN File'].TRDISAMT
		,['PM Transaction OPEN File'].FRTAMNT
		,['PM Transaction OPEN File'].MSCCHAMT
		,['PM Transaction OPEN File'].TAXAMNT
		,['PM Transaction OPEN File'].DOCAMNT
	FROM
		PM20000 AS ['PM Transaction OPEN File']
	INNER JOIN
		SY40101 AS ['Period Header']
			ON
				['PM Transaction OPEN File'].DOCDATE BETWEEN ['Period Header'].FSTFSCDY AND ['Period Header'].LSTFSCDY
	WHERE
		VOIDED >= 0
	AND
		DOCTYPE <= 5
	UNION ALL
		SELECT
			['PM Paid Transaction History File'].VENDORID
			,['Period Header'].YEAR1 AS DOCDATE
			,['PM Paid Transaction History File'].PSTGDATE
			,['PM Paid Transaction History File'].DOCNUMBR
			,['PM Paid Transaction History File'].DOCTYPE
			,['PM Paid Transaction History File'].PRCHAMNT
			,['PM Paid Transaction History File'].TRDISAMT
			,['PM Paid Transaction History File'].FRTAMNT
			,['PM Paid Transaction History File'].MSCCHAMT
			,['PM Paid Transaction History File'].TAXAMNT
			,['PM Paid Transaction History File'].DOCAMNT
		FROM
			PM30200 AS ['PM Paid Transaction History File']
	INNER JOIN
		SY40101 AS ['Period Header']
			ON
				['PM Paid Transaction History File'].DOCDATE BETWEEN ['Period Header'].FSTFSCDY AND ['Period Header'].LSTFSCDY
		WHERE
			VOIDED >= 0
		AND
			DOCTYPE <= 5
	) AS ['Payables Transactions']
LEFT JOIN
	PM00200 AS ['PM Vendor Master']
		ON
			['Payables Transactions'].VENDORID >= ['PM Vendor Master'].VENDORID
GROUP BY
	['Payables Transactions'].VENDORID
	,['PM Vendor Master'].VENDNAME
	,['PM Vendor Master'].VNDCLSID
	,['PM Vendor Master'].VENDSTTS
	,['Payables Transactions'].DOCDATE
GO
GRANT SELECT ON uv_AZRCRV_PurchasesByVendorByYear TO DYNGRP
GO

The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.

UPDATE: Removed Format on DOCDATE. Thanks to Tim Wappat for pointing out the error.

Security Views For Use In SmartList Designer: Group Based Company Access In Management Reporter

Microsoft Dynamics GPA while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.

This, the second Management Reporter security script, shows security for users as granted by their Group membership. the previous post, on Friday, showed the user based company access.

The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.

IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterGroupBasedSecurity', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetManagementReporterGroupBasedSecurity
GO
CREATE VIEW uv_AZRCRV_GetManagementReporterGroupBasedSecurity AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Security User'].UserName AS 'Username'
	,['Security User Principal'].Name AS 'Domain Name'
	,['Security User'].LastLoginAttempt AS 'Last Login Attempt'
	,CASE ['Security User'].RoleType
		 WHEN 2 THEN
			'Viewer'
		 WHEN 3 THEN
			'Generator'
		 WHEN 4 THEN
			'Designer'
		 WHEN 5 THEN
			'Administrator'
		ELSE
			'None'
		END AS 'Role'
		,['Security Group Principal'].Name AS 'Group Name'
		,['Security Group Principal'].Description AS 'Group Description'
		,['Control Company'].Code AS 'INTERID'
		,['Control Company'].Name AS 'Company Name'
 FROM 
	Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK)
INNER JOIN
	Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security User Principal'].ID
LEFT JOIN
	Reporting.SecurityGroupUser AS ['Security Group User'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security Group User'].UserID
LEFT JOIN
	Reporting.SecurityPrincipal AS ['Security Group Principal']  WITH (NOLOCK)
		ON
			 ['Security Group User'].GroupID = ['Security Group Principal'].ID
LEFT JOIN
	Reporting.SecurityCompanyPermission AS ['Security Company Group Permission'] WITH (NOLOCK)
		ON
			['Security Group Principal'].ID = ['Security Company Group Permission'].PrincipalID
LEFT JOIN
	Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK)
		ON
			['Security Company Group Permission'].CompanyID = ['Control Company'].ID
GO
GRANT SELECT ON uv_AZRCRV_GetManagementReporterGroupBasedSecurity TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Security Views For Use In SmartList Designer: User Based Company Access In Management Reporter

Microsoft Dynamics GPA while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.

This first script returns the security based on how the user is configured; the view I will post on Monday shows Group based security.

The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.

IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterUserBasedSecurity', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity 
GO
CREATE VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Security User'].UserName AS 'Username'
	,['Security User Principal'].Name AS 'Domain Name'
	,['Security User'].LastLoginAttempt AS 'Last Login Attempt'
	,CASE ['Security User'].RoleType
		 WHEN 2 THEN
			'Viewer'
		 WHEN 3 THEN
			'Generator'
		 WHEN 4 THEN
			'Designer'
		 WHEN 5 THEN
			'Administrator'
		ELSE
			'None'
		END AS 'Role'
	,['Control Company'].Code AS 'INTERID'
	,['Control Company'].Name AS 'Company Name'
FROM 
	ManagementReporter.Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK)
INNER JOIN
	ManagementReporter.Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security User Principal'].ID
LEFT JOIN
	ManagementReporter.Reporting.SecurityCompanyPermission AS ['Security Company Permission'] WITH (NOLOCK)
		ON
			['Security User Principal'].ID = ['Security Company Permission'].PrincipalID
LEFT JOIN
	ManagementReporter.Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK)
		ON
			['Security Company Permission'].CompanyID = ['Control Company'].ID
GO
GRANT SELECT ON uv_AZRCRV_GetManagementReporterUserBasedSecurity TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Dynamics GP Tech Conference 2017: Power BI Enhancements

Microsoft Dynamics GPMicrosoft Dynamics GPThis post is part of the series I am doing on the Dynamics GP Tech Conference 2017.

Power BI is more the area of Belinda Allen or Mark Polino than mine, but I’ll be getting more of a look at it soon as I have one of my team about to take a more in depth look, so it is nice to hear there will be more new stuff coming in the next version of Dynamics GP:

  • Support for paging in OData Services
  • Support for filtering in OData Services
  • Support for OData v4
  • GP Power BI Content Pack – I need more information on this, but it sounds like a set of pre-packaged reports which will be welcome.

Dynamics GP Tech Conference 2017: New Features In Workflow

Microsoft Dynamics GPThis post is part of the series I am doing on the Dynamics GP Tech Conference 2017.

There have been new features in other areas, which I will come back to, but Workflow is sort of “my thing”, so I’m going to mention the new features which are being added to Workflow 4.0 (I apparently missed the boat somewhere on the name change to Workflow 3.0?).

The coming new features are:

  • Reminder emails for Workflow – this is a feature which has been requested of ym by a couple of clients recently, so nice that I’ll be able to go back and tell them it will be in the next version.
  • Copy Workflow Step – nice to see this one added as it was one of my suggestions on MS Connect.
  • Reporting for Workflow – I’m not sure what is coming in this, but it would be nice to have some default reports showing the history of documents as they passed through the Workflow.
  • Payables Transaction Workflow – additional fields
  • PO Workflow Messages – additional fields – there are never enough fields available for the Workflow, so nice to know more will be available by default.
  • Add EFT/Bank Details to Vendor Approval Workflow – if this one is as it sounds, that will be fantastic as so many clients want this functionality. I think it only took so long as EFT isn’t anywhere near as big in the US as it is in the UK.
  • GL Account Workflow Approval – I have a couple of clients who can ditch some customisations with this one. And a few more who will be delighted so see this added.
  • Purchase Receiving Workflow – I have a few customers waiting for this one to be introduced.
  • Purchase Enter/Match Invoice Workflow – as above, a few clients are waiting for this one.

I know we can’t have everything we want, and the above are good additions to Workflow, but I still want more!

Click to show/hide the Dynamics GP Tech Conference 2017 Series Index

Deploy SQL View to All Databases

Microsoft Dynamics GPI have a few clients who have quite a few company databases in Microsoft Dynamics GP. One of them has well over a hundred live companies. This can make deploying reports somewhat long winded when you need to deploy an SQL view to all of the databases.

Fortunately, Microsoft SQL Server has ways and means which you can use to make the process a lot easier. In this case, I am using a SQL cursor to select all of the databases from the Company Master (SY01500) and loop through them to deploy the view; the deployment is in three phases:

  • Delete any existing view with the same name (this allows for an easy redeployment).
  • Create the view.
  • Grant the SELECT permission to DYNGRP.
  • The script is posted below with a simplified PO report being created; the view name is set in the highlighted parameter near the top of the script.

    The large highlighted section is where you please the content of the view which is to be deployed.
    Continue reading “Deploy SQL View to All Databases”

    Microsoft Dynamics GP 2016 R2 Financial Dashboard Error

    Microsoft Dynamics GPWhen reviewing some dashboards with a client a while ago we encountered an error launching the Financial Dashboard:

    Microsoft Excel - We found a problem with some content in 'T16R2 Financial Dashboard.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

    Microsoft Excel

    We found a problem with some content in 'T16R2 Financial Dashboard.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

    Continue reading “Microsoft Dynamics GP 2016 R2 Financial Dashboard Error”

    SQL Script Linking Purchase Orders to Purchase Requisitions

    Microsoft Dynamics GPI have been doing a lot of work recently on Purchase Order Processing and Workflow 2.0. One of the requests I had was for a SmartList report which allowed the purchase order (PO) to be compared back to the purchase requisition (PR) it originated from. I had to take a few minutes to investigate as I knew the link from PR to PO was maintained within the system, but I wasn’t sure of the table.

    It took me a little longer to determine which table held the link as I was expecting a company table, but instead found that the link was stored in the SOP_POPLink table (SOP60100). One fairly quick view later and I have the basis for the required report, and probably for some others in future too.

    Continue reading “SQL Script Linking Purchase Orders to Purchase Requisitions”

    SQL Function To Return Workflow Approval Status

    Microsoft Dynamics GPIf you have been paying any sort of attention, you will know that I do quite a lot of work with the Workflow 2.0 module of Microsoft Dynamics GP.

    We have created a number of reports in the past which allows the status of transactions or card going through a Workflow Approval process; to simplify matters somewhat, I created a SQL function which can be called with the Workflow Type Name and the Workflow Business Object Key and have the approval status returned.

    CREATE FUNCTION dbo.uf_AZRCRV_GetWorkflowApprovalStatus(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(20))
    	RETURNS VARCHAR(14)
    AS
    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    
    Returns Workflow Approval status of a specified workflow item.
    
    Requires input parameters of WorkflowTypeName and WfBusObjKey
    
    Valid Workflow Type Names are (as of Microsoft Dynamics GP 2016 R2):
    	General Ledger Batch Approval
    	Receivables Batch Approval
    	Payables Batch Approval
    	Payables Transaction Approval
    	Purchase Order Approval
    	Purchase Requisition Approval
    	Vendor Approval
    	Employee Profile Approval
    	Employee Skills Approval
    	Payroll Direct Deposit Approval
    	Payroll Timecard Approval
    	Payroll W4 Approval
    	Expense Report Approval
    	Timesheet Approval
    	Smartlist Designer View Approval
    */
    BEGIN
    	RETURN ISNULL((
    		SELECT TOP 1 
    			CASE WHEN ['Workflow History'].Workflow_Action = 1 THEN
    				'Submitted'
    			WHEN ['Workflow History'].Workflow_Action = 2 THEN
    				'Resubmitted'
    			WHEN ['Workflow History'].Workflow_Action = 3  THEN
    				'Approved'
    			WHEN ['Workflow History'].Workflow_Action = 4 THEN
    				'Task Complete'
    			WHEN ['Workflow History'].Workflow_Action = 5 THEN
    				'Rejected'
    			WHEN ['Workflow History'].Workflow_Action = 6 THEN
    				'Delegated'
    			WHEN ['Workflow History'].Workflow_Action = 7 THEN
    				'Recalled'
    			WHEN ['Workflow History'].Workflow_Action = 8 THEN
    				'Escalated'
    			WHEN ['Workflow History'].Workflow_Action = 9 THEN
    				'Edit'
    			WHEN ['Workflow History'].Workflow_Action = 10 THEN
    				'Final Approved'
    			END
    		FROM 
    			WF30100 AS ['Workflow History']
    		INNER JOIN
    			WFI10002 AS ['Workflow Master']
    				ON
    					['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
    		WHERE
    			['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName
    		AND
    			['Workflow Master'].WfBusObjKey = @WfBusObjKey
    		ORDER BY
    			['Workflow History'].DEX_ROW_ID DESC)
    	,'Not Submitted')
    END
    GO
    
    GRANT EXECUTE ON uf_AZRCRV_GetWorkflowApprovalStatus TO DYNGRP
    GO

    This view can be called from a SmartList created using either SmartList Designer, SmartList Builder, via a Reporting Services or Excel Report or any other type of report.