Skip to main content
Dec 16, 2019 10:19 AM UTC

How to show Zero (instead of empty) in No Data cell

Originally posted by Hubert Gao

Hello,

In CubeView, is there any setting to show Zero for No Data cell instead of showing empty cell?

Thank you!

5 replies

Feb 4, 2020 4:33 PM UTC
Originally posted by Jeff Jones

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.

 

 

Newcomer
Aug 15, 2022 9:52 AM UTC

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!

Member
Jun 29, 2022 10:51 PM UTC

This only applies to the PDF format, is there a way to do this in the Cube View or Excel Views as well?

Member
Dec 9, 2022 8:40 AM UTC

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 If

 

But drill down will not work anymore. 

Member
Dec 9, 2022 9:04 AM UTC

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

 

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 If