20.08.2019, 15:09 | #1 |
Участник
|
How to concatenate financial dimension values in a View for a given hierarchy
Источник: http://alexvoy.blogspot.com/2019/08/...dimension.html
============== Following my previous posting How to filter existing transactions based on a financial dimension value set , I would like to show how we can construct a view with all financial dimenion values for a given hierarchy, including placeholders for those values are absent. In the aforementioned example, it was CDPDimensionAttributeValuesUnionConcatView view as depicted. Instead of a series of dependant views, we can create one view as follows (CDPDimAttrSelectedView is just a set of selected attributes; explained in the previous link) The key point is a computed column method, which creates a final string in the same sequence of attributes as selected in the hierarchy. private static server str finDimValues() // X++ { return @"STUFF((SELECT '-' + ISNULL( STUFF((SELECT '-' + t3.DisplayValue from DimensionAttributeValueSetItemView as t3 JOIN DimensionHierarchyLevel as t17 on t17.DIMENSIONATTRIBUTE = t3.DIMENSIONATTRIBUTE join CDPFinDimAttrForAggr t25 on t17.DIMENSIONHIERARCHY = t25.DIMENSIONHIERARCHY where t1.DimensionAttributeValueSet = t3.DimensionAttributeValueSet and t7.DIMENSIONATTRIBUTE = t3.DIMENSIONATTRIBUTE order by t17.LEVEL_ FOR XML path('') ), 1, 1, '') , 'N/A') FROM DIMENSIONATTRIBUTE t6 JOIN DimensionHierarchyLevel as t7 on t7.DIMENSIONATTRIBUTE = t6.RECID join CDPFinDimAttrForAggr t15 on t7.DIMENSIONHIERARCHY = t15.DIMENSIONHIERARCHY FOR XML PATH('')), 1, 1, '')"; } Thanks a lot for all participants on the forum thread, and especially to Kair84 who helped me with SQL command. Источник: http://alexvoy.blogspot.com/2019/08/...dimension.html
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору. |
|
20.08.2019, 15:11 | #2 |
Участник
|
How to concatenate financial dimension values in a View for a given hierarchy
Источник: http://alexvoy.blogspot.com/2019/08/...dimension.html
============== Following my previous posting How to filter existing transactions based on a financial dimension value set , I would like to show how we can construct a view with all financial dimenion values for a given hierarchy, including placeholders for those values are absent. In the aforementioned example, it was CDPDimensionAttributeValuesUnionConcatView view as depicted. Instead of a series of dependant views, we can create one view as follows (CDPDimAttrSelectedView is just a set of selected attributes; explained in the previous link) The key point is a computed column method, which creates a final string in the same sequence of attributes as selected in the hierarchy. private static server str finDimValues() // X++ { return @"STUFF((SELECT '-' + ISNULL( STUFF((SELECT '-' + t3.DisplayValue from DimensionAttributeValueSetItemView as t3 JOIN DimensionHierarchyLevel as t17 on t17.DIMENSIONATTRIBUTE = t3.DIMENSIONATTRIBUTE join CDPFinDimAttrForAggr t25 on t17.DIMENSIONHIERARCHY = t25.DIMENSIONHIERARCHY where t1.DimensionAttributeValueSet = t3.DimensionAttributeValueSet and t7.DIMENSIONATTRIBUTE = t3.DIMENSIONATTRIBUTE order by t17.LEVEL_ FOR XML path('') ), 1, 1, '') , 'N/A') FROM DIMENSIONATTRIBUTE t6 JOIN DimensionHierarchyLevel as t7 on t7.DIMENSIONATTRIBUTE = t6.RECID join CDPFinDimAttrForAggr t15 on t7.DIMENSIONHIERARCHY = t15.DIMENSIONHIERARCHY FOR XML PATH('')), 1, 1, '')"; } Thanks a lot for all participants on the forum thread, and especially to Kair84 who helped me with SQL command. Источник: http://alexvoy.blogspot.com/2019/08/...dimension.html
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору. |
|
|
|