If 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.
We’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 ( ).
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.
Mark 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.
Enter the user and password and click OK to log in:
With the system database deployed it is now time to deploy the sample company. This is done by launching GP Utilities.
Enter the user and password and click OK to log in:
Now 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:
Microsoft Dynamics GP 2015 R2 has been released and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.
The twenty second Feature of the Day is . A top suggestion on the MS Connect site, this feature moves the Debit column to display before the Credit column by default in account transaction reporting options in which the Credit column previously displayed before the Debit column.
This applies to SmartLists and Excel Reports, as well as the database tables and views on which the reports are based.
Microsoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.
The thirteenth Feature of the Day is SmartList Designer Create View .
Within SmartList Designer a user can send a SmartList through workflow to create a SQL view based on the SmartList query.
This SQL view can then be used outside of GP for such purposes as creating reports using SQL reporting services or Power BI reports in Excel.
The view will also allow the user to publish the SmartList as a refreshable excel report from inside of Dynamics GP.
Now that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts o how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here. I am assuming you already have your server built with Windows and SQL Server for this series.
In the last post I deployed the system database; in this one I’m going to take a look at deploying the sample company. While this isn’t something generally done with a new system, I am in the process of building a new demo/test environment.
We’ll pick up where the previous post left off on the Additional Tasks step of GP Utilities. If you had closed GP Utilities log back in and progress through to this step and in the combo box, in the middle of the screen, make sure it is set to Add sample company data and click Process:
The Inside Microsoft Dynamics GP blog has started a series of posts on the new features of, the soon to be released, Microsoft Dynamics GP 2015. As I have done for the last two releases, I intend to shadow these posts and add my own opinions on these new feature; the series index is here.
The twentieth Feature of the Day is Create Refreshable Excel Reports in SmartList Designer. This feature will enable users to create refreshable Excel reports using SmartList Designer. Reports created from SQL views may be published to the Excel report library.
By publishing a refreshable report, the user can open the report in Excel and the data will always be up to date:
I like the look of this feature. The out-of-the-box Excel Reports are of somewhat limited utility as they return all data for the report which means that for a transaction SmartList there can be vast sums of data returned. But with these SmartList Designers you should be able to define a filter in the report so it only includes, for example, the current or previous months transactions.
The roles targeted by this feature are: