How to show Zero (instead of empty) in No Data cell
Hello,
In CubeView, is there any setting to show Zero for No Data cell instead of showing empty cell?
Thank you!
Forum Discussion
This only applies to the PDF format, is there a way to do this in the Cube View or Excel Views as well?
You can create a UD8 Member with a dynamic calc and select this member in CV´s POV.
If api.Data.GetDataCell("E#" & Entity & ":P#" & Parent & ":C#" & Cons & ":S#" & Scenario & ":T#" & Time & ":V#" & View & ":A#" & Account & ":F#" & Flow & ":O#" & Origin & ":I#" & IC & ":U1#" & U1 & ":U2#" & U2 & ":U3#" & U3 & ":U4#" & U4 & ":U5#" & U5 & ":U6#" & U6 & ":U7#" & U7 &":U8#None").CellStatus.IsNoData Then
Return api.Data.CreateDataCellObject(0.0, False, False)
Else
Return api.Data.GetDataCell("E#" & Entity & ":P#" & Parent & ":C#" & Cons & ":S#" & Scenario & ":T#" & Time & ":V#" & View & ":A#" & Account & ":F#" & Flow & ":O#" & Origin & ":I#" & IC & ":U1#" & U1 & ":U2#" & U2 & ":U3#" & U3 & ":U4#" & U4 & ":U5#" & U5 & ":U6#" & U6 & ":U7#" & U7 & ":U8#None").CellAmount
End IfBut drill down will not work anymore.
Forgot this before
Dim Entity As String = api.Pov.Entity.Name
Dim Parent As String = api.Pov.Parent.Name
Dim Cons As String = api.Pov.Cons.Name
Dim Scenario As String = api.Pov.Scenario.Name
Dim Time As String = api.Pov.Time.Name
Dim View As String = api.Pov.View.Name
Dim Account As String = api.Pov.Account.Name
Dim Flow As String = api.Pov.Flow.Name
Dim Origin As String = api.Pov.Origin.Name
Dim IC As String = api.Pov.IC.Name
Dim U1 As String = api.Pov.UD1.Name
Dim U2 As String = api.Pov.UD2.Name
Dim U3 As String = api.Pov.UD3.Name
Dim U4 As String = api.Pov.UD4.Name
Dim U5 As String = api.Pov.UD5.Name
Dim U6 As String = api.Pov.UD6.Name
Dim U7 As String = api.Pov.UD7.NameIf api.Data.GetDataCell("E#" & Entity & ":P#" & Parent & ":C#" & Cons & ":S#" & Scenario & ":T#" & Time & ":V#" & View & ":A#" & Account & ":F#" & Flow & ":O#" & Origin & ":I#" & IC & ":U1#" & U1 & ":U2#" & U2 & ":U3#" & U3 & ":U4#" & U4 & ":U5#" & U5 & ":U6#" & U6 & ":U7#" & U7 &":U8#None").CellStatus.IsNoData Then
Return api.Data.CreateDataCellObject(0.0, False, False)
Else
Return api.Data.GetDataCell("E#" & Entity & ":P#" & Parent & ":C#" & Cons & ":S#" & Scenario & ":T#" & Time & ":V#" & View & ":A#" & Account & ":F#" & Flow & ":O#" & Origin & ":I#" & IC & ":U1#" & U1 & ":U2#" & U2 & ":U3#" & U3 & ":U4#" & U4 & ":U5#" & U5 & ":U6#" & U6 & ":U7#" & U7 & ":U8#None").CellAmount
End If
This can be done with the ReportNoDataNumberFormat option in the Cell Format Settings for the rows or columns.
ReportNoDataNumberFormat By default, XF displays NoData (i.e. null) cells as empty text in the Data Explorer and as zeroes in a report. However, any .NET number format text can be specified to format those zero values differently in the report. For example, show the word NODATA if desired by typing NODATA in that setting. To display empty text, type # in that setting. If the cell’s number format setting already does something with null values, this property does not need to be filled.
You can use any of the number formats listed in the object lookup.
Hope this answers your question.
I would like to also follow up on this. Can you please explain how to leverage this functionality for the data explorer view?
Thank you!
