Conditional Formatting to Show "0"

[WillVitale]

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

[MatthiasTesch · Accepted solution]

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]

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]

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.  

[osdevadmin]

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]

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]

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

[WillVitale]

Hi Matthias,

I just edited my formula to change the value on which we use to make the value 0.

Thanks,

Will