OneStream Community

Rams_2022 · Monday

I am writing the Business rule in Revenue % account dimension, Revenue % = X / PY Y. My PY can be based on POV. For example, if user can select current year as FY2024 then PY is FY2023 or If user choose MAY 2024, then PY is MAY 2023 or if user choose Q2 2024 then the PY is Q2 2023.

I am using this code and I am able to achieve if it is year, but I need more of dynamic based on user selected PoV. how to automate this code based on PoV. any suggestions would be really appreciated.

Return api.Data.GetDataCell("-A#X / (A#Y:T#GlobalPrior1")

jmohl · Monday

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine

Namespace OneStream.BusinessRule.DashboardStringFunction.SampleTimeRule
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As Object
			Try
					If (args.FunctionName.XFEqualsIgnoreCase("GetPriorTimeDyn")) Then
					
						'Use name/value pair to store the POV time in the business rule
						Dim povTimeName As String = args.NameValuePairs.XFGetValue("PovTimeName")
							
						'Extract the current and prior year names from the selected POV time
						Dim povYearName As String = TimeDimHelper.GetYearFromId(TimeDimHelper.GetIdFromName("povTime"))
						Dim priorYearName As String = TimeDimHelper.GetYearFromId(TimeDimHelper.GetPriorYearPeriodId(TimeDimHelper.GetIdFromName(povTimeName)))
						
						'Create empty string to store return time
						Dim returnTimeName As String = String.Empty
						
						If povTimeName.Length = 4 'Year
								
							'Return the same month for the prior year
							returnTimeName = priorYearName
							
						Else If povTimeName.Substring(4, 1).XFEqualsIgnoreCase("M") 'Months
								
							'Extract current month from POV time and convert to a string 
							Dim povMonthNum As String = TimeDimHelper.GetSubComponentsFromName(povTimeName).Month.XFToString
							
							'Return the same month for the prior year
							returnTimeName = String.Concat(priorYearName,"M", povMonthNum)
							
						Else If povTimeName.Substring(4, 1).XFEqualsIgnoreCase("Q") 'Quarters
								
							'Extract current quarter from POV time and convert to a string 
							Dim povQuarterNum As String = TimeDimHelper.GetSubComponentsFromName(povTimeName).Quarter.XFToString
							
							'Return the same month for the prior year
							returnTimeName = String.Concat(priorYearName,"Q", povQuarterNum)
															
						Else If povTimeName.Substring(4, 1).XFEqualsIgnoreCase("H") 'Half Year
								
							'Extract current quarter from POV time and convert to a string 
							Dim povHalfYearNum As String = TimeDimHelper.GetSubComponentsFromName(povTimeName).HalfYear.XFToString
							
							'Return the same month for the prior year
							returnTimeName = String.Concat(priorYearName,"H", povHalfYearNum)
					
						End If
							
						Return returnTimeName
							
					End If
				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

This XFBR will dynamically call the corresponding prior year equivalent. You should be able to use a name/value pair to similarly insert the relevant account into the calculation. There is likely a simpler way to accomplish what you are looking for, but this should work for most cases.

To call the XFBR from a member filter, use the following: T#|POVTime|, T#XFBR(SampleTimeRule, GetPriorTimeDyn, povTimeName=|POVTime|) - you can adjust this to use parameters as needed.

Rams_2022 · Monday

@jmohl thank you so much. but while compiling the code and I am getting an error message.

Unable to compile formula.

1) Error at line 24: 'FunctionName' is not a member of 'FinanceRulesArgs'.

2) Error at line 27: 'NameValuePairs' is not a member of 'FinanceRulesArgs'.

any suggestion please to resolve this code

Rams_2022 · Monday

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine

Namespace OneStream.BusinessRule.DashboardStringFunction.SampleTimeRule
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As Object
Try
If args.FunctionName.XFEqualsIgnoreCase("GetPriorTimeDyn") Then
' Use name/value pair to store the POV time in the business rule
Dim povTimeName As String = args.NameValuePairs.XFGetValue("PovTimeName")

' Extract the current and prior year names from the selected POV time
Dim povYearName As String = TimeDimHelper.GetYearFromId(TimeDimHelper.GetIdFromName(povTimeName))
Dim priorYearName As String = TimeDimHelper.GetYearFromId(TimeDimHelper.GetPriorYearPeriodId(TimeDimHelper.GetIdFromName(povTimeName)))

' Calculate prior year POV time dynamically
Dim priorYearPOV As String = GetPriorYearTime(api, povTimeName)

' Retrieve PSVMPRC and PY PSVMREV values for the current POV and prior year POV
Dim PSVMPRC_Current As Double = api.Data.GetDataCell("A#PSVMPRC:T#" & povTimeName).CellAmount
Dim PSVMREV_PriorYear As Double = api.Data.GetDataCell("A#PSVMREV:T#" & priorYearPOV).CellAmount

' Calculate PSVMPRC%
Dim PSVMPRC_Percentage As Double = If(PSVMREV_PriorYear <> 0, PSVMPRC_Current / PSVMREV_PriorYear, 0)

' Set the calculated PSVMPRC% to the appropriate member
api.Data.SetDataCell("A#PSVMPRC%:T#" & povTimeName, PSVMPRC_Percentage, False)

Return PSVMPRC_Percentage
End If

Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function

Private Function GetPriorYearTime(ByVal api As Object, ByVal povTimeName As String) As String
' Extract the prior year time dynamically based on POV time
Dim povYear As Integer = TimeDimHelper.GetSubComponentsFromName(povTimeName).Year
Dim priorYear As Integer = povYear - 1
Dim priorYearPOV As String

If povTimeName.Length = 4 Then
' Year case: return prior year name
priorYearPOV = priorYear.ToString()
ElseIf povTimeName.Substring(4, 1).XFEqualsIgnoreCase("M") Then
' Month case: return prior year month
Dim povMonthNum As String = TimeDimHelper.GetSubComponentsFromName(povTimeName).Month.XFToString
priorYearPOV = String.Concat(priorYear.ToString(), "M", povMonthNum)
ElseIf povTimeName.Substring(4, 1).XFEqualsIgnoreCase("Q") Then
' Quarter case: return prior year quarter
Dim povQuarterNum As String = TimeDimHelper.GetSubComponentsFromName(povTimeName).Quarter.XFToString
priorYearPOV = String.Concat(priorYear.ToString(), "Q", povQuarterNum)
ElseIf povTimeName.Substring(4, 1).XFEqualsIgnoreCase("H") Then
' Half year case: return prior year half year
Dim povHalfYearNum As String = TimeDimHelper.GetSubComponentsFromName(povTimeName).HalfYear.XFToString
priorYearPOV = String.Concat(priorYear.ToString(), "H", povHalfYearNum)
Else
' Default to prior year
priorYearPOV = priorYear.ToString()
End If

Return priorYearPOV
End Function
End Class
End Namespace

I am getting the below error while compiling. Any suggestion would be appreciated?

Unable to compile formula.

1) Error at line 87: Statement cannot appear outside of a method body.

2) Error at line 88: Statement cannot appear outside of a method body.

3) Error at line 89: Statement cannot appear outside of a method body.

4) Error at line 90: Statement cannot appear outside of a method body.

5) Error at line 91: 'End Try' must be preceded by a matching 'Try'.

6) Error at line 92: Statement cannot appear outside of a method body.

7) Error at line 93: 'End Function' must be preceded by a matching 'Function'.

😎 Error at line 101: 'End Class' must be preceded by a matching 'Class'.

9) Error at line 102: 'End Namespace' must be preceded by a matching 'Namespace'.

My requirement is to perform the below actual calculation:

PSVMPRC % = PSVMPRC / PY PSVMREV.

My PY is based on PoV. for example: if user choose current month as MAY 2024 then PY is MAY 2023, if the user choose FY2024 YTD then PY is FY2023 YTD, if the user choose current year QTD then PY QTD. Can one please suggest whether I am doing correct approach or not and suggestions?

jmohl · Monday

No worries. The error message is due to the business rule type. You are calculating via a Finance business rule; however, the provided code snippet is for an XFBR. You will obviously need to update this code to fit your specific application, but hopefully this is a helpful start. In addition, the sample code is a normal "Calculate" business rule, so it will need to be attached to your cube for testing purposes. You can easily update it to a custom calculate if called from a data management sequence.

As a disclaimer, I have not tested this code, but can confirm it compiles when pasted as a finance business rule. Ensure your business rule name matches the namespace object on row 18. Hopefully this helps!

One last note - you could potentially try a dynamic calculation (via UD8 formula) and call it from your intended cube view/reports. The ideal approach ultimately depends on your specific circumstance/requirements; there are several different ways you could solve for this.

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine

Namespace OneStream.BusinessRule.Finance.SampleFinanceRule
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
			Try
				Select Case api.FunctionType
					
						
					Case Is = FinanceFunctionType.Calculate
						
						'Limit calculation scope to base entities and local currency
						Dim isBase As Boolean = Not api.Entity.HasChildren
						Dim isLocal As Boolean = api.Cons.IsLocalCurrencyForEntity
						
						If isBase AndAlso isLocal Then
						
							'Part 1: Determing the appropriate ending time
							
							'Store the POV time in a variable for later reference
							Dim povTimeName As String = api.Pov.Time.Name
								
							'Extract the current and prior year names from the POV time
							Dim povYearName As String = TimeDimHelper.GetYearFromId(TimeDimHelper.GetIdFromName(povTimeName))
							Dim priorYearName As String = TimeDimHelper.GetYearFromId(TimeDimHelper.GetPriorYearPeriodId(TimeDimHelper.GetIdFromName(povTimeName)))
							
							'Create empty string to store the prior time
							Dim priorTimeName As String = String.Empty
							
							If povTimeName.Length = 4 'Year
									
								'Return the same month for the prior year
								priorTimeName = priorYearName
								
							Else If povTimeName.Substring(4, 1).XFEqualsIgnoreCase("M") 'Months
									
								'Extract current month from POV time and convert to a string 
								Dim povMonthNum As String = TimeDimHelper.GetSubComponentsFromName(povTimeName).Month.XFToString
								
								'Return the same month for the prior year
								priorTimeName = String.Concat(priorYearName,"M", povMonthNum)
								
							Else If povTimeName.Substring(4, 1).XFEqualsIgnoreCase("Q") 'Quarters
									
								'Extract current quarter from POV time and convert to a string 
								Dim povQuarterNum As String = TimeDimHelper.GetSubComponentsFromName(povTimeName).Quarter.XFToString
								
								'Return the same month for the prior year
								priorTimeName = String.Concat(priorYearName,"Q", povQuarterNum)
																
							Else If povTimeName.Substring(4, 1).XFEqualsIgnoreCase("H") 'Half Year
									
								'Extract current quarter from POV time and convert to a string 
								Dim povHalfYearNum As String = TimeDimHelper.GetSubComponentsFromName(povTimeName).HalfYear.XFToString
								
								'Return the same month for the prior year
								priorTimeName = String.Concat(priorYearName,"H", povHalfYearNum)
						
							End If						
						
							'Part 2: Calculate your results
							'Define source and target intersections to limit calculation scope
							Dim strTops As String = ":O#Top" 'Add dimensions that will remain constant
							Dim strNones As String = ":O#Import"
							
							'Limit calculation to only run for specific accounts
							Dim accountFilter As String = "A#ParentAccount.Base"
						
							'Finally, calculate the percentage account - note string interpolation for source/target intersection variables
							api.Data.Calculate($"A#PSVMPRC_Pct{strNones} = RemoveZeros(A#PSVMPRC:T#{priorTimeName}{strTops} / A#PYPSVMREV:T#{priorTimeName}{strTops}", accountFilter,,,,,,,,,,,,,, False)
						
						End If
						
				End Select

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

jmohl · Monday

The errors that you are seeing reflect the rule being used as Finance Business Rule instead of an XFBR, I recently replied to this post with an updated version of the business rule (as a finance business rule); however, my response was "rejected by a moderator" (which is interesting). You can try updating the initial code snipped that was provided to be a finance business rule. Best of luck,

Rams_2022 · Tuesday

Hi @community-api Team, any suggestions how to automate this.

PSVMPRC % = PSVMPRC / PY PSVMREV.

My PY should be based on PoV selected for Time. For example, if user can select current year as FY2024 then PY is FY2023 or If user choose MAY 2024, then PY is MAY 2023 or if user choose Q2 2024 then the PY is Q2 2023. Is this achievable if yes, any guidance please?

OneStream Community

PY dynamic