Skip to main content
A
Anonymous
December 16, 2019

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

  • December 16, 2019
  • 2 replies
  • 0 views
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!

    2 replies

    A
    Anonymous
    February 4, 2020
    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.

     

     

    X
    xtensibilitybro
    Newcomer
    August 15, 2022

    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!

      R
      ryannelissa
      Newcomer
      June 29, 2022

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

        G
        graessda
        Newcomer
        December 9, 2022

        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. 

        G
        graessda
        Newcomer
        December 9, 2022

        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

        Terms & ConditionsAccessibility statement