Forum Discussion

SaBrien's avatar
SaBrien
New Contributor II
8 months ago

Cube View NoData Formatting in Excel

Is there a way to format 'NoData' cells so that when the report exports to excel it shows a dash or 0, instead of the cell being blank?

Cube View
  • tuhler's avatar
    tuhler
    8 months ago

    Hi,

    I was able to accomplish this using UD8 dynamic calc. By setting up a dynamic calc to return a 0 if IsNoData is TRUE. Then you can use the Excel formatting as Krishna mentioned.

    Dim cellCheck As DataCell = api.Data.GetDataCell("UD8#None")

    If cellCheck.CellStatus.IsNoData() Then
          Return 0
    Else
         Return api.Data.GetDataCell("UD8#None").CellAmount
    End If

    Thank you,

    Thomas Uhler

  • tuhler's avatar
    tuhler
    New Contributor III
    8 months ago

    Hi,

    I was able to accomplish this using UD8 dynamic calc. By setting up a dynamic calc to return a 0 if IsNoData is TRUE. Then you can use the Excel formatting as Krishna mentioned.

    Dim cellCheck As DataCell = api.Data.GetDataCell("UD8#None")

    If cellCheck.CellStatus.IsNoData() Then
          Return 0
    Else
         Return api.Data.GetDataCell("UD8#None").CellAmount
    End If

    Thank you,

    Thomas Uhler

  • Krishna's avatar
    Krishna
    Valued Contributor
    8 months ago

    SaBrien  - Try this setting in the CV Cell Format in Excelformat  ExcelNumberFormat = [#,###,0;(#,###,0);-]

     

     

    • SaBrien's avatar
      SaBrien
      New Contributor II
      8 months ago

      We've tried many variations using the excel number format but none seem to work. Any cell that has 'NoData' will be blank and we were wanting them to show zeros.