07-01-2024 04:54 PM - edited 07-01-2024 05:08 PM
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")
07-01-2024 05:44 PM
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.
07-01-2024 05:54 PM
@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
07-01-2024 06:47 PM - edited 07-01-2024 06:54 PM
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?
07-01-2024 08:18 PM - edited 07-01-2024 08:20 PM
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
07-01-2024 08:32 PM
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,
07-02-2024 10:10 AM
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?
07-17-2024 12:11 PM
Any suggestions/help would appreciated?