![]() Where PURCHID = '000081' and DATAAREAID = 'USMF' On PurchLine.DefaultDimension = FinDim.RecId Here is an example: selectįinDim.Department, FinDim.ItemGroup, FinDim.CostCenter, FinDim.BusinessUnit, FinDim.ProjectĭIMENSIONATTRIBUTEVALUESET.RECID as RECID,įOR DIM_NAME IN (,, ,, ) PurchLine) add the RecId from the FinancialDimensionAttributeValueSet and join it on the Dimension reference field from the transaction table. In order to join the financial dimension pivot record with the transaction like (e.g. If some dimensions do not hold values it will be NULL in the SQL but the statement will not fail The following statement creates a single record with columns for the dimensions: SELECT * FROM The PIVOT is used to switch row values into columns. Where DIMENSIONATTRIBUTEVALUESET.RECID = 52565498264 On DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID On DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DIMENSIONATTRIBUTEVALUE.RECID On DIMENSIONATTRIBUTEVALUESET.RecId = DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET selectĭIMENSIONATTRIBUTEVALUESET.RECID as DIM_ID,ĭIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE as DIM_VALUE The following statement collects the financial dimension values from the image above (RecID for the set is 52565498264). The tables involved are the DimensionAttributeValueSet, DimensionAttributeValueSetItem, DimensionAttributeValue and the DimensionAttribute. 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. However, since Dynamics AX the financial dimension data model has been enhanced to be more flexible. At work we had the requirement to provide a SQL view of PurchLine Records including their financial dimensions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |