Hi,
I have am using U5 member for my client to write comments based on U8 Member which checks the variance between 2 columns and puts a Yes/No or prior year not used in the UD8 on the report. UD8 is set as a Dynamic cal formula. Below is an example for Income Statement change to prior month, periodic view.
Dim timeName As String = Api.Pov.Time.Name
Dim timeId As String = Api.Pov.Time.MemberId
Dim priorTime As Integer = api.Time.AddTimePeriods(-1,True)
Dim priorTimeName As String = api.Time.GetNameFromId(priorTime)
Dim acct As String = api.Pov.Account.Name
Dim cc As String = api.Pov.UD1.Name
'Const None_All = ":O#Top:F#None:I#None:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None"
'GetDataCell("T#WF:V#Periodic-T#WFPrior1:V#Periodic"):Name("Prior Mo Var")
Dim current As Decimal = api.Data.GetDataCell("A#[" & acct & "]:U1#[" & cc & "]:C#USD:V#Periodic:T#" & timeName & ":U8#None").CellAmount
Dim prior As Decimal = api.Data.GetDataCell("A#[" & acct & "]:U1#[" & cc & "]:C#USD:V#Periodic:T#" & priorTimeName & ":U8#None").CellAmount
Dim curr As String = api.Pov.Cons.Name
Dim ent As String = api.Pov.Entity.Name
'Return the specified number of characters from the start of the text string
Dim acctname As String = api.Pov.Account.Name '<--Text string containing characters to extract
Dim numChars As Integer = 1 '<--Number of characters in string to be extracted
Dim rightChars As String = "R"
If ViewMember.IsAnnotationTypeViewId(api.Pov.View.MemberId) Then
If (curr = "USD" And ent = "SWM_Legal_Consolidated") Then
If (Not System.Math.Abs(prior) > 0) And System.Math.Abs(current) > 1 Then
Return "Acct not used Last year"
Else If (System.Math.Abs(prior) = 0) And System.Math.Abs(current) = 0 Then
Return " "
Else If (((System.Math.Abs(current - prior) > 2000000) And (System.Math.Abs((current - prior) / prior) > .094999))) Then
Return "Yes > $ 2,000,000 and 10%"
End If
End If
If (curr = "USD" And Not ent = "SWM_Legal_Consolidated") Then
If (Not System.Math.Abs(prior) > 0) And System.Math.Abs(current) > 1 Then
Return "Acct not used Last year"
Else If (System.Math.Abs(prior) = 0) And System.Math.Abs(current) = 0 Then
Return " "
Else If System.Math.Abs(current - prior) > 1000000 Then
Return "Yes > $ 1,000,000"
Else If (((System.Math.Abs(current - prior) > 500000) And (System.Math.Abs((current - prior) / prior) > .094999))) Then
Return "Yes > $ 500,000 and 10%"
Else
Return "No"
End If
End If
End If
Note, I then created a U5# Dynamic calc formula to consolidate the U5 comments entered to the parent entity
'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("U5#ISV_PM_Periodic_Actual:U8#None:E#[" & etest.Name & "]:C#USD").DataCellAnnotation
If Not sSource.Equals(String.Empty) Then
sSave = sSave.Trim & "; " & eTest.Name & ": " & sSource
End If
Next
Else
sSource = api.Data.GetDataCellEx("U5#ISV_PM_Periodic_Actual: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
End If
