Implementing Your Ledger Transactions with split out Financial Dimensions view

This post goes over how to create a view for AX 2012 with your various financial dimension split out into columns so you can easily report on those various values. Now if someone asks for all ledger transactions against financial dimension Site, you can do that with one simple select statement in SQL. This post builds upon this post and this post and this view won't work without those other two views.

First, this query is fairly simple but has lots of busywork to get to where you want it to be. Lets break the query out into its 3 major peices. this is basic query with no financial dimensions broken out. [TEMPLATE #1] and [TEMPLATE #2] is where we will add the required info for the financial dimensions.

CREATE VIEW
[dbo].[vw_ledgerGeneralJournalSubledgerVoucherSliptDim] as
select     'LedgerAccount' = dimattrvalcomb.displayvalue, 
           mainacc.mainaccountid, 
           'MainAccountName' = mainAcc.NAME, 
	   [TEMPLATE #1]
           genjourentry.accountingdate, 
           genjourentry.journalcategory, 
           genjouraccentry.postingtype, 
           'CurrencyCode' = genjouraccentry.transactioncurrencycode, 
           genjouraccentry.transactioncurrencyamount, 
           genjouraccentry.accountingcurrencyamount, 
           genjouraccentry.reportingcurrencyamount, 
           'MainAccountRecId' =    genjourentry.ledger, 
           'GeneralJournalEntry' = genjourentry.recid, 
           genjourentry.fiscalcalendarperiod, 
           'LedgerVoucher' = subledvougenjourentry.voucher, 
           'DataAreaId' =    subledvougenjourentry.voucherdataareaid, 
           genjourentry.ledgerentryjournal, 
           genjourentry.documentnumber, 
           'CategoryRef' = mainacc.accountcategoryref, 
           genjouraccentry.partition, 
           ledentryjour.journalnumber, 
           'ValueCombinationRecId' = dimattrvalcomb.recid, 
           genjouraccentry.text, 
           genjourentry.createdby, 
           genjourentry.createddatetime 
FROM       generaljournalaccountentry AS genjouraccentry 
JOIN       generaljournalentry        AS genjourentry 
ON         genjourentry.recid = genjouraccentry.generaljournalentry 
AND        genjourentry.partition = genjouraccentry.partition 
JOIN       subledgervouchergeneraljournalentry AS subledvougenjourentry 
ON         subledvougenjourentry.generaljournalentry = genjourentry.recid 
AND        subledvougenjourentry.partition = genjourentry.partition 
INNER JOIN dimensionattributevaluecombination AS dimattrvalcomb 
ON         dimattrvalcomb.recid = genjouraccentry.ledgerdimension 
AND        dimattrvalcomb.partition = genjouraccentry.partition 
LEFT JOIN  ledgerentryjournal AS ledentryjour 
ON         ledentryjour.recid = genjourentry.ledgerentryjournal 
AND        ledentryjour.partition = genjourentry.partition 
INNER JOIN mainaccount AS mainacc 
ON         mainacc.recid = dimattrvalcomb.mainaccount 
AND        mainacc.partition = dimattrvalcomb.partition 
LEFT JOIN  vw_ledgersplitdimensionentity AS dimension 
ON         dimension.valuecombinationrecid = genjouraccentry.ledgerdimension 
AND        dimension.partition = genjouraccentry.partition
[TEMPLATE #2]

 

 

[Template #1]

Template #1 will cover the financial dimension value and, optionally, the description for that value. Since this is a view and going to be slow no matter what we do, might as well bring back extra info just incase we need it.

'dimension' = dimensionTags.value, 
         'dimensionDescription' = dimensionTags.NAME, 

Here we will want to include this template for EACH finacial dimension name. The easiest way to get this info is to just copy the names from General Ledger > Setup > Financial Dimensions > Financial Dimension. If you have 9 financial dimensions, you will have to copy/repalce/paste template #1 9 times in the [TEMPLATE #1] section. You will notice we will be referencing some tables that aren't in the query. Template #2 will take care of that.

[Template #2]

Template #2 will cover our joins for the tables referenced in template #1. We will want to LEFT JOIN in all of our financial dimension values and descriptions for each of the financial dimensions named above. Here is the basic template:

LEFT JOIN vw_ledgerallfinancialtags AS DimensionTags 
       ON DimensionTags.recid = dimension.Dimension 
          AND DimensionTags.partition = 
              dimension.partition 
          AND DimensionTags.dimensionname = 
              'Dimension' 

Very similar process to what we did for Template #1.

 

The Easy Way Out!

Here is some awful SQL that will fill in the templates for you then you can use something like SQL Formatter to pretty it up. Just update the first part to INSERT your financial dimension names into #tmp and run it.

select cast('Site' as varchar(30)) as dim
into #tmp

insert into #tmp values('department')
--and so on

select template1 = '         '''+ #tmp.dim + ''' = ' + #tmp.dim + 'Tags.value, 
         '''+ #tmp.dim + 'Description'' = dimensionTags.NAME, ',
	   template2 = 'LEFT JOIN vw_ledgerallfinancialtags AS '+ #tmp.dim + 'Tags 
       ON '+ #tmp.dim + 'Tags.recid = dimension.' + #tmp.dim + ' 
          AND '+ #tmp.dim + 'Tags.partition = 
              dimension.partition 
          AND '+ #tmp.dim +'Tags.dimensionname = 
              '''+ #tmp.dim +''' '
from #tmp

drop table #tmp