Ledger Default Dimensions

Submitted by Nathan Clouse on 09/30/15.

A view that shows a list of defaultDimensions with each financial dimension broken out into its own column. You can join this to any defaultDimension field to get the financial dimensions in the defaultDimension broken out into an easy to consume format. This is implemented for Contoso data so your specific implementation will most likely be different. Please see this post on how to implement this view for your instance.

AX Version:

AX 2012

Module:

General Ledger

CREATE VIEW [dbo].[vw_ledgerDefaultDimension] 
AS 
  SELECT davsi.dimensionattributevalueset, 
         dav.dimensionattribute, 
         davsi.partition, 
         'Agreement' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE 
         NAME = 'Agreement' 
         AND dimensionattributevalueset 
             = 
             davsi.dimensionattributevalueset), 
         'BusinessUnit' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE 
         NAME = 'BusinessUnit' 
         AND dimensionattributevalueset = 
             davsi.dimensionattributevalueset), 
         'Cashflow_CN' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE  NAME = 'Cashflow_CN' 
                 AND dimensionattributevalueset = 
         davsi.dimensionattributevalueset), 
         'CostCenter' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE 
         NAME = 'CostCenter' 
         AND dimensionattributevalueset 
             = 
             davsi.dimensionattributevalueset), 
         'CostCenter_CN' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE 
         NAME = 'CostCenter_CN' 
         AND dimensionattributevalueset = 
             davsi.dimensionattributevalueset), 
         'Department' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE 
         NAME = 'Department' 
         AND dimensionattributevalueset 
             = 
             davsi.dimensionattributevalueset), 
         'Department_CN' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE 
         NAME = 'Department_CN' 
         AND dimensionattributevalueset = 
             davsi.dimensionattributevalueset), 
         'ExpenseAndIncomeCode' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE  NAME = 'ExpenseAndIncomeCode' 
                 AND dimensionattributevalueset = 
                     davsi.dimensionattributevalueset), 
         'Filial' = (SELECT displayvalue 
                     FROM   dbo.vw_ledgerbasedimensionview 
                     WHERE 
         NAME = 'Filial' 
         AND dimensionattributevalueset = 
             davsi.dimensionattributevalueset), 
         'FiscalEstablishment' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE  NAME = 'FiscalEstablishment' 
                 AND dimensionattributevalueset = 
                     davsi.dimensionattributevalueset), 
         'ItemGroup' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE 
         NAME = 'ItemGroup' 
         AND dimensionattributevalueset 
             = 
             davsi.dimensionattributevalueset), 
         'LegalEntity' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE  NAME = 'LegalEntity' 
                 AND dimensionattributevalueset = 
         davsi.dimensionattributevalueset), 
         'Ownership_CN' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE 
         NAME = 'Ownership_CN' 
         AND dimensionattributevalueset = 
             davsi.dimensionattributevalueset), 
         'Project' = (SELECT displayvalue 
                      FROM   dbo.vw_ledgerbasedimensionview 
                      WHERE 
         NAME = 'Project' 
         AND dimensionattributevalueset = 
             davsi.dimensionattributevalueset), 
         'Purpose_CN' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE 
         NAME = 'Purpose_CN' 
         AND dimensionattributevalueset 
             = 
             davsi.dimensionattributevalueset), 
         'Retailchannel' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE 
         NAME = 'Retailchannel' 
         AND dimensionattributevalueset = 
             davsi.dimensionattributevalueset), 
         'ServiceLine' = 
         (SELECT displayvalue 
          FROM   dbo.vw_ledgerbasedimensionview 
          WHERE  NAME = 'ServiceLine' 
                 AND dimensionattributevalueset = 
         davsi.dimensionattributevalueset), 
         'Store' = (SELECT displayvalue 
                    FROM   dbo.vw_ledgerbasedimensionview 
                    WHERE 
         NAME = 'Store' 
         AND dimensionattributevalueset = 
             davsi.dimensionattributevalueset), 
         'Terminal' = (SELECT displayvalue 
                       FROM   dbo.vw_ledgerbasedimensionview 
                       WHERE 
         NAME = 'Terminal' 
         AND dimensionattributevalueset = 
             davsi.dimensionattributevalueset), 
         'Worker' = (SELECT displayvalue 
                     FROM   dbo.vw_ledgerbasedimensionview 
                     WHERE 
         NAME = 'Worker' 
         AND dimensionattributevalueset = 
             davsi.dimensionattributevalueset) 
  FROM   dbo.dimensionattributevaluesetitem davsi 
         INNER JOIN dbo.dimensionattributevalue dav 
                 ON dav.recid = 
                    davsi.dimensionattributevalue 
         INNER JOIN dbo.dimensionattribute da 
                 ON da.recid = dav.dimensionattributego