Skip to main content
July 1, 2024

PY dynamic

  • July 1, 2024
  • 3 replies
  • 0 views

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")

3 replies

July 1, 2024
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.

jmohl_0-1719870023145.png

 

 

Rams_2022Author
July 1, 2024

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_2022Author
July 1, 2024

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?

 

Rams_2022Author
July 2, 2024

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?

Rams_2022Author
July 17, 2024

Any suggestions/help would appreciated?