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://GPLifehacks.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:
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!
Microsoft MVP, Business Solutions