Forum Discussion

ajackett's avatar
ajackett
New Contributor III
2 years ago

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 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  

Reports
  • Mike_Sabourin's avatar
    Mike_Sabourin
    2 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
    Valued Contributor
    2 years ago

    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.

     

  • ajackett's avatar
    ajackett
    New Contributor III
    2 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
      2 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
        Valued Contributor
        2 years ago

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

    • EricOsmanski's avatar
      EricOsmanski
      Valued Contributor
      2 years ago

      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.

      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
      • ajackett's avatar
        ajackett
        New Contributor III
        2 years ago

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

  • ajackett's avatar
    ajackett
    New Contributor III
    2 years ago

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

  • ajackett's avatar
    ajackett
    New Contributor III
    2 years ago

    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.