03-07-2022
11:26 AM
- last edited
a week ago
by
JackLacava
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
Solved! Go to Solution.
08-31-2022 08:44 AM - edited 08-31-2022 08:47 AM
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.
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.
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.
03-07-2022 02:12 PM
Hi Yan,
This is what we're using for values, formatting the cell to be red when it's not zero:
We also use this to call out intercompany accounts on a different CV:
Maybe the issue is in the formatting of the conditional request? Hope the above are useful.
03-07-2022 03:20 PM
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
03-07-2022 03:51 PM
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)?
03-07-2022 03:56 PM
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.
03-07-2022 04:36 PM
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.
03-07-2022 05:02 PM
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.
07-18-2022 03:55 PM
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 --->".
07-18-2022 03:59 PM
Unfortunately no: the only approach that seems to be available is the Extender business rule.
08-30-2022 11:04 PM
did you find a solution ? I have similar requirements
08-31-2022 08:31 AM
Davy,
See my response above: currently the only available approach is the extender BR.
08-31-2022 08:44 AM - edited 08-31-2022 08:47 AM
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.
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.
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.
08-31-2022 09:27 AM
Lee, Thank you!
Just tried this approach and it worked like a charm!
08-31-2022 02:33 PM
Glad to help!
08-31-2022 09:29 AM
Davy,
Please see LeeBown's post below for a solution not requiring Extender BR. I tested it and it works.
03-07-2022 03:25 PM
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.
11-13-2022 11:23 AM - edited 11-13-2022 11:26 AM
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
11-14-2022 09:28 AM
Also - I think the syntax is Contains instead of Contain
11-14-2022 09:27 AM
Hi Sandeep,
Should the portion inside the red box be "If (CellAmount..." instead of RowName?
11-14-2022 09:37 AM
Thanks.i will try that. In order to keep going developed the report in XFDOC. Its working fine in excel :).