Forum Discussion

prachtiwari11's avatar
prachtiwari11
New Contributor II
7 months ago

Remove zeros in background in CVs

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?
Cube View
Parameters
  • FredLucas's avatar
    FredLucas
    7 months ago

    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.
FredLucas's avatar
FredLucas
Contributor III

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.
MikeG's avatar
MikeG
Contributor III
7 months ago

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.

  • prachtiwari11's avatar
    prachtiwari11
    New Contributor II
    7 months ago

    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?

    • MikeG's avatar
      MikeG
      Contributor III
      7 months ago

      Hi prachtiwari11, zero is the correct value in the datacell correct?  And you want to show a null or blank instead of the zero?  If you have a rule that is calculating zero you may want to visit the rule and update the api.DataCalculate to a RemoveZeros() function call so you don't calculate and store the zero if that is not desired.  There are additional methods to hide derived values if you search here on Community you'll find more examples - the UD8 dynamic calc method that FredLucas mentioned is another approach.