在工作中，我需要提供包括财务维度的 PurchLine 记录的SQL视图。 但是，由于Dynamics AX的财务维度数据模型为了更加灵活已得到增强。 与默认情况下Dynamics AX 2009只支持3个维度相比，现在可以自由配置任意数量的财务维度。
At work we had the requirement to provide a SQL view of PurchLine Records including their financial dimensions. However, since Dynamics AX the financial dimension data model has been enhanced to be more flexible. In contrast to older versions where Dynamics AX 2009 supported by default 3 dimensions, you are now free to configure as much as you want.
Financial Dimensions in Dynamics AX 2012
涉及的表是DimensionAttributeValueSet，DimensionAttributeValueSetItem，DimensionAttributeValue和DimensionAttribute。 以下语句查询上图所示的财务维度值（集合的RecID为52565498264）。
The tables involved are the DimensionAttributeValueSet, DimensionAttributeValueSetItem, DimensionAttributeValue and the DimensionAttribute. The following statement collects the financial dimension values from the image above (RecID for the set is 52565498264).
select
DIMENSIONATTRIBUTEVALUESET.RECID as DIM_ID,
DIMENSIONATTRIBUTE.NAME as DIM_NAME,
DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE as DIM_VALUE
from DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUESETITEM
on DIMENSIONATTRIBUTEVALUESET.RecId = DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUE
on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DIMENSIONATTRIBUTEVALUE.RECID
join DIMENSIONATTRIBUTE
on DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID
where DIMENSIONATTRIBUTEVALUESET.RECID = 52565498264
结果如下：
The result looks like this:
PIVOT用于将行值转换为列。 以下语句创建一个单独的记录，其中包含维度列：
The PIVOT is used to switch row values into columns. The following statement creates a single record with columns for the dimensions:
SELECT * FROM
(
select
DIMENSIONATTRIBUTE.NAME as DIM_NAME,
DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE as DIM_VALUE
from
DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUESETITEM
on DIMENSIONATTRIBUTEVALUESET.RecId = DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUE
on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DIMENSIONATTRIBUTEVALUE.RECID
join DIMENSIONATTRIBUTE
on DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID
where DIMENSIONATTRIBUTEVALUESET.RECID = 52565498264
) AS SourceTable
PIVOT
(
Max(DIM_VALUE)
FOR DIM_NAME IN
([Department],
[ItemGroup],
[CostCenter],
[BusinessUnit],
[Project])
)
AS PivotTable
The result looks like this:
如果某些维度值为空，则在SQL中输出为NULL，语句不会失败
If some dimensions do not hold values it will be NULL in the SQL but the statement will not fail
为了将财务维数据透视记录添加到诸如（例如PurchLine）之类的交易记录，请从FinancialDimensionAttributeValueSet中添加RecId并将其加入到交易记录表的维度参考字段中。 下面是一个例子：
In order to join the financial dimension pivot record with the transaction like (e.g. PurchLine) add the RecId from the FinancialDimensionAttributeValueSet and join it on the Dimension reference field from the transaction table. Here is an example:
select
PurchId, ItemId, PURCHQTY, PURCHPRICE,
FinDim.Department, FinDim.ItemGroup, FinDim.CostCenter, FinDim.BusinessUnit, FinDim.Project
from PURCHLINE
left join
(
SELECT * FROM
(
select
DIMENSIONATTRIBUTEVALUESET.RECID as RECID,
DIMENSIONATTRIBUTE.NAME as DIM_NAME,
DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE as DIM_VALUE
from DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUESETITEM
on DIMENSIONATTRIBUTEVALUESET.RecId = DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUE
on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DIMENSIONATTRIBUTEVALUE.RECID
join DIMENSIONATTRIBUTE
on DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID
) AS SourceTable
PIVOT
(
Max(DIM_VALUE)
FOR DIM_NAME IN ([Department], [ItemGroup], [CostCenter], [BusinessUnit], [Project])
) AS PivotTable
) as FinDim
on PurchLine.DefaultDimension = FinDim.RecId
where PURCHID = '000081' and DATAAREAID = 'USMF'
结果集有两条记录，每条记录对应一条采购订单行以及相应的财务维度值：
The result set are two records, one for each PurchLine, and the corresponding financial dimension values:
