Remove zeros in background in CVs

[prachtiwari11]

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?

[FredLucas · Accepted solution]

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]

@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]

@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]

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.