Conditional Formatting based on 2 different columns in cube view

[Adina_Miclea]

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

[JackLacava]

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

[Adina_Miclea]

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!