Threshold setup for

anilkpantala
New Contributor

We have developed cube view for BS level commentary to avoid the business excel and email conversation on close process.
But we need suggestion on threshold setup to commentary column to allow users to input.

Ex: If Var% > 10% only users allow to enter commentary else   not.

4 REPLIES 4

FredLucas
Contributor

Hi,

As the "Can Modify Data" settings cannot be configured at cell level (only by CV, row or column definition) and your requirement is data dependent, I don't think there is an easy way to achieve that.

Also, a solution like that could potentially create a situation where a user has previously input a comment to explain a Var% > 10% and due to data changes the Var% is no longer > 10% meaning that the system would have potentially kept an outdated comment that the user would not be able to update/delete because the cell would be "locked" (i.e.: would not allow updating as the Var% was no longer >10%). 

Having said that, if you have a process in place to overcome the above scenario and you believe the requirement is a must and justifies the complexity involved and assuming you do not need to use member expansion in rows (e.g.: A#AccX.Base) you could try placing an XFBR on the Member Filter of the Row Overrides section of the commentary column.

The XFBR would receive the POV and the threshold as parameters, calc the variance for the given POV and return:

An empty string if the calculated variance was over the threshold (i.e.: no change to the original POV) or something like "GetDataCell(U8#|CVUD8|)" so it returns the same POV but using a GetDataCell which would in principle turn that cell read only.

I've not tested this solution but from a technical POV might be worth to give it a try.

Regards,

Fred

Thank you for suggestion here FredLucas and do have XFBR on threshold limit? If you have, please share that helps. 

Krishna
Valued Contributor

@anilkpantala  -  My assumption is that cannot be done in Cell Format but you can try in XFBR. See an exmaple you should call this XFBR in your column.

		If args.FunctionName.XFEqualsIgnoreCase("Test3") Then
					
Dim Cbe As String = String.Empty
Dim Msct As String = String.Empty
Dim myCell As DataCellInfoUsingMemberScript
Dim myValue As Decimal = 0
Dim sValue As String = String.Empty
			
				Cbe  = "CorpStd"
								Msct   = "E#E1000:C#USD:S#Actual_YTD_Periodic:T#2024M2:V#YTD:A#A1000:F#Total_Flow:O#Top:I#Top:U1#Total_CostCenter:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None"
								myCell  = BRApi.Finance.Data.GetDataCellUsingMemberScript(si,Cbe,Msct)
								myValue  = myCell.DataCellEx.DataCell.CellAmount

					If 		myValue > 5000 Then 
						Brapi.ErrorLog.LogMessage(si, "IF ----")
						sValue = "V#YTD:I#Top:O#Top:F#EndBalInput:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None"
					
					Else 

						sValue = "V#VarianceExplanation:I#Top:O#Forms:F#EndBalInput:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None"
						
					Return sValue
				
				End If	
				
			End If 	

 

Thanks
Krishna

I tried no luck since threshold should work based on variance % below is the column calculation for Var%

GetDataCell((Divide(CVC(VarMo), CVC(YTDCurYR)))*100):Name(VarPercentage) 

expected results is If Var% > 10% only users allow to enter commentary else   not.