OneStream’s specialty planning apps are based on the principle that every “thing” doesn’t need to go to the cube. If you think about it, it is a pretty neat concept.
Do the lowest level planning (People, Asset, Sales, et al.) in Specialty application and send the higher-level data to the cube if you want to look at the lowest level drill-down to see the details.
When you are working on Specialty planning, and when you are trying to migrate from Hyperion Planning, you might need some functions to convert the application. Most Hyperion Planning implementations use YTD, Prior, and Cross dimension operators in calculations. Specialty planning gives you a lot of flexibility with in-built functions (rules). It also provides a function that you can use to create your custom functions.
The stock function that we are going to look at is called SumPlanCustom, and we are going to use this function to create a YTD, QTD, Prior, and a give me a calculated result function.
Keep in mind that you need to do this only if your Specialty Planning calculation needs YTD or any of the above functions to derive another calculation. By default, OS cube handles these without any effort.
Setup for the functions
I’m in GolfStream (Sample OneStream application) People Planning application.
I went ahead and added an Activity Type called CalcOnly to capture YTD, QTD, et al.
The next step is to add the accounts to capture the time series values.
Since we are going to use the Stock helper function, you need to add a reference to that rule (in my case it is PLP_SolutionHelper).
We are now ready to create the functions. You can refer to my other post on adding references (external and internal) to learn more about this.
YTD function
I’m pretty sure y’all know what a YTD function is. If you don’t, then it is as follows.
- Jan YTD = Jan
- Feb YTD = Jan + Feb
- Apr YTD = Jan + Feb + Mar + Apr
OneStream stores periods as numbers in Specialty Planning applications. Next year Jan is 13, Feb is 14 and so on. In-order to calculate YTD we need to OneStream stores periods as numbers in Specialty Planning applications. Next year Jan is 13, Feb is 14 and so on. In-order to calculate YTD, we need to find out when the year jump is happening. The following code makes use of Mod function and \ (it is not the regular divide /), which gives the integer after division.
The idea here is to find Jan, that is 1, 13, 25, 37, and so on.
Private Function GetYTDValue(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal args As DashboardStringFunctionArgs) As String
Try
Dim returnValue As String
Dim regID As String = args.NameValuePairs.XFGetValue("RegisterID")
Dim regInstance As String = args.NameValuePairs.XFGetValue("RegisterInstance")
Dim cleanRegID As String =regID.Replace("'", String.Empty)
Dim account As String = args.NameValuePairs.XFGetValue("Account")
Dim period As Integer = args.NameValuePairs.XFGetValue("Period")
Dim fieldToSum As String = args.NameValuePairs.XFGetValue("Field")
Dim criteria As String = "Account = '" & account & "' And RegisterID='" & cleanRegID & "_" & regInstance & "'"
If period <=12
criteria = criteria & " And Period <= " & period
Else
' YTD should start with first months like 13, 25, 37 and so on
' check to see if the mod is coming as zero, these months are the end of year
' if mod is zero, get int of period/12 - 1 * 12 + 1 will give start month of year
' e.g in case of 36 the start month will be ((36\12) - 1) * 12 + 1 = (3-1) * 12 + 1 = 25
If period Mod 12 = 0
Dim startPeriod As Integer = ((period\12) - 1) * 12 + 1
criteria = criteria & " And Period >= " & startPeriod & " And Period <= " & period
Else
' mod is not zero get the get period\12 * 12 + 1 will give start month of year
' e.g in case of 39 the start month will be (39\12) * 12 + 1 = 3 * 12 + 1 = 37
Dim startPeriod As Integer = (period\12) * 12 + 1
criteria = criteria & " And Period >= " & startPeriod & " And Period <= " & period
End If
End If
'Get the Cache Item
Dim cacheItemHelper As OneStream.BusinessRule.DashboardExtender.PLP_SolutionHelper.RegisterCacheItem = globals.GetObject(cleanRegID)
If Not cacheItemHelper Is Nothing Then
returnValue = cacheItemHelper.SumPlanCustom(si, criteria,fieldToSum).XFToStringForFormula
Else
BRApi.ErrorLog.LogMessage(si, StringHelper.FormatMessage(m_MsgCacheItemNotFound, regID))
End If
Return returnValue
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
You can copy this function and add it to a Dashboard XFBR String rule. To use the above function in a calculation, add the following code in the specialty rule allocation methods.
XFBR(PeoplePlanning_ParamHelper, GetYTDValue, Account=[Salary_Exp], RegisterID=[|RegisterID|], RegisterInstance=[|RegisterIDInstance|], Period=|CalcPer|, Field=Amount)
Here are the results, below given is the salary details of and employee.
Here are the YTD results.
QTD Function
QTD is similar to the YTD function, with the exception that every first month in the quarter, QTD is the month itself.
- Jan QTD = Jan
- Feb QTD = Jan + Feb
- Apr QTD = Apr
We are using Mod function to find out whether it is the first, second, or third month.
Private Function GetQTDValue(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal args As DashboardStringFunctionArgs) As String
Try
Dim returnValue As String
Dim regID As String = args.NameValuePairs.XFGetValue("RegisterID")
Dim regInstance As String = args.NameValuePairs.XFGetValue("RegisterInstance")
Dim cleanRegID As String =regID.Replace("'", String.Empty)
Dim account As String = args.NameValuePairs.XFGetValue("Account")
Dim period As Integer = args.NameValuePairs.XFGetValue("Period")
Dim fieldToSum As String = args.NameValuePairs.XFGetValue("Field")
Dim criteria As String = "Account = '" & account & "' And RegisterID='" & cleanRegID & "_" & regInstance & "'"
If period Mod 3 = 0' means this is the last month of quarter 3, 6, 9, 12 and so on
criteria = criteria & " And Period >= " & period - 2 & " And Period <= " & period
Else If period Mod 3 = 1 ' means this is the first month of the quarter 1, 4, 7, 10 and so on
criteria = criteria & " And Period = " & period
Else If period Mod 3 = 2 ' means this is the second month of the quarter 2, 5, 8, 11 and so on
criteria = criteria & " And Period >= " & period - 1 & " And Period <= " & period
End If
'Get the Cache Item
Dim cacheItemHelper As OneStream.BusinessRule.DashboardExtender.PLP_SolutionHelper.RegisterCacheItem = globals.GetObject(cleanRegID)
If Not cacheItemHelper Is Nothing Then
returnValue = cacheItemHelper.SumPlanCustom(si, criteria,fieldToSum).XFToStringForFormula
Else
BRApi.ErrorLog.LogMessage(si, StringHelper.FormatMessage(m_MsgCacheItemNotFound, regID))
End If
Return returnValue
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Alloc method call for QTD
XFBR(PeoplePlanning_ParamHelper, GetQTDValue, Account=[Salary_Exp], RegisterID=[|RegisterID|], RegisterInstance=[|RegisterIDInstance|], Period=|CalcPer|, Field=Amount)
Prior Function
Prior function, as the name implies get the value of a prior month. The good thing is you can even move across years when you are in OS specialty planning with ease. (I’m not showing that in my calc. However, I’ve added pointers in the comments)
Private Function GetPriorValue(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal args As DashboardStringFunctionArgs) As String
Try
Dim returnValue As String
Dim regID As String = args.NameValuePairs.XFGetValue("RegisterID")
Dim regInstance As String = args.NameValuePairs.XFGetValue("RegisterInstance")
Dim cleanRegID As String =regID.Replace("'", String.Empty)
Dim account As String = args.NameValuePairs.XFGetValue("Account")
Dim period As Integer = args.NameValuePairs.XFGetValue("Period")
Dim fieldToSum As String = args.NameValuePairs.XFGetValue("Field")
Dim criteria As String = "Account = '" & account & "' And RegisterID='" & cleanRegID & "_" & regInstance & "'"
If period Mod 12 = 1' means this is the first month of the year, you can decide whether to go back or start as zero.
'Since you are in register this would mean you can use WFTimeName and/or WFScenarioName to do this prior
Else
criteria = criteria & " And Period = " & period - 1
End If
'Get the Cache Item
Dim cacheItemHelper As OneStream.BusinessRule.DashboardExtender.PLP_SolutionHelper.RegisterCacheItem = globals.GetObject(cleanRegID)
If Not cacheItemHelper Is Nothing Then
returnValue = cacheItemHelper.SumPlanCustom(si, criteria,fieldToSum).XFToStringForFormula
Else
BRApi.ErrorLog.LogMessage(si, StringHelper.FormatMessage(m_MsgCacheItemNotFound, regID))
End If
Return returnValue
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Alloc method call for Prior
XFBR(PeoplePlanning_ParamHelper, GetPriorValue, Account=[Salary_Exp], RegisterID=[|RegisterID|], RegisterInstance=[|RegisterIDInstance|], Period=|CalcPer|, Field=Amount)
CalcValue Function
Think of this like a cross-dimension operator, you are just looking for a previously calculated account.
For this example, I’m trying to get the values of FICA and FUTA expenses to find what are my Total Taxes.
Private Function GetCalcValue(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal args As DashboardStringFunctionArgs) As String
Try
Dim returnValue As String
Dim regID As String = args.NameValuePairs.XFGetValue("RegisterID")
Dim regInstance As String = args.NameValuePairs.XFGetValue("RegisterInstance")
Dim cleanRegID As String =regID.Replace("'", String.Empty)
Dim account As String = args.NameValuePairs.XFGetValue("Account")
Dim period As Integer = args.NameValuePairs.XFGetValue("Period")
Dim fieldToSum As String = args.NameValuePairs.XFGetValue("Field")
Dim criteria As String = "Account = '" & account & "' And RegisterID='" & cleanRegID & "_" & regInstance & "' And Period = " & period
'Get the Cache Item
Dim cacheItemHelper As OneStream.BusinessRule.DashboardExtender.PLP_SolutionHelper.RegisterCacheItem = globals.GetObject(cleanRegID)
If Not cacheItemHelper Is Nothing Then
returnValue = cacheItemHelper.SumPlanCustom(si, criteria,fieldToSum).XFToStringForFormula
Else
BRApi.ErrorLog.LogMessage(si, StringHelper.FormatMessage(m_MsgCacheItemNotFound, regID))
End If
Return returnValue
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Alloc method call for CalcValue
XFBR(PeoplePlanning_ParamHelper, GetCalcValue, Account=[SalaryFica_Exp], RegisterID=[|RegisterID|], RegisterInstance=[|RegisterIDInstance|], Period=|CalcPer|, Field=Amount) + XFBR(PeoplePlanning_ParamHelper, GetCalcValue, Account=[SalaryFuta_Exp], RegisterID=[|RegisterID|], RegisterInstance=[|RegisterIDInstance|], Period=|CalcPer|, Field=Amount)
Now a bonus function, since I was looking at FICA and FUTA, I found that they both are having same Activity Type (103_Taxes) I thought instead of getting the two accounts seperately and adding them up, why not just add the activity type itself.
Hence the GetTotalActivity function was born.
GetTotalActivity Function
Private Function GetTotalActivityValue(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal args As DashboardStringFunctionArgs) As String
Try
Dim returnValue As String
Dim regID As String = args.NameValuePairs.XFGetValue("RegisterID")
Dim regInstance As String = args.NameValuePairs.XFGetValue("RegisterInstance")
Dim cleanRegID As String =regID.Replace("'", String.Empty)
Dim activityType As String = args.NameValuePairs.XFGetValue("Activity")
Dim period As Integer = args.NameValuePairs.XFGetValue("Period")
Dim fieldToSum As String = args.NameValuePairs.XFGetValue("Field")
Dim criteria As String = "RegisterID='" & cleanRegID & "_" & regInstance & "' And Period = " & period & " And ActivityType='" & activityType & "'"
'Get the Cache Item
Dim cacheItemHelper As OneStream.BusinessRule.DashboardExtender.PLP_SolutionHelper.RegisterCacheItem = globals.GetObject(cleanRegID)
If Not cacheItemHelper Is Nothing Then
returnValue = cacheItemHelper.SumPlanCustom(si, criteria,fieldToSum).XFToStringForFormula
Else
BRApi.ErrorLog.LogMessage(si, StringHelper.FormatMessage(m_MsgCacheItemNotFound, regID))
End If
Return returnValue
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Alloc method call for ActivityTotal
XFBR(PeoplePlanning_ParamHelper, GetTotalActivityValue, Activity=[103_Taxes], RegisterID=[|RegisterID|], RegisterInstance=[|RegisterIDInstance|], Period=|CalcPer|, Field=Amount)