04-19-2022 07:06 PM - last edited on 08-11-2023 11:04 AM by JackLacava
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")
04-21-2022 09:06 AM
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)
04-21-2022 11:30 AM
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.
04-21-2022 04:48 PM
11-28-2023 04:51 PM
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