MDGP 2018 RTM Feature of the Day: Copy User Access Across AA Dimensions

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 RTM; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily as well as adding my own commentary.

The series index for this series of posts is here.

The fifteenth Feature of the Day is Copy User Access Across AA Dimensions. A new button has been introduced to the User Access to Trx Dimensions window which allows setup to be copied between users:

User Access to Trx Dimensions

All alphanumeric dimensions in AA need to have access configured on a per user basis; this can mean a lot of work to both setup and maintain. This new functionality should at least make it a little easier by allowing you to copy settings between users.

Click to show/hide the MDGP 2018 RTM Feature of the Day Series Index

Dynamics GP Tech Conference 2017: Optimised Financials

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

The Financials modules have some enhancements coming which should be of benefit:

  • Copy user access across AA transaction dimension codes – this should make maintaining AA dimension code access easier.
  • Email single statement from customer card and enquiry – handy when you only want to send a single statement to the customer on the phone, rather than to a range of them.
  • PO Generator opens PO List with the new PO’s filter.
  • PO Number display on Requisition List for originator – getting easy access to the PO number for PR orinators will be a good new addition; it will save me writing SmartLists for everyone based off this view.
  • Print Purchase Requisition – I have mixed opinions. Workflow can handle the sending of requisition details to an approver anyway, and I can’t really think of another reason to output one?
  • Display Hold status on Sales Transaction Entry – more information is never a bad thing.
  • One check per invoice settings – allows for more granular control than the setting in the Select Payments window.
  • Save Select Payments settings – are you tired of having to configure your payment run manually every time? Well, Microsoft’s got you covered with this feature.

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

Microsoft’s Migration To A New Knowledge Base System Is Now Complete

Microsoft Dynamics GPMicrosoft have been migrating their Knowledge Base to a new system, making some articles unavailable, but have announced the migration is now complete and the articles should now be available again.

Or at least most of them; in the post, Cheryl does say that”outdated content or rarely used content (i.e. no page views in recent years)”; I have mixed feelings about this as I have occasionally stumbled across articles for GP 8 or 9 which have still fixed an issue I’ve had with the most recent versions of Microsoft Dynamics GP.

The migration means that a link like https://support.microsoft.com/kb/915903 is now https://support.microsoft.com/help/915903.

You may need to update any bookmarks or links you have on the old pattern, but at least for the ones I have checked, the old pattern is being forwarded to the old.

This change is why the above link was unavailable when I wrote the Removing Analytical Accounting from Microsoft Dynamics GP post.

KB articles are available by either searching in PartnerSource or CustomerSource (and may the God of your choice answer your prayers for assistance) or by using the search engine of your choice.

Removing Analytical Accounting from Microsoft Dynamics GP

Microsoft Dynamics GPI recently posted a script to remove the Fixed Asset Management tables from Microsoft Dynamics GP. I wrote that script for a client who wanted to start using Fixed Asset Management, but it turned out the module had been installed at some point in the past and then removed and the client wanted to reintroduce it.

We’ve just had a similar instance come up with a client upgrading from Microsoft Dynamics GP 2013 SP2 to 2016 R2 where Analytical Accountijng has been installed at some stage and removed and was stopping the upgrade from going through.

In this case though, there was (operative word was) a Knowledge Base article from Microsoft on how to remove Analytical Accounting which is no longer available (hopefully it will be back as I understand Microsoft are moving to a new KB system).

The script was available when we had the issue, but a couple of weeks later, the question was asked by one of the other MVPs. By the time I saw the question, they’d been emailed the scripts by one of the others.

However, when my colleague downloaded the scripts they had also saved a copy of the web page, which meant I could grab the download links for the scripts.

  1. System Database Script
  2. Company Database Script

After running the Microsoft scripts, you’ve downloaded using the above links, run the below script on the system database to remove the Analytical Accounting entries from the upgrade tables:

DECLARE @PRODID INT = 3180

DELETE DB_Upgrade WHERE PRODID = @PRODID
DELETE DU000020 WHERE PRODID = @PRODID

As always before running scripts, make sure you have a good backup of your databases.

New Microsoft Dynamics GP Sample Data

Microsoft Dynamics GPMicrosoft have been adding quite a lot of new functionality to Microsoft Dynamics in recent versions, but the sample data has not been getting updated.

To remedy this, Terry Heley on the Dynamics GP Support and Services Blog has shared some macros, for Dynamics GP 2016, which add sample data into the Fabrikam sample data.

The new sample data focuses on a few modules:

  • Advanced Human Resources
  • Analytical Accounting
  • Workflow for HRP
  • Project Accounting Time and Expense
  • PTO Manager
  • Employee Self Service

Terry points out that this is a run at your own risk macro built on GP 2016, although it would probably be usable on Dynamics GP 2015 as well.

There are instruction documents with each macro as macros can crash based on the menu bar selected in user Preferences.

Also note that there is no support for these macros so no cases can be logged if issues are found.

The download link is available at the end of the post.

SQL View Joining GL Transactions To MDA

Microsoft Dynamics GPWe have a couple of clients using MDA (Multi-dimensional Analysis; the precursor to Analytical Accounting), which I don’t know terribly well. So, when one of them asked for a new SmartList Object to be created which extracts information about General Journals and the related MDA information, I needed to do some exploring of the database to work out the links.

Unfortunately, the links between the GL transactions and MDA are not especially obvious. To verify what I had created I did a search and came across a post from 2011 by Mark Polino which was posting code created by a Jeremy Lowell.

I ended up combining some of the code I had with Jeremy’s code (when I tried just his I was getting duplicate lines) to create the below SQL View. Since writing and giving the view to the client, I’ve spotted a few places where the SQL could be tightened up, but this view has been tested in its current state.

CREATE VIEW uv_AZRCRV_LinkGLtoMDA AS
	SELECT DISTINCT
		GLT.JRNENTRY
		,GLT.YEAR
		,GLT.TRXDATE
		,GLT.REFRENCE
		,GLT.SOURCDOC
		,GLT.DEBITAMT
		,GLT.CRDTAMNT
		,GLT.ACTINDX
		,DTA10100.DTASERIES
		,DTA10100.DTAREF
		,DTA10100.GROUPID
		,DTA10100.DTA_GL_Reference
		,DTA10100.GROUPAMT
		,DTA10200.CODEID
		,DTA10200.POSTDESC
		,DTA10200.CODEAMT
	FROM
		(SELECT GLT.JRNENTRY
			,GLT.OPENYEAR AS YEAR
			,GLT.TRXDATE
			,GLT.REFRENCE
			,GLT.SOURCDOC
			,GLT.DEBITAMT
			,GLT.CRDTAMNT
			,GLT.ACTINDX
			,GLT.SEQNUMBR
			,GLT.OrigSeqNum
			,GLT.ORCTRNUM
		FROM
			GL20000 AS GLT WITH (NOLOCK)
		UNION ALL 
			SELECT GLT.JRNENTRY
				,GLT.HSTYEAR AS YEAR
				,GLT.TRXDATE
				,GLT.REFRENCE
				,GLT.SOURCDOC
				,GLT.DEBITAMT
				,GLT.CRDTAMNT
				,GLT.ACTINDX
				,GLT.SEQNUMBR
				,GLT.OrigSeqNum
				,GLT.ORCTRNUM
			FROM
				GL30000 AS GLT WITH (NOLOCK)
		) AS GLT
	LEFT OUTER JOIN
		DTA10100 WITH (NOLOCK)
			ON
				DTA10100.JRNENTRY = GLT.JRNENTRY
			AND
				DTA10100.ACTINDX = GLT.ACTINDX 
			AND
				(DTA10100.SEQNUMBR = GLT.SEQNUMBR OR DTA10100.SEQNUMBR <> GLT.SEQNUMBR)
			AND
				GLT.ORCTRNUM = DTA10100.DOCNUMBR 
	LEFT OUTER JOIN
		DTA10200 WITH (NOLOCK)
			ON
				(DTA10200.DTAREF = DTA10100.DTAREF
					AND
				GLT.SEQNUMBR = GLT.OrigSeqNum)
			OR
				(DTA10200.DTAREF = DTA10100.DTAREF
					AND
				GLT.SEQNUMBR <> GLT.OrigSeqNum)
GO
GRANT SELECT ON uv_AZRCRV_LinkGLtoMDA TO DYNGRP
GO

MDGP 2016 R1 Feature of the Day: Analytical Accounting User Access Settings

In Microsoft Dynamics GP 2016 R1 Analytical Accounting has been enhanced to add an option to allow users to be given access to all transaction dimensions:

Analytical Accounting User Access Settings

This is a nice simple feature but one which will make maintaining Analytical Accounting so much easier. I have more clients using the old Multi-Dimensional Analysis than AA, with maintaining access being one of the sticking points so I may have to have a chat with them and see if this changes the equation.

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

MDGP 2015 R2 Feature of the Day: Analytical Accounting Transaction Lists

Microsoft Dynamics GPMicrosoft 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 fourth Feature of the Day is Analytical Accounting Transaction Lists.

This feature provides improvements to SmartLists and Excel Reports for Analytical Accounting (AA) transaction information. The first improvement is an expansion of the amount of information available to be included as columns in the AA SmartLists and Excel Reports. AA tracks a lot of information for each record, and we’ve included more of this data in the SmartLists and Excel Reports.

Microsoft have also improved the way the AA dimension code information displays in the lists. In prior releases, the lists contained a single column for the AA transaction dimension code. Thus, if the company is tracking more than one dimension on a transaction, multiple lines were required in the list for a distribution entry.

In GP2015 R2, they have updated the SmartLists and Excel Reports to display a column for each transaction dimension, showing the code assigned for that dimension.

For example, the below SmartList shows how the list would display for a company tracking Cost Center and Project as AA transaction dimensions.

Analytical Accounting SmartList

The change in how the data displays improves readability of the report, and also improves the ease with which users can summarize and work with the data in Excel using pivot table functionality.

Analytical Accounting is one of the modules which is not much used, but can add substantial analysis without needing the chart of accounts to be overly complicated.

If you’re thinking of starting to use Analytical Accounting, more information is available in my second book, Microsoft Dynamics GP Financial Management:

Amazon.com Amazon.co.uk

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

MDGP 2015 Feature of the Day: Management Reporter Data Integration

Microsoft Dynamics GPThe 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 thirty third Feature of the Day is Management Reporter Data Integration. In Microsoft Dynamics GP 2015, the user can determine the data that is available to the Microsoft Dynamics Management Reporter DataMart, improving the performance and giving the user control over the data selected.

The user can choose per company (including sample company) if they want the General Ledger data available for reporting.

The user also has the ability to include or exclude Analytical Accounting data for each company:

Company Setup Options

I am slightly puzzled by this feature of the day as you can simply not import a company into Management Reporter if you don;t want to report on it. While I steer clients away from AA where possible, I am not entirely opposed to it, but I do wonder who would want to report on AA without also wanting to be able to report on the GL.

The roles targeted by this feature are:

  • All

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

“The Rebel Alliance Needs You!” or “Vote To Resurrect Multi-Dimensional Analysis”

Microsoft Dynamics GPThere was a recent discussion about Multi-Dimensional Analysis (MDA) and Analytical Accounting (AA) between a group of Microsoft MVPs for Microsoft Dynamics GP; this group involved people from at least three continents and resulted in a fairly unanimous opinion that Multi-Dimensional Analysis was a useful module.

More useful in many ways than Analytical Accounting which was intended to replace it. The problem being that while MDA was fairly simple and easy to use, AA was fairly complex and clunky. While the reporting options for AA are more comprehensive than MDA, they are still not up to the standard of much of Dynamics GP itself.

To this end a suggestion has been logged on Microsoft Connect which we would appreciate you voting on. Suggestions on MS Connect with a lot of votes stand a good chance of being included in a later version of Microsoft Dynamics GP.

Please visit and vote on this suggestion.

Rebel AllianceSource Unknown