AX 2012 Financial Reporting, simplified

The changes from 2009 to 2012 as it related to financials are fairly comprehensive and those of us who have made that transition have at least one point in time said "I miss LedgerTrans". It was simple. All the stuff I could want was right there and easy to consume. I have found a way to get back to a (slightly) more simple financial reporting time with some views. Since these are views, we are sacrificing performance for ease of use but in SQL 2012 or 2014, you can make them indexed views so its not terrible; not great but still not terrible. First, Lets make a view to simplify working with financial dimensions.

 

CREATE VIEW [dbo].[vw_DimensionValues] AS 

SELECT     dalv.dimensionattributevaluegroup , 
                                    dalv.displayvalue, 
                                    dalv.ordinal, 
                                    'sort' = Row_number() OVER (partition BY dalv.dimensionattributevaluegroup ORDER BY dalv.ordinal ASC),
dalv.partition
                         FROM       dimensionattributevaluecombination davc 
                         INNER JOIN dimensionattributevaluegroupcombination davgc 
                         ON         davc.recid = davgc.dimensionattributevaluecombination 
and davc.PARTITION = davgc.PARTITION
                         INNER JOIN dimensionattributevaluegroup davg 
                         ON         davg.recid = davgc.dimensionattributevaluecombination 
and davg.PARTITION = davc.PARTITION
                         INNER JOIN dimensionattributelevelvalue dalv  
                         ON         davg.recid = dalv.dimensionattributevaluegroup 
and davg.PARTITION = davc.PARTITION
                         GROUP BY   dalv.dimensionattributevaluegroup, 
                                    dalv.displayvalue, 
                                    dalv.ordinal,
dalv.partition



GO

Next, lets make a view that build on that view to give use something a little more meaningful. I set this up with generic financial dimensions just calling them finDim1, finDim2 and so on up to 7. you can change this to suit your needs. Keeping them generic will allow for the same style of data consumption regardless of account structure though so do what works best for you. You could split the views out by account structure if you want.

CREATE VIEW [dbo].[vw_DimensionAttributes] AS 

SELECT   dimensionattributevaluegroup, 
            Max( 
            CASE ordinal 
                     WHEN 1 THEN displayvalue 
                     ELSE '' 
            END )AS 'MainAccount', 
            Max( 
            CASE ordinal 
                     WHEN 2 THEN displayvalue 
                     ELSE '' 
            END) AS 'finDim1', --rename later
            Max( 
            CASE ordinal 
                     WHEN 3 THEN displayvalue 
                     ELSE '' 
            END) AS 'finDim2', --rename later
            Max( 
            CASE ordinal 
                     WHEN 4 THEN displayvalue 
                     ELSE '' 
            END) AS 'finDim3',   --rename later
            Max( 
            CASE ordinal 
                     WHEN 5 THEN displayvalue 
                     ELSE '' 
            END) AS 'finDim4', --rename later
Max( 
            CASE ordinal 
                     WHEN 6 THEN displayvalue 
                     ELSE '' 
            END) AS 'finDim5', --rename later
Max( 
            CASE ordinal 
                     WHEN 7 THEN displayvalue 
                     ELSE '' 
            END) AS 'finDim6', --rename later
Max( 
            CASE ordinal 
                     WHEN 8 THEN displayvalue 
                     ELSE '' 
            END) AS 'finDim7', --rename later
PARTITION
   FROM     vw_DimensionValues 
   GROUP BY dimensionattributevaluegroup, PARTITION


GO

Now that we have our prereqs created, we can proceed to the final part that brings it all together.

CREATE VIEW [dbo].[vw_ledgerTrans] AS 

   SELECT   'DisplayValue' =  davc.displayvalue, 
              'MainAccount' = ma.mainaccountid,
              'Name' = ma.NAME,
              'Date' = gje.accountingdate,
              'JournalNumber' = gje.journalnumber, 
              'Voucher' = gje.subledgervoucher,
              'Posting' = gjae.postingtype,  -- join enum dictionary later to get label
 'Currency' = gjae.transactioncurrencycode, 
              'Debit' = CASE 
WHEN gjae.iscredit = 0 THEN gjae.accountingcurrencyamount 
ELSE 0 
END, 
              'Credit' = CASE 
WHEN gjae.iscredit = 1 THEN gjae.accountingcurrencyamount 
ELSE 0 
END, 
              'MainAccountId' = vda.MainAccount, 
              vda.finDim1, 
              vda.finDim2, 
              vda.finDim3, 
              vda.finDim4,
 vda.finDim5, 
              vda.finDim6, 
              vda.finDim7    
   FROM       generaljournalentry gje 
   INNER JOIN generaljournalaccountentry gjae 
   ON         gje.recid = gjae.generaljournalentry 
   and   gje.PARTITION = gjae.PARTITION
   INNER JOIN dimensionattributevaluegroupcombination davgc 
   ON         gjae.ledgerdimension = davgc.dimensionattributevaluecombination 
   and   davgc.PARTITION = gjae.PARTITION
   INNER JOIN dimensionattributevaluecombination davc 
   ON         davc.recid = davgc.dimensionattributevaluecombination 
   and   davc.PARTITION = davgc.PARTITION
   INNER JOIN mainaccount ma 
   ON         ma.recid = davc.mainaccount 
   and   ma.PARTITION = gje.PARTITION
   INNER JOIN vw_DimensionAttributes vda 
   ON         vda.dimensionattributevaluegroup = davgc.dimensionattributevaluegroup 
   and   vda.PARTITION = gje.PARTITION


GOa

This view, vw_ledgerTrans is very similar to the AX 2009 table ledgerTrans. This will allow you join it to various transactions and get the financials behind those transactions without having to quite a few joins and functions just to get nicely formed data. The result is this (using Contoso Data):