Skip to main content
A
ajackett
Newcomer
May 11, 2023
Solved

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

  • May 11, 2023
  • 4 replies
  • 0 views

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 negative - such as Tax, Interest, Depreciation etc. Opex accounts above OP will be shown as positive, as is currently happening. 

I understand this could be done by allocating the sign flip to the specific accounts in the transformation rules and/or changing the account type to Revenue, however, this is only relevant for the Mgmt set of reports, not the Stat reports. 

I'm interested in seeing if anyone has any ideas for how to show different signage on certain reports, rather than applying them all reports. On the cube views, the rows currently us A#M_NPAT.base for the entire CoA . 

Thanks in advance. 

Andrew  

Best answer by Mike_Sabourin

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. 

4 replies

E
OneStream Employee
EricOsmanskiOneStream Employee
OneStream Employee
May 11, 2023

You could write a dynamic calc similar to the below (usually in UD8) in order to flip the sign on certain accounts. The logic in the rule would be dependent upon your COA and would likely needed to be modified from the below example.

EricOsmanski_0-1683806559284.png

 

    A
    ajackettAuthor
    Newcomer
    May 11, 2023

    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

    E
    OneStream Employee
    EricOsmanskiOneStream Employee
    OneStream Employee
    May 11, 2023

    Without knowing even more specifics here is some sample code. This is untested but might be able to get you close. You will have to update the red highlight at the very least.

    EricOsmanski_0-1683808005593.png

    Dim acct As String = api.Pov.Account.Name
Dim acctMbrId As Integer = api.Members.GetMemberId(DimType.Account.Id, acct)
Dim acctMbrInfo As MemberInfo = BRApi.Finance.Members.GetMemberInfo(si, DimType.Account.Id, acctMbrId, False)
Dim acctDimPk As DimPk = api.Dimensions.GetDim("AccountDimName").DimPk
Dim mbrListM_TAX As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(acctDimPk, "M_TAX.Base")
Dim mbrListM_Depreciation As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(acctDimPk, "M_Depreciation.Base")

If (Not acct Is Nothing) Then
	If mbrListM_TAX.Contains(acctMbrInfo) OrElse mbrListM_Depreciation.Contains(acctMbrInfo) Then
		Return api.Data.GetDataCell("A#" + acct + ":U8#None * -1")
	Else
		Return api.Data.GetDataCell("A#" + acct + ":U8#None")
	End If
End If

Return Nothing
    A
    ajackettAuthor
    Newcomer
    May 11, 2023

    Thanks Eric - I'll take a look through both this and Michel's option and see how I go!

    A
    ajackettAuthor
    Newcomer
    May 11, 2023

    Thanks Michel - I will have a look at this tomorrow and see how I go!

    A
    ajackettAuthor
    Newcomer
    May 16, 2023

    Thanks guys - I have ended up creating a new cube view row template to separate the GL among different rows. Previously I had it as 1 row for A#NPAT.base so there was less maintenance, but now I have separated out the different PL sections to maintain the sign flip that through the cube view formatting. 

    I do appreciate your responses though, the coding I think will come in useful for other ideas that I have, especially using the Text fields.  

    Terms & ConditionsAccessibility statement