Forum Discussion

Ginger_Main's avatar
Ginger_Main
New Contributor II
3 years ago

Attempting to Show NMF in a Cube View

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

 

Cube View
  • Cosimo's avatar
    Cosimo
    Contributor II
    3 years ago

    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's avatar
      Ginger_Main
      New Contributor II
      3 years ago

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

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

      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

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        3 years ago

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