Annotation - Comments

[DCruz]

Hello,

The customer would like to explain a dollar variance between Budget and Actual by leveraging 4 columns of data to explain the difference displayed: enter a dollar amount, a combo box to select a type of UD1 data timing (Perm/Temp) and another combo box to select UD8 driver type (Audit Change/Scope Change) with the last column for a comment. 

The comment input cell has the following: V#Annotation with UD3&UD5 based upon the CV POV selection and the remaining UD's set to None; V#Annotation:UD1#None:UD8#None.

For reporting purposes the customer would like to associate the commentary entered with the Combo Box selections made for UD1 and UD8.

V#Annotation:UD1#Perm:UD8#AuditChange

What is the best method to do this?

I have tried the following:

api.Data.Calculate("U8#AuditChange:V#Annotation:U1#Perm":O#Import" _
& " = U8#None:V#Annotation:U1#None:O#Top")

[TheJonG]

Hi Daisy - I am not sure I totally follow...are you trying to move or copy the comment the user entered? If so, api.Data.Calculate will not work for copying annotations or commentary. Something like the below would work.

Dim yourText As String = api.Data.GetDataCellEx("U8#AuditChange:V#Annotation:U1#Perm:O#Import").DataCellAnnotation

api.Data.SetDataAttachmentText("U8#None:V#Annotation:U1#None:O#Top",yourText,False)

[Koemets]

To piggyback on Jon's reply. Annotations are attachments by nature, so they can't be treated as regular data cell in your code. So you need to attach them to the respective datacells in the way Jon suggested. 

[DCruz]

Thanks Jo.  I'll try that option.  Here's a doc I put together to hopefully better explain the intent of the solution that I'm seeking.

[d_rochford]

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