Forum Discussion

WillVitale's avatar
WillVitale
Contributor
4 months ago

Conditional Formatting to Show "0"

Hello,

I'm building a cube view and we have some small amounts that are causing our B/W Pcts to have a huge %. Is there a way I can use conditional formatting to make the value show as 0 instead?

Thanks,

Will

Cube View
Reports
  • MatthiasTesch's avatar
    MatthiasTesch
    4 months ago

    Hi Will, you can use e.g. a U8# member and put a dynamic calc on it to return 0. In the formula you can also define for which cases / amount ranges you want to show 0 (e.g. < 0.01 or > 0.01). This can alos be used to show 0 in case of NODATA.

    Dim dAmount As Decimal = Api.Data.GetDataCell(your POV:U8#None).CellAmount If dAmount < 0.1 or Then Return 0 End If

  • osdevadmin's avatar
    osdevadmin
    New Contributor III
    4 months ago

    I think you may have to change the formula to round off values to 0. Even if conditional formatting is able to make it zero, it would not impact calculation as it would still go ahead and calculate large percentages using the small amounts you are getting. 

    • WillVitale's avatar
      WillVitale
      Contributor
      4 months ago

      Hi,

      I didn't know if you could use conditional formatting on the B/W % column that looks at a different column, but since I'm using a UD8 for it, I don't know if that's possible within a cube view.

      • MatthiasTesch's avatar
        MatthiasTesch
        New Contributor II
        4 months ago

        Standard formatting (also conditional) will always be applied to the current column/row based on the data in that column/row. It does not matter if the data is comming from a POV in the cube or from a calculation in the cubeview.

        If think referring in conditional formating to another column will only be possible using paramters in combination with business rules. Maybe someone has another idea...

  • MatthiasTesch's avatar
    MatthiasTesch
    New Contributor II
    4 months ago

    Hi Will, you can use e.g. a U8# member and put a dynamic calc on it to return 0. In the formula you can also define for which cases / amount ranges you want to show 0 (e.g. < 0.01 or > 0.01). This can alos be used to show 0 in case of NODATA.

    Dim dAmount As Decimal = Api.Data.GetDataCell(your POV:U8#None).CellAmount If dAmount < 0.1 or Then Return 0 End If

    • WillVitale's avatar
      WillVitale
      Contributor
      4 months ago

      Hi Matthias,

      Currently my B/W column is already a UD8, so would the best thing is to create a new one or just edit the current one?

      • MatthiasTesch's avatar
        MatthiasTesch
        New Contributor II
        4 months ago

        Correct, I'd suggest to update the formula on that UD8# member to check the calculated value and adjust it (round or set to 0) before returning it.