01-04-2021 07:28 AM - last edited on 05-02-2023 10:48 AM by JackLacava
Hello,
We have a report that needs to transition between the Budget scenario and Actual scenario as we move through the year. Ideally the user will select a period, and depending if the period in the displayed column is current or in the past it will show Actual, else it will show Budget. I was asked to complete this without creating a new scenario.
I tried the below, but didn't have any success.
Public Function PastBudgetCurrFutActual(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As String
Try
Dim currTime = args.NameValuePairs.XFGetValue("CurrentTime")
Dim timeId As Integer = BRApi.Finance.Members.GetMemberId(si, DimTypeId.Time, currTime)
Dim colTime = args.NameValuePairs.XFGetValue("ColTime")
Dim timeId2 As Integer = BRApi.Finance.Members.GetMemberId(si, DimTypeId.Time, colTime)
If timeId >= TimeId2
Return "S#Actual"
Else
Return "S#Budget"
End If
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Any assistance would be appreciated. Thanks.
10-18-2021 07:05 AM - edited 10-18-2021 07:29 AM
This won't work with this way with a xfbr function, if you are using dynamic time lists like T#2021.base.
Instead, you can create an ud8 function:
Dim currTime As String = BRApi.Utilities.GetApplicationProperties(si).GlobalTime
Dim timeId As Integer = BRApi.Finance.Members.GetMemberId(si, DimTypeId.Time, currTime)
Dim timeId2 As Integer = api.Pov.Time.MemberId
Dim newDataCellPk As DataCellPk = api.Pov.GetDataCellPk()
newDataCellPk.UD8Id = DimConstants.None
If timeId >= TimeId2
newDataCellPk.ScenarioId = api.Members.GetMemberId(dimtypeid.Scenario, "Actual")
Else
newDataCellPk.ScenarioId = api.Members.GetMemberId(dimtypeid.Scenario, "Budget")
End If
Return api.Data.GetDataCell(newDataCellPk)
If you want not only the data to change, but the column header as well, you can do it with a xfbr function creating a comma separated list of povs:
Public Function PastBudgetCurrFutActual(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As String
Try
Dim curTime = args.NameValuePairs.XFGetValue("CurTime")
Dim curTimeMember As Member = BRApi.Finance.Members.GetMember(si, DimTypeId.Time, curTime)
Dim colTimeQuery = args.NameValuePairs.XFGetValue("ColTimeQuery")
Dim colTimeMemberList As list (Of MemberInfo) = BRApi.Finance.Members.GetMembersUsingFilter(si, brapi.Finance.Dim.GetDimPk(si, "Time"), colTimeQuery,False)
Dim povs As New List(Of String)
For Each colTimeMember As MemberInfo In colTimeMemberList
If curTimeMember.MemberId >= colTimeMember.Member.MemberId
povs.Add($"S#Actual:T#{colTimeMember.member.Name}")
Else
povs.Add($"S#Budget:T#{colTimeMember.member.Name}")
End If
Next
Return String.Join(",", povs)
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
You call it this way: xfbr(<business rule name>, PastBudgetCurrFutActual, CurTime=2021M10, ColTimeQuery=[T#|PovYear|.base])
It will create a list like this one:
S#Actual:T2021M1, S#Actual:T2021M1, .. ,S#Actual:T2021M10,S#Budget:T2021M11,S#Budget:T2021M12
I didn't test both functions, but both concepts will work.
10-18-2021 07:47 AM - edited 10-18-2021 07:48 AM
Why simple if you can solve it complicated?
Here is the simple solution: T#POV.AllPriorInYear:S#Actual, T#POV:S#Actual, T#POV.AllNextInYear:S#Budget
you can find it here:
The other concepts might be helpful in more complicated situations.
12-14-2021 06:47 PM
Just piggy backing off of Christian's last comment/solution, you can also have T#POV.AllPriorInYearInclusive:S#Actual, so could skip the T#POV:S#Actual.