Forum Discussion

OSAdmin's avatar
OSAdmin
Valued Contributor II
6 years ago

VarianceExplanations - Agg

Originally posted by Diane Rochford

How to I get the variance explanations, entered at base entity, to agg to the parent entity?

1 Reply

  • OSAdmin's avatar
    OSAdmin
    Valued Contributor II
    Originally posted by James Kirkby

    Hi Diane - had the same question a while back and eventually figured it out here :)

     

    I created a UD8 Dynamic Calc member, which I put in a column of a cube view to aggregate this information. Hope this helps!

     

    'Check to see if the current Cube View row has Children
    
    If api.Entity.HasChildren Then
    
    'Declare a variable outside of the loop to display the concatened footnotes
    	Dim ParentAggDispSB As New System.Text.StringBuilder()
    	
    'Get the entity in the current row to build the list of children
    	Dim CurrentEntity As String = api.Pov.Entity.Name
    	
    'Create a list that consists of the entities current children	
    	Dim CurEntityChildren As New List(Of Member)
    	CurEntityChildren = api.Members.GetChildren(api.Dimensions.GetDim("MainEntity").DimPk, api.Members.GetMember(DimType.Entity.ID,CurrentEntity).MemberId)
    	Dim CurEntToAgg As Member
    'Loop thru the Each Entity in the list of children
    		For Each CurEntToAgg In CurEntityChildren
    				
    			Dim CurEntToAggStr As String = CurEntToAgg.Name
    			
    			Dim ChildEntFootNote As DataCellEx
    'Get the current footnote
    			ChildEntFootNote = api.Data.GetDataCellEx("E#"& CurEntToAgg.Name &":V#Footnote:U8#None")	
    			
    			Dim ChildEntFootnoteStr As String
    		
    			ChildEntFootnoteStr = ChildEntFootNote.DataCellAnnotation.ToString		
    'If no footnote end the process. Otherwise add the current child footnote to a concatenated string			
    			If Not String.IsNullOrEmpty(ChildEntFootNoteStr) Then 				
    					ParentAggDispSB.Append(CurEntToAggStr)
    					ParentAggDispSB.Append(":")
    					ParentAggDispSB.Append(ChildEntFootNoteStr)
    					ParentAggDispSB.Append("||")
    			End If 
    		
    		Next
    'Return the fully concatenated string if any footnotes are available
    		
    	Return ParentAggDispSB.ToString			
    
    End If