This DynamicCalc formula consolidates Annotations for entities in a hierarchy.
'Cube View definition
'Row Definition: E#[Total GolfStream].tree
'Col1 Definition: V#Annotation:Name("Comment")
'Col2 Definition: V#Annotation:UD8#DynamicTextCons:Name("Consolidated")
'UD8 Member setup
'Name: DynamicTextCons <-- If this is changed it must be updated in the CV Col2 definition above.
'Formula Type: DynamicCalc
'Allow Input: True
'Is Consolidated: False
'In Use: True
'Assign the formula below to the UD8 member to show the consolidated text
Dim iEntityID As Integer = api.pov.Entity.MemberId
Dim iETestID As Integer
Dim strETestName As String = string.empty
Dim eTest As Member
Dim sSave As String = string.empty
Dim sSource As String = string.empty
If api.View.IsAnnotationType Then
If api.Entity.HasChildren() Then
For Each etest In api.Members.GetDescendents( _
api.Dimensions.GetBaseDim(dimtypeid.Entity).DimPk, iEntityID)
iETestID = etest.MemberId
sSource = api.Data.GetDataCellEx("U8#None:E#[" & etest.Name & "]:C#" & _
api.Entity.GetLocalCurrency(iETestID).Name).DataCellAnnotation
If Not sSource.Equals(String.Empty) Then
sSave = sSave.Trim & "; " & eTest.Name & ": " & sSource
End If
Next
Else
sSource = api.Data.GetDataCellEx($"U8#None:E#[{api.pov.entity.Name}]").DataCellAnnotation
If Not sSource.Equals(String.Empty) Then
sSave = ";" & sSource
End If
End If
End If
If Not sSave.Equals(String.Empty) Then
Return right(sSave, len(sSave)-1).Trim
Else
Return Nothing
End If
Source: Snippet Editor: Finance - Common Account - DynamicCalc
