Monday, August 19, 2019

How to concatenate financial dimension values in a View for a given hierarchy

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 '-' +
                            STUFF((SELECT '-' + t3.DisplayValue
                                    from DimensionAttributeValueSetItemView as t3
                                    JOIN DimensionHierarchyLevel as t17
                                    on t17.DIMENSIONATTRIBUTE = t3.DIMENSIONATTRIBUTE
                                    join CDPFinDimAttrForAggr t25
                                            t17.DIMENSIONHIERARCHY = t25.DIMENSIONHIERARCHY
                                    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
                                        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.

No comments: