Показать сообщение отдельно
Старый 20.08.2019, 15:11   #2  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
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, напишите личное сообщение администратору.