Conditional Formatting in CubeView in the "annotation" or other "Text" cell

YanSavinsky
New Contributor III

I would like to apply conditional formatting to a Cube View column that contains "Annotation", "Variance Explanation" or other textual data.

I have tried  IsNoData and "CellAmount" conditions, but with no success.

 

I would appreciate any direction.

Thank you!

 

Yan Savinsky

1 ACCEPTED SOLUTION

If you set the UD8 to Return a 1 if an explanation or 0 if not required.

Then set up a Delimited List Parameter where 1, 0 are the values and your Explain / Not Required text strings are the Display items.

LeeBown_0-1661949753533.png

Then set your CV column up to use this Combo Box as follows, note Modify Data = False means the cell will not allow drop down.

LeeBown_0-1661950042290.png

 

The cell value in the Cube View will display as Explain / Not Required but the cell amount will actually be 1 / 0 allowing you to perform conditional formatting.

 

 

View solution in original post

19 REPLIES 19

NicoleBruno
Contributor III

Hi Yan, 
This is what we're using for values, formatting the cell to be red when it's not zero: 

NicoleBruno_0-1646680241835.png

We also use this to call out intercompany accounts on a different CV: 

NicoleBruno_1-1646680334818.png

Maybe the issue is in the formatting of the conditional request? Hope the above are useful. 

 

Thank you, Nicole!

The row I am trying to apply conditional formatting to has View dimension set to V#Annotation so it has text in the cells instead of numbers. I tried the "CellAmount" condition and it does not work. I also tried "IsNoData" with no success. Basically I want to change the background of the cells with "Explain --->" text in them.

If (CellAmount = "Explain --->") Then

BackgroundColor = Yellow

End If

If (IsNoData <> True) Then

BackgroundColor = Yellow

End If

 

ysavinsky_0-1646684267071.png

 

Hi, 

How is the cell getting "Explain --->" in it to begin with? Is that text based on something that you could use for your conditional formatting (ie. Var % column >100)? 

I am using a UD8 member with the member formula. Formula checks that both amount and percent variances exceed specific thresholds and if they do, places "Explain --->" in the V#Annotation for that account.

I am thinking I will have to add similar checks in the CubeView Extender BR and apply formatting that way.

CubeExtender does it only for a report. I don't think it'll do that on "Data Explorer." Keep that in mind when you go ahead with it.

Hi, 

I would submit a OS support ticket and ask if any of the conditions for the conditional formatting can be used with text. I assume CellAmount is only for numbers but I wonder what the restriction on IsNoData is and what the explanation is regarding why it's not working. Maybe try IsDerivedData = True instead of IsNoData, though that's just a guess. 

ASides
New Contributor II

Hi Yan, did you come across a solution for this that did not require an Extender BR? I am in a similar situation where I need the conditional formatting to evaluate a text cell similar to yours which contains "Explain --->". 

YanSavinsky
New Contributor III

Unfortunately no: the only approach that seems to be available is the Extender business rule.

did you find a solution ? I have similar requirements

YanSavinsky
New Contributor III

Davy,

See my response above: currently the only available approach is the extender BR.

If you set the UD8 to Return a 1 if an explanation or 0 if not required.

Then set up a Delimited List Parameter where 1, 0 are the values and your Explain / Not Required text strings are the Display items.

LeeBown_0-1661949753533.png

Then set your CV column up to use this Combo Box as follows, note Modify Data = False means the cell will not allow drop down.

LeeBown_0-1661950042290.png

 

The cell value in the Cube View will display as Explain / Not Required but the cell amount will actually be 1 / 0 allowing you to perform conditional formatting.

 

 

YanSavinsky
New Contributor III

Lee, Thank you! 

Just tried this approach and it worked like a charm!

 

 

YanSavinsky_1-1661952436573.png

 

Glad to help!

YanSavinsky
New Contributor III

Davy,

 

Please see LeeBown's post below for a solution not requiring Extender BR. I tested it and it works.

If you use a cubeview extender rule, you might get some leverage (Not tested). However, I don't think the UI has anything related to TextValue.

SandeepSingh
New Contributor

BlankData_Override.PNGBlankData_Override1.PNG

 

Hi Nicole/Lee and All,

Need some help here pls. In cube view i needs to populate NM where there is "NODATA", i did played around with cellformat formula (see screenshot) still no success. Please help.

 

Thanks..Sandeep S

Also - I think the syntax is Contains instead of Contain

NicoleBruno
Contributor III

Hi Sandeep, 

Should the portion inside the red box be "If (CellAmount..." instead of RowName?

Thanks.i will try that. In order to keep going developed the report in XFDOC. Its working fine in excel :).