Forum Discussion

Bansal_P's avatar
Bansal_P
New Contributor II
29 days ago

Conditional Statement in the Cube View Columns

Hey All,

We got a recent requirement of implementing a conditional statement inside the cube view columns. The user wants to calculate a certain column on the basis of the value of another column. The design is explained below:

If B <> 0 then

D= B+C

else if B = 0 then

D= A+C

ABCD
1010020120 (100+20)
200130150 (20+130)
0203050 (20+30)

I'm currently facing difficulty to implement this solution inside the cube view. My first shot was trying something in the XFBR string but that I couldn't get around the logic to implement the same.

Any leads will be appreciated.

 

Thanks!

Pragyanshu Bansal

JackLacava OSAdmin 

Cube View
Parameters
Reports
  • EdwinS's avatar
    EdwinS
    29 days ago

    Hi Bansal, 

    You can try using the custom function in finance business rules, you can Add the function in the cube view using GetDataCell, like: (BR#[BRName=YourFinBusinessRule, FunctionName=CalculateD, A=S#Actual,B=S#Budget, C=T#2023M12]):Name(D)

    In the finance business rule you can add the below code segment:

    Select Case api.FunctionType
				Case Is = FinanceFunctionType.DataCell
					If args.DataCellArgs.FunctionName.XFEqualsIgnoreCase("CalculateD") Then
						Dim colA As String = args.DataCellArgs.NameValuePairs.XFGetValue("A")
						Dim colB As String = args.DataCellArgs.NameValuePairs.XFGetValue("B")
						Dim colC As String = args.DataCellArgs.NameValuePairs.XFGetValue("C")
						Dim A As Decimal = api.Data.GetDataCell($"{colA}").CellAmount
						Dim B As Decimal = api.Data.GetDataCell($"{colB}").CellAmount
						Dim C As Decimal = api.Data.GetDataCell($"{colC}").CellAmount
						If Not B = 0 Then
							Return B + C
						Else 
							Return A + C
						End If
					End If
				End Select

    I tested this and it worked. You can expand on the suggestion to meet your needs.

     

  • EdwinS's avatar
    EdwinS
    New Contributor II
    29 days ago

    Hi Bansal, 

    You can try using the custom function in finance business rules, you can Add the function in the cube view using GetDataCell, like: (BR#[BRName=YourFinBusinessRule, FunctionName=CalculateD, A=S#Actual,B=S#Budget, C=T#2023M12]):Name(D)

    In the finance business rule you can add the below code segment:

    Select Case api.FunctionType
				Case Is = FinanceFunctionType.DataCell
					If args.DataCellArgs.FunctionName.XFEqualsIgnoreCase("CalculateD") Then
						Dim colA As String = args.DataCellArgs.NameValuePairs.XFGetValue("A")
						Dim colB As String = args.DataCellArgs.NameValuePairs.XFGetValue("B")
						Dim colC As String = args.DataCellArgs.NameValuePairs.XFGetValue("C")
						Dim A As Decimal = api.Data.GetDataCell($"{colA}").CellAmount
						Dim B As Decimal = api.Data.GetDataCell($"{colB}").CellAmount
						Dim C As Decimal = api.Data.GetDataCell($"{colC}").CellAmount
						If Not B = 0 Then
							Return B + C
						Else 
							Return A + C
						End If
					End If
				End Select

    I tested this and it worked. You can expand on the suggestion to meet your needs.