03-27-2023 04:35 AM - last edited on 05-24-2023 11:33 AM by JackLacava
Hello all,
Is there any way to set a conditional formatting in a cube view column based on 2 different columns?
I have 2 variance columns (one on dollar amount and another with a % variance) that should have a red color if 2 conditions are met. For example if the variances are higher than 200k and 20%.
Any input is appreciated.
Thank you!
Adina
03-29-2023 06:22 AM
A workaround in CV could be to create a third column, with its value calculated from the first two. You can do that calculation with an XFBR or with a GetDataCell custom function. I'll show the latter, because it's a bit less common and equally useful (note this code is not well-tested, I'm showing the principle). In the member expansion you'd have:
GetDataCell(BR#[BRName=MyFinanceBR, FunctionName=MyCustomCheck, FirstCriteria=CVC(Col1), SecondCriteria=CVC(Col2)]):Name(Pass Or Fail)
You'd then need a Finance Business Rule that implements that function:
' in MyFinanceBR
Case Is = FinanceFunctionType.DataCell
If args.DataCellArgs.FunctionName.XFEqualsIgnoreCase("MyCustomCheck") Then
If args.DataCellArgs.NameValuePairs("FirstCriteria") > 200000 _
And args.DataCellArgs.NameValuePairs("SecondCriteria") > 0.20 Then
Return 1
Else
Return 0
End If
End If
At that point you can have conditional formatting on the cell that works with the returned value:
If (CellAmount = 1) Then
BackgroundColor = Red
end if
03-29-2023 10:19 AM
Thanks for your help! It doesn't seem to work unfortunately.
We managed to do the same but in a different way 🙂 We created dynamic calcs in UD8 for the variance between the 2 periods and for the percent variance. Then created a BR that would return 1 or 2 based on the same logic:
Case Is = FinanceFunctionType.DataCell
If args.DataCellArgs.FunctionName.XFEqualsIgnoreCase("FunctionName") Then
Dim varVsPY As Decimal = api.data.GetDataCell("U8#varVsPY").CellAmount
Dim varVsPYpct As Decimal = api.data.GetDataCell("U8#varVsPYpct").CellAmount
If varVsPY >200000 And varVsPYpct >20 Then
Return 2
Else Return 1
End If
End If
However, was looking for the variance cells to be formatted. It seems not possible ...
Thanks!