Skip to main content
P
prachtiwari11
June 4, 2024
Solved

Remove zeros in background in CVs

  • June 4, 2024
  • 1 reply
  • 0 views

Hello All,

Any ideas how I can remove these "0's" from the background in the Cubeviews? Is there any property that will make it disabled?

    Best answer by FredLucas

    Hi prachtiwari11,

    These zeros are what OS calls as Derived Data.

    These are the result of the Periodic / YTD view calculation.

    So assuming that showing the YTD number does not give you the expected results, I don't think there is an out-of-the-box way to suppress this type of data but there's a workaround that could maybe work for you which is to use the conditional formatting to hide these numbers by formatting them with the same color as the cell background.

    You could do so by using the conditional cell format and checking for "IsDerivedData". 

    Example: 

    If (IsDerivedData = True) Then  
  TextColor = White
End If

     

    In case your Cube View is for reporting only, i.e.: not required for inputs, you could use this technique instead (as per KB article):

    Introduction

    Is it possible to hide the derived values that can be seen in a cube view (greyed out values)? 

     

    Step by Step Instructions

    This can be done using a UD8 member.

    1. Create a new member in a spare UD dimension such as UD8.
    2. Put a dynamic calculation formula on this member to suppress the derived values, see the sample below.
    3. Call this member in the cube view.
     
     Dim viewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId)

    '=== Supress derived data ===
    If viewMember.IsAnnotationType = False Then
    If api.Data.GetDataCell("U8#None").CellStatus.IsDerivedData = False Then
    Return api.Data.GetDataCell("U8#None")
    Else
    Return Nothing
    End If
    Else
    Return Nothing
    End If


    Please note: This setup is for reporting purposes only. When changing the UD8 member to this dynamic member, the cell will no longer be available for input. If the cell needs to be available for input, either this solution cannot be utilized, or create a UD8 list parameter, so the user can select UD8#None for input, or UD8#<dynamic member> for reporting.

    1 reply

    F
    FredLucasAnswer
    June 4, 2024

    Hi prachtiwari11,

    These zeros are what OS calls as Derived Data.

    These are the result of the Periodic / YTD view calculation.

    So assuming that showing the YTD number does not give you the expected results, I don't think there is an out-of-the-box way to suppress this type of data but there's a workaround that could maybe work for you which is to use the conditional formatting to hide these numbers by formatting them with the same color as the cell background.

    You could do so by using the conditional cell format and checking for "IsDerivedData". 

    Example: 

    If (IsDerivedData = True) Then  
  TextColor = White
End If

     

    In case your Cube View is for reporting only, i.e.: not required for inputs, you could use this technique instead (as per KB article):

    Introduction

    Is it possible to hide the derived values that can be seen in a cube view (greyed out values)? 

     

    Step by Step Instructions

    This can be done using a UD8 member.

    1. Create a new member in a spare UD dimension such as UD8.
    2. Put a dynamic calculation formula on this member to suppress the derived values, see the sample below.
    3. Call this member in the cube view.
     
     Dim viewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId)

    '=== Supress derived data ===
    If viewMember.IsAnnotationType = False Then
    If api.Data.GetDataCell("U8#None").CellStatus.IsDerivedData = False Then
    Return api.Data.GetDataCell("U8#None")
    Else
    Return Nothing
    End If
    Else
    Return Nothing
    End If


    Please note: This setup is for reporting purposes only. When changing the UD8 member to this dynamic member, the cell will no longer be available for input. If the cell needs to be available for input, either this solution cannot be utilized, or create a UD8 list parameter, so the user can select UD8#None for input, or UD8#<dynamic member> for reporting.
    M
    MikeG
    June 4, 2024

    FredLucas I would suggest TextColor = Transparent, instead of white.  For the Cell Format you can control what is displayed when the resulting data cell is zero:   NumberFormat = [#,###;(#,###);" "].

    The 3rd argument is how to handle when value is zero, i.e. show a blank space instead of the value zero.

    Also, you can do ReportNoDataNumberFormat = ["0"], replace the zero with a blank space.

    Hope this helps.

    P
    prachtiwari11Author
    June 4, 2024

    MikeG This is only hiding the "0" from the cell, but as soon as I click on the cell, automatically, 0 is getting entered. I want no data in my cell, it should purely be for manual input. Is there any way how I can achieve without using Business rule?

    Terms & ConditionsAccessibility statement