SQL Script Linking Purchase Orders to Purchase Requisitions

● Ian Grieve ●  ● 1 Comment   ● 

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

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

SQL Function To Return Workflow Approval Status

● Ian Grieve ●  ● 5 Comments   ● 

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.

● Categories: Dynamics, GP, Microsoft, Workflow ● Tags: , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

Fixing Reports Not Implemented Error After Upgrade

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPWe’ve been doing a number of upgrades recently and I encountered an old error when trying to deploy the SQL Server Reporting Services (SSRS) and Excel Reports through the Reporting Tools Setup window (Administration >> Setup >> System >> Reporting Tools).

This particular client is being upgraded from Microsoft Dynamics GP 2013 SP2 to 2016 R2.

The problem is that some of the companies are showing as Not implemented. The companies showing this way are the oldest; TEST2016… is actually the main company renamed for testing.

Reporting Tools Setup

Continue reading → Fixing Reports Not Implemented Error After Upgrade

● Categories: Dynamics, GP, Microsoft, Uncategorized ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Historical Excel Reporting Price Increase Coming

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPMark Polino has a set of Historical Excel Reports available for sale. The reports available are as follows:

  • Receivables Management Historical Aged Trial Balance
  • Payables Management Historical Aged Trial Balance
  • Historical Inventory Trial Balance
  • Historic Stock Status Report

Now is the ideal time to buy them, as the price is increasing on 1st April 2017.

● Categories: Dynamics, GP, Microsoft, Third Party Add-on ● Tags: , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2016 R1: Deploy System Database

● Ian Grieve ●  ● 11 Comments   ● 

Microsoft Dynamics GPNow that we have the first client installed, we can deploy the system database. By default the system database is called DYNAMCIS, but i have chosen to use one called D16R1. To deploy the system database, hit the Windows start button, search for and launch GP Utilities.

While I do try to minimise the use of the sa account as it is the SQL Server system administrator account, the initial implementation of Dynamics GP does require its use. So, on the Welcome to Microsoft Dynamics GP Utilities window enter sa in the User ID field and enter the relevant password.

Once done, click OK:

Welcome to Microsoft Dynamics GP Utilities

Continue reading → Hands On With Microsoft Dynamics GP 2016 R1: Deploy System Database

● Categories: Dynamics, GP, GP Utilities, Microsoft ● Tags: , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R1 Feature of the Day: OData Service

● Ian Grieve ●  ● 0 Comments   ● 

The previous feature of the day introduced the OData Service installation. This one gives a brief overview of how the service works:

OData Service

The main features of the OData service are:

  • Ability to define end points for the service
  • View GP data with any tool that supports OData feeds

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

● Categories: Dynamics, GP, GP OData Service, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R1 Feature of the Day: OData Service Deployment

● Ian Grieve ●  ● 2 Comments   ● 

Microsoft Dynamics GP 2016 R1 is seeing the introduction of a new complimentary product: an OData service which will allow reports on Dynamics GP data using any reporting tool (such as PoweBI) to be created:

OData Service Deployment

The main features of are:

  • Separate install under Additional Products
  • Use GP Security to control access to data
  • Supports tables, views and stored procs as data sources

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

● Categories: Dynamics, GP, GP OData Service, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R1 Feature of the Day: PowerBI Reports on the Home Page

● Ian Grieve ●  ● 1 Comment   ● 

In February 2014 Microsoft launched PowerBI for Office 265 and are now bringing it to the Microsoft Dynamics GP 2016 R1 home page (but only the home page of the desktop client):

PowerBI on the Microsoft Dynamics GP 2016 R1 home page

The main features of are:

  • New part on Home Page to display Power BI Reports.
  • Click on the report to take you out to your Power BI site.

I’m not entirely sure how to feel about the addition of PowerBI to Dynamics GP; it almost feels like a scatter gun approach is being used for reporting with Dynamics GP. There are standard reports, Metrics, Excel Reports, SSRS Reports (which for most clients replace the Metrics to the extent I had to lookup the name of Metrics), Management Reporter (with an unknown roadmap/future) reports and now PowerBI reports.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft, PowerBI ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R1 Feature of the Day: SmartLists from Favorites

● Ian Grieve ●  ● 0 Comments   ● 

As mentioned in the previous post, Microsoft Dynamics GP 2013 SP2 saw the introduction of SmartList Designer and have been gradually improving it since. This new feature allows a user to create a new SmartList from a favourite, instead of just from a SmartList Object:

SmartList

SmartList Designer

The main features of are:

  • A user can create a new SmartList from a favourite using Designer
  • The user doesn’t have to remove all extra columns from the default SmartList

This new feature will ease the creation of a SmartList Object simialr to an existing favourite instead of a copy of the default SmartList Object to which the favourite belongs.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Designer ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R1 Feature of the Day: Excel Export as Numbers

● Ian Grieve ●  ● 0 Comments   ● 

This feature of the day puzzles me somewhat as I would swear this was a feature introduced within the last couple of versions. It is the export of numbers as numbers from a SmartList:

Excel Export as Numbers

As I mentioned in the introductory paragraph, this is a feature I believe was already introduced, but upon further reflection this may be a slightly different new feature.

The previous feature I am thinking of (but can’t find a specific announcement) exported the numbers to five decimal places and without a currency symbol. The example given in the screenshot, above, has a currency symbol and is to two decimal places.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft, SmartList ● Tags: , , , , , ,  ● Permalink ● Shortlink ●