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

[ajackett]

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  

[EricOsmanski]

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]

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

[EricOsmanski]

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]

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

[Michel_Sabourin · Accepted solution]

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]

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

[Michel_Sabourin]

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

[ajackett]

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

[ajackett]

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.