Skip to main content
R
Rev
Newcomer
July 27, 2023
Solved

Copy Annotations across scenario

  • July 27, 2023
  • 2 replies
  • 0 views

Hi,

While we copy from Scenario S#Bud1 to S#Bud2, Annotations are not getting copied.

Understand from related posts, it can be achieved using api.Data.SetDataAttachmentText(). Any tried approach using this syntax.

Any leads would be appreciated.

Thanks

    Best answer by Henning

    As a short summary to do this:

    1. Create a data table (in memory) using SQL as a source, pulling the existing comments from the table where annotations are stored (make sure to filter by time, scenario and whatever else you can narrow it down)

    2. Loop through the filtered records in your data table, and write the annotations back using the SetDataAttachmentText() function for each row in the data table, using the POV from the looped through row adjusted by scenario

     

    The code you need is briefly described in this post, which you are probably already aware of: https://community.onestreamsoftware.com/t5/Rules/Query-on-annotation-data-copy/m-p/7741 

     

    Make sure to test this on a low scale first and see if your code works. If you have no experience with code such as this, your implementation partner or the OneStream Remote Consulting team can likely help you (billable) to set this up. To get in touch with the OS Remote Consulting team, just open a ticket with mailto:support@onestream.com 

    2 replies

    H
    OneStream Employee
    HenningOneStream Employee
    OneStream Employee
    July 27, 2023

    Hi,

    Please give this post a Kudo so that this might get addressed in a future release 🙂

    https://community.onestreamsoftware.com/t5/Financial-Close-Consolidations/Copy-Annotations-Function/idi-p/15553

     

      H
      OneStream Employee
      HenningOneStream EmployeeAnswer
      OneStream Employee
      July 28, 2023

      As a short summary to do this:

      1. Create a data table (in memory) using SQL as a source, pulling the existing comments from the table where annotations are stored (make sure to filter by time, scenario and whatever else you can narrow it down)

      2. Loop through the filtered records in your data table, and write the annotations back using the SetDataAttachmentText() function for each row in the data table, using the POV from the looped through row adjusted by scenario

       

      The code you need is briefly described in this post, which you are probably already aware of: https://community.onestreamsoftware.com/t5/Rules/Query-on-annotation-data-copy/m-p/7741 

       

      Make sure to test this on a low scale first and see if your code works. If you have no experience with code such as this, your implementation partner or the OneStream Remote Consulting team can likely help you (billable) to set this up. To get in touch with the OS Remote Consulting team, just open a ticket with mailto:support@onestream.com 

        S
        SWilyums
        Contributor
        October 25, 2024

        Here is an example I used to copy VarianceExplantions from one scenario to another.  I am only copying the text values not files.  I also run it from a DM Step using Custom Calculate.

        #Region "Seed Prior Fcst VarianceExplanations to current Fcst"
        Else If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("Seed_VarExp_to_Curr_LE") Then
        'Query DataAttachment table for VarianceExplanation values for the sourceScenario
        'Write those values to a datatable (in-memory)
        'Loop through the rows in the datatable and use api.Data.SetDataAttachmentText to assign the values to the WFScenario
        Dim sourceScenario As String = args.CustomCalculateArgs.NameValuePairs("sourceScenario")
        Dim povEntity As String = api.Pov.Entity.Name
        'AttachmentType = View|200 = Annotation|220 = Assumptions|221 = AuditComment|222 = Footnote|223 = VarianceExplanation
        Dim sql As New Text.StringBuilder()
        sql.AppendLine("SELECT *")
        sql.AppendLine("FROM DataAttachment ")
        sql.AppendLine("WITH (NOLOCK) ")
        sql.AppendLine("WHERE Time = '"& povYear & "M12" & "' ")
        sql.AppendLine("AND ")
        sql.AppendLine("Scenario = '" & sourceScenario & "' ")
        sql.AppendLine("AND ")
        sql.AppendLine("Cube = 'XXXX' ")
        sql.AppendLine("AND ")
        sql.AppendLine("Entity = '" & povEntity & "' ")
        sql.AppendLine("AND ")
        sql.AppendLine("Cons = 'USD' ")
        sql.AppendLine("AND ")
        sql.AppendLine("AttachmentType = '223' ") 'View Dimension
         
        Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
           Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, sql.ToString,False)
              If dt.Rows.Count > 0 Then
                For Each dr As DataRow In dt.Rows
                api.Data.SetDataAttachmentText("A#" & dr.Item("Account") & ":V#VarianceExplanation:F#EndBalLoad:O#BeforeAdj:I#None:U1#" & dr.Item("UD1") & ":U2#No_BU:U3#No_Loc:U4#None:U5#None:U6#None:U7#Plan:U8#None", dr.Item("Text"), True)
                Next
              End If
           End Using
         End Using
        #End Region
        Terms & ConditionsAccessibility statement