The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

Bansal_P's avatar
Bansal_P
New Contributor III
1 year ago
Solved

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. T...
Cube View
Parameters
Reports
  • EdwinS's avatar
    EdwinS
    1 year 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
1 year 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.