Rule to Pick Scenario based on comparison between POV time and Time set for column in cubeview

[OSAdmin]

Originally posted by Thomas Regehr

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.

[ChristianW]

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.

[ChristianW]

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.

[PKiernan]

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.