Earlier I wrote about how to use MDA for dimension analysis rather than Analytical Accounting.  You can find that blog here.
To continue that thought, I have created a SQL view to help users get that dimensional analysis data easily!  The script captures commonly used data – if you feel I am missing something, please let me know and I will try to add it to the view for others.  I also like to push this data to Excel and Power BI – take a look at the Excel Report and Power BI reports I created at the bottom of this post!
The script below should be run against your company database – you can then use it to build a smartlist.  Instructions for adding the view to a smartlist using Smartlist Designer are included with GP LifeHacks #137.
Here is the view:

CREATE view [AA_GPLifeHacks_MDA_Transactions]
as
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Dimension Analysis SQL View
-- Run Against Company Database
-- Provides Detailed list and GL information for MDA Transactions
-- Visit http://lifehacks365.com for more cool GP Stuff!
-- Updated by: Shawn Dorward - As of 04/30/2018
-- Modified to include TRXDATE match so that recurring transactions do not duplicate
-- Updated by: Shawn Dorward - As of 09/06/2018
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
SELECT
DTA10100.GROUPID MDA_GROUP,
DTA10100.GROUPAMT MDA_GROUP_AMOUNT,
DTA10200.CODEID MDA_CODE,
DTA10200.CODEAMT MDA_CODE_AMOUNT,
DTA10200.DTAQNTY MDA_CODE_QUANTITY,
DTA10100.DTAREF MDA_TRX_REFERENCE,
DTA10100.DOCNUMBR ORIG_DOC_NUMBER,
DTA10100.DTA_GL_Reference GL_REFERENCE,
DTA10100.JRNENTRY JE_ENTRY_NUM,
DTA10100.TRXDATE POSTING_DATE,
GL00105.ACTNUMST ACCOUNT_NUMBER,
GL00100.ACTDESCR ACCOUNT_DESCRIPTION,
JE_INFO2.YEAR1 FISCAL_YEAR,
JE_INFO2.ORMSTRID ORIGINAL_MASTERID,
JE_INFO2.ORMSTRNM ORIGINAL_MASTER_NAME,
JE_INFO2.DEBITAMT DEBIT_AMOUNT,
JE_INFO2.CRDTAMNT CREDIT_AMOUNT,
case DTA10100.PSTGSTUS
 when 1 then 'Work'
 when 2 then 'Posted'
 when 3 then 'History'
 when 4 then 'Originating'
 end POSTING_STATUS
FROM
GL00100 RIGHT OUTER JOIN
DTA10100 LEFT OUTER JOIN
(SELECT JRNENTRY, OPENYEAR AS YEAR1, DEBITAMT,
CRDTAMNT, ORDOCNUM, ORMSTRNM, ORMSTRID, ORCTRNUM,
ACTINDX, SOURCDOC, TRXDATE
FROM GL20000 AS JE_INFO1
UNION ALL
SELECT JRNENTRY, HSTYEAR AS YEAR1, DEBITAMT,
CRDTAMNT, ORDOCNUM, ORMSTRNM, ORMSTRID, ORCTRNUM,
ACTINDX, SOURCDOC, TRXDATE
FROM GL30000 AS JE_INFO1
) AS JE_INFO2
ON DTA10100.ACTINDX = JE_INFO2.ACTINDX
AND DTA10100.TRXDATE = JE_INFO2.TRXDATE
AND DTA10100.JRNENTRY = JE_INFO2.JRNENTRY
ON GL00100.ACTINDX = DTA10100.ACTINDX LEFT OUTER JOIN
GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
DTA10200 ON DTA10100.DTASERIES = DTA10200.DTASERIES
AND DTA10100.DTAREF = DTA10200.DTAREF
GO
GRANT SELECT ON [AA_GPLifeHacks_MDA_Transactions] to DYNGRP

Excel Refreshable Report

Step 1: Create a SmartList using the View above
The easiest way to get this data in an Excel Refreshable Report is to first create a Smartlist using the view.  You can do that by using GP LifeHacks #137 – simply replace the view name in the instructions with the view name above.
Step 2: Publish the SmartList to your Excel Refreshable Reports catalog
Now that the SmartList is created, highlight the SmartList and click the ‘publish’ button in the SmartList window.

This will create the report and save it in the same location that your other Excel Refreshable Reports are stored.
Now simply open the report and create a new tab.  Sheet1 will update with a data refresh (F5) in Excel.  After you build your report/charts/etc by adding content to the new tabs (PIVOT TABLES BABY!!!), simply save the excel file to use this over and over again!
Here is my sample Excel file:

Power BI

For Power BI – publish the view using OData and connect it to your dashboard.  This can be done using the instructions provided here.
Take a look at my visual for Power BI!  I don’t know, I just love the fish 🙂

Thanks for reading!
Shawn

Shawn Dorward
Microsoft MVP, Business Solutions
LifeHacks365.com | LinkedIn | Twitter | GPUG | MSDYNGP | Facbook| Instagram
Original Post:
https://lifehacks365.com/2018/04/30/dimension-analysis-for-mda-sql-view-for-dynamics-gp/