Ledger Split Dimension Entity

Submitted by Nathan Clouse on 09/29/15.

This view, plus several others, can be used to create a view that gives you financial postings with their ledger account plus all the elements of the ledger account broken out into their own column so you can filter on the easily. This is very similar to the LedgerTrans table in 2009. This view is based on Contoso data and will most likely be more simple for your implementation. Please refer to this post to see how this can be constructed for you.

AX Version:

AX 2012

Module:

General Ledger

CREATE VIEW [dbo].[vw_ledgerSplitDimensionEntity] 
AS 
  SELECT * 
  FROM   (SELECT da.NAME, 
                 entityinstance, 
                 dalvv.valuecombinationrecid, 
                 dalvv.[partition] 
          FROM   dimensionattributelevelvalueview dalvv 
                 JOIN dimensionattribute da 
                   ON da.recid = dalvv.dimensionattribute 
                      AND da.[partition] = 
         dalvv.[partition]) 
         dimension 
         PIVOT (Max(entityinstance) 
               FOR NAME IN ( agreement, 
                             businessunit, 
                             cashflow_cn, 
                             costcenter, 
                             costcenter_cn, 
                             department, 
                             department_cn, 
                             expenseandincomecode, 
                             filial, 
                             fiscalestablishment, 
                             itemgroup, 
                             legalentity, 
                             ownership_cn, 
                             project, 
                             purpose_cn, 
                             retailchannel, 
                             serviceline, 
                             store, 
                             terminal, 
                             worker )) piv; 

go

quicktab