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

“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