Forum Discussion

NidhiMangtani's avatar
NidhiMangtani
Contributor III
3 years ago

Query on annotation data copy

Hi All, I am aware of various options available for copying scenario data in OneStream. These options (data management or custom calculate) usually copy only periodic data across scenarios. Any ide...
Admin
  • PeterFu's avatar
    PeterFu
    3 years ago

    Hi,

     

    If you create a Finance Business Rule and under CustomCalculate you run this syntax, api.Data.SetDataAttachmentText(). That should copy your annotation, but you challenge will then be to make it more dynamic. Probably use sql to create a data table for data attachments.

     

     

    Peter

     

  • SWilyums's avatar
    SWilyums
    2 months ago

    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

     

SirOti's avatar
SirOti
New Contributor

Yes, this is definitely the way to go. By the way, have you successfully copied over data attachments that are files rather than texts? If yes, how did you approach it?

TonyToniTone's avatar
TonyToniTone
Contributor II
2 years ago

No I have not tried to copy over the data attachment file.  The Data Attachment table has a column called "FileName" and a column called "FileBytes".  Whenever a data attachment is uploaded to a specific data cell or a data unit, each record from within the data attachment file is stored as a unique record and tagged with the File Name.  That File Name will have a corresponding unique File Bytes ID.  This File Bytes ID is shared with each data record from the data attachment file.  There are 2 GetDataAttachments functions - 1 to get Data Attachment List by DataCellPk and 1 to get Data Attachment List by Member Script

Member Script

Dim objDataAttachmentList As DataAttachmentList = BRApi.Finance.Data.GetDataAttachments(si, memberScript, includeFileBytes, startRowIndex, pageSize)

DataCellPk

Dim objDataAttachmentList As DataAttachmentList = BRApi.Finance.Data.GetDataAttachments(si, dataCellPk, includeFileBytes, startRowIndex, pageSize)

These functions should be able to get the Data Attachment files.  However, there is not a SetDataAttachmentsFile function to update the Data Attachment table.  I'm sure there is a more creative way to update the Data Attachment table without having a SetDataAttachmentsFile type function using a combination of SELECT, INSERTS, and UPDATES