Ledger Dimensions Flattened

Submitted by Nathan Clouse on 09/30/15.

This provides a flattened view of all LedgerDimensions so you can join a ledgerDimension and get a broken out ledger account by the elements that compose that ledger account by account structure. There is a lot there and rather than explain it further, a demonstration would better illustrate the point. This specific implementation is based on Contoso data so your implementation will most likely differ. To customize this view to your specific needs, please see this post.

AX Version:

AX 2012

Module:

General Ledger

CREATE VIEW [dbo].[vw_ledgerLedgerDimensionsFlat] 
AS 
  SELECT dv.partition, 
         dv.dimensionhierarchy, 
         'AccountStructure' = dv.dimensionhierarchy, 
         dv.dimensionattributevaluecombination, 
         'LedgerDimension' = 
            dv.dimensionattributevaluecombination, 
         'MainAccount' = Max(CASE dv.dimensionattributename 
                               WHEN 'MainAccount' THEN 
                               dv.displayvalue 
                               ELSE '' 
                             END), 
         'Agreement' = Max(CASE dv.dimensionattributename 
                             WHEN 'Agreement' THEN 
                             dv.displayvalue 
                             ELSE '' 
                           END), 
         'BusinessUnit' = Max(CASE dv.dimensionattributename 
                                WHEN 'BusinessUnit' THEN 
                                dv.displayvalue 
                                ELSE '' 
                              END), 
         'Cashflow_CN' = Max(CASE dv.dimensionattributename 
                               WHEN 'Cashflow_CN' THEN 
                               dv.displayvalue 
                               ELSE '' 
                             END), 
         'CostCenter' = Max(CASE dv.dimensionattributename 
                              WHEN 'CostCenter' THEN 
                              dv.displayvalue 
                              ELSE '' 
                            END), 
         'CostCenter_CN' = 
         Max(CASE dv.dimensionattributename 
               WHEN 'CostCenter_CN' THEN 
               dv.displayvalue 
               ELSE '' 
             END), 
         'Department' = Max(CASE dv.dimensionattributename 
                              WHEN 'Department' THEN 
                              dv.displayvalue 
                              ELSE '' 
                            END), 
         'Department_CN' = 
         Max(CASE dv.dimensionattributename 
               WHEN 'Department_CN' THEN 
               dv.displayvalue 
               ELSE '' 
             END), 
         'ExpenseAndIncomeCode' = 
         Max(CASE dv.dimensionattributename 
               WHEN 'ExpenseAndIncomeCode' 
             THEN dv.displayvalue 
               ELSE '' 
             END), 
         'Filial' = Max(CASE dv.dimensionattributename 
                          WHEN 'Filial' THEN dv.displayvalue 
                          ELSE '' 
                        END), 
         'FiscalEstablishment' = 
         Max(CASE dv.dimensionattributename 
               WHEN 'FiscalEstablishment' 
             THEN dv.displayvalue 
               ELSE '' 
             END), 
         'ItemGroup' = Max(CASE dv.dimensionattributename 
                             WHEN 'ItemGroup' THEN 
                             dv.displayvalue 
                             ELSE '' 
                           END), 
         'LegalEntity' = Max(CASE dv.dimensionattributename 
                               WHEN 'LegalEntity' THEN 
                               dv.displayvalue 
                               ELSE '' 
                             END), 
         'Ownership_CN' = Max(CASE dv.dimensionattributename 
                                WHEN 'Ownership_CN' THEN 
                                dv.displayvalue 
                                ELSE '' 
                              END), 
         'Project' = Max(CASE dv.dimensionattributename 
                           WHEN 'Project' THEN 
                           dv.displayvalue 
                           ELSE '' 
                         END), 
         'Purpose_CN' = Max(CASE dv.dimensionattributename 
                              WHEN 'Purpose_CN' THEN 
                              dv.displayvalue 
                              ELSE '' 
                            END), 
         'Retailchannel' = 
         Max(CASE dv.dimensionattributename 
               WHEN 'Retailchannel' THEN 
               dv.displayvalue 
               ELSE '' 
             END), 
         'ServiceLine' = Max(CASE dv.dimensionattributename 
                               WHEN 'ServiceLine' THEN 
                               dv.displayvalue 
                               ELSE '' 
                             END), 
         'Store' = Max(CASE dv.dimensionattributename 
                         WHEN 'Store' THEN dv.displayvalue 
                         ELSE '' 
                       END), 
         'Terminal' = Max(CASE dv.dimensionattributename 
                            WHEN 'Terminal' THEN 
                            dv.displayvalue 
                            ELSE '' 
                          END), 
         'Worker' = Max(CASE dv.dimensionattributename 
                          WHEN 'Worker' THEN dv.displayvalue 
                          ELSE '' 
                        END) 
  FROM   (SELECT davc.partition, 
                 'DimensionHierarchy' = 
                 davc.accountstructure, 
                 DAVGC.dimensionattributevaluecombination, 
                 dalv.displayvalue, 
                 DALV.ordinal, 
                 'DimensionAttributeName' = da.NAME 
          FROM   dimensionattributevaluecombination davc 
                 JOIN 
         dimensionattributevaluegroupcombination 
         davgc 
                   ON 
         davc.partition = davgc.partition 
         AND davc.recid = 
             davgc.dimensionattributevaluecombination 
                 JOIN dimensionattributevaluegroup davg 
                   ON davg.partition = davgc.partition 
                      AND davg.recid = 
                          davgc.dimensionattributevaluegroup 
                      AND davg.dimensionhierarchy = 
                          davc.accountstructure 
                 JOIN dimensionattributelevelvalue dALV 
                   ON davg.partition = dALV.partition 
                      AND davg.recid = 
                          dALV.dimensionattributevaluegroup 
                 JOIN dimensionattributevalue dav 
                   ON 
         dav.recid = dalv.dimensionattributevalue 
                 JOIN dimensionattribute da 
                   ON da.partition = dav.partition 
                      AND da.recid = dav.dimensionattribute 
          GROUP  BY davc.partition, 
                    davc.accountstructure, 
davgc.dimensionattributevaluecombination, 
DALV.displayvalue, 
dalv.ordinal, 
da.NAME) dv 
GROUP  BY dv.partition, 
dv.dimensionhierarchy, 
dv.dimensionattributevaluecombination 

go