Attempting to Show NMF in a Cube View

Ginger_Main
New Contributor II

Hello All,

 

I am working on a dynamic calc in the account dimension to calculate percent margin. If the percent margin shows above 100% or below -100%, I want the cube view cell to display 'NMF'. At the moment I believe I have the syntax correct in my formula, but maybe my execution isn't right on. My suspicion is that I can't return strings for the CreateDataCellObject. Full formula below:

 

Any help is much appreciated!

 

Dim PM As Decimal
PM = CDec(api.Data.GetDataCell("Divide(A#PRODUCT_MARGIN,A#PRODUCT_REVENUE)").CellAmount)

If PM > 100 Then
Return api.Data.CreateDataCellObject("NMF", False, False)
Else If PM < -100 Then
Return api.Data.CreateDataCellObject("NMF", False, False)
Else
Return api.Data.GetDataCell("Divide(A#PRODUCT_MARGIN,A#PRODUCT_REVENUE)")
End If

Ginger_Main_0-1665675204874.png

 

3 REPLIES 3

Cosimo
Contributor II

Hey Ginger,

I think it has more to do with the POV intersection you are using this dynamic member with. You won't be able to display the returned text if the intersecting value member is V#Periodic, Y#YTD, etc. Try to reference your dynamic member with V#Annotation or any other text based value member. Keep in mind that a true % value would actually be text and not a numeric value.

 

Ginger_Main
New Contributor II

Hey Cosimo thanks for the reply,

 

I tried the following intersection here to no avail. Maybe a possible fix would be to have a column override for the percent margin?:

Ginger_Main_0-1665687054408.png

Here is an example of how my percent margin interacts with my cube view:

Ginger_Main_1-1665687161989.png

At the moment, all of my percent calculations show in the cube view, even some going all the way up to 1,000%. The formula calculates and displays the values, it just doesn't replace those values with 'NMF' if it is over 100% or less than -100%. 

The following is a copy of the cell POV information for %Margin:

Cb#Financials:E#SWCON:P#SWCON:C#USD:S#Actual:T#2022M8:V#MTD:A#PM_PERC_MARGIN:F#EndBal:O#Top:I#Top:U1#DEPT:U2#PROGRAMS:U3#Product:U4#None:U5#None:U6#None:U7#None:U8#NODATACELL

If working with an Annotation view like Footnote, you can return simple strings, rather than a DataCell object. Try that.

I would also use brapi.ErrorLog.LogMessage to figure out which part of your code actually gets executed and when, i.e. if PM is populated when you think it is (and by what).