Forum Discussion

ajackett's avatar
ajackett
New Contributor III
3 years ago
Solved

Management reports to sign flip on some expense accounts to be negative

Hi all,  We have a Global Chart of Accounts that is used for both our Statutory and Management reporting. For our management reports, there is a requirement to have expense items below OP showing as...
Reports
  • Mike_Sabourin's avatar
    Mike_Sabourin
    3 years ago

    You have a couple of options. If those lines are isolated in your cube views, you can just format that line to show as flipped. You can also use a Text field to delineate those accounts and then use a member formula in a reporting UD (typically UD8) to calculate accounts with those text fields differently. 

ajackett's avatar
ajackett
New Contributor III
3 years ago

Thanks Eric - I did actually have a look at something like this, but I hadn't really worked out how to apply it to specific accounts, or a specific group of accounts - since this will only apply to a certain group of Expenses. The parent accounts I would need to incorporate in are M_TAX and M_Depreciation, to apply to their children entities - how could this be added into the rule? I'm still just learning about writing business rules, so it's not my strong point at this stage. 

Thanks again

Mike_Sabourin's avatar
Mike_Sabourin
Contributor II
3 years ago

You have a couple of options. If those lines are isolated in your cube views, you can just format that line to show as flipped. You can also use a Text field to delineate those accounts and then use a member formula in a reporting UD (typically UD8) to calculate accounts with those text fields differently. 

  • EricOsmanski's avatar
    EricOsmanski
    Icon for OneStream Employee rankOneStream Employee
    3 years ago

    The text field is a great option- it allows you to move the maintenance from the rule to the metadata.

  • Mike_Sabourin's avatar
    Mike_Sabourin
    Contributor II
    3 years ago

    Here's code I wrote to sum by entity text, but you should be able to adapt for accounts.

    Dim viewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId)
    If viewMember.IsAnnotationType Then
    '  Return an empty string if this is a text-based dataCell.
    Return ""
    End If
     
    Dim MGMTid As Integer = api.Members.GetMemberId(DimType.Entity.Id, "MGMT")
    Dim baseEnts As List(Of Member) = api.Members.GetBaseMembers(api.Pov.EntityDim.DimPk, MGMTid, Nothing)
    Dim povText As String = api.Entity.Text(3)
    Dim LocTot As Decimal = 0
    If Not baseEnts Is Nothing Then
    For Each CurrEnt As Member In baseEnts
    Dim currText As String= api.Entity.Text(current.MemberId,3)
    If currText = povText Then
    LocTot = LocTot + api.Data.GetDataCell("E#" & CurrEnt.Name & ":T#[" + api.Pov.Time.Name + "]:V#" & api.Pov.View.Name & ":C#" & api.Pov.Cons.Name & ":O#Top:I#Top:F#Top:U1#Top:U2#Top:U3#Top:U4#" & api.Pov.UD4.Name & ":U5#Top:U6#None:U7#None:U8#None").CellAmount
    'brapi.ErrorLog.LogMessage(si, "Test-Entity~" & CurrEnt.Name & "~povText~" & povText & "~currText~" & currText & "~LocTot~" & LocTot)
    Else
    LocTot = LocTot
    End If 'If TargetCurr = povCurr Then
    Next 'CurrEnt
    If LocTot <> 0 Then 
    Return LocTot 
    Else 
    Return Nothing 
    End If
    Else
    Return Nothing
    End If 'If Not baseEnts Is Nothing Then