Источник:
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