HI Team,
We have created a stored calc and it is working fine at Base and parent entity level, but when we are running at Top entity level and my Time is 2028, at this i am getting below error. could not able to figure it. Can you please help us resolve the issue.
Hi Cosimo,
The scenario we are using is "3YearPlanning". this Scenario will have data at Quarters and Year. and Consolidation is working at T#2026 & T#2027. we are getting error for T#2028. it seems like strange. i am attaching the code for your reference. please help us to resolving the issue.
Dim time As String = api.Pov.Time.Name
Dim povScenario As String = api.Pov.Scenario.Name
Dim povUD2 As String = api.Pov.UD2.Name
Dim povPriorTime1 As String = api.Members.GetMemberName(dimtype.Time.Id, api.Time.AddTimePeriods(api.pov.Time.MemberId, -1, True)).ToString
Dim povPriorTime2 As String = api.Members.GetMemberName(dimtype.Time.Id, api.Time.AddTimePeriods(api.pov.Time.MemberId, -2, True)).ToString
Dim povScenarioYear As String
Dim povScenarioYearPlusTwo As String
Dim povScenarioYearPlusThree As String
Dim sPOVYear As String = api.Pov.Time.Name.Substring(0,4)
Dim iMonthLessOne As Integer = api.Time.AddTimePeriods(-1, True)
Dim sMonthLessOne As String = api.Time.GetNameFromId(iMonthLessOne)
Dim StatTaxPovAVG As String = "C#Local:S#Actual:T#2017M12:V#Periodic:A#StatutoryTaxRate:F#EndBal_Input:O#Forms:I#None:U1#UND_CC:U2#CFIN001948:U3#UND_UD3:U4#UND_UD4:U5#UND_UD5:U6#UND_UD6:U7#UND_UD7:U8#StatutoryTaxRate_AVG"
Dim StatTaxPov As String = "C#Local:S#Actual:T#2017M12:V#Periodic:A#StatutoryTaxRate:F#EndBal_Input:O#Forms:I#None:U1#UND_CC:U2#CFIN001948:U3#UND_UD3:U4#UND_UD4:U5#UND_UD5:U6#UND_UD6:U7#UND_UD7:U8#UND_UD8"
Dim dStatTaxRateAVG As Decimal = 1 - api.Data.GetDataCell(StatTaxPovAVG).CellAmount
Dim dStatTaxRate As Decimal = 1 - api.Data.GetDataCell(StatTaxPov).CellAmount
Dim GSS_UnivAlloc As String = "(A#KPI_TOT_UNIV_REG:E#Total_Flex:V#Periodic:O#TOP/A#300:E#Total_Flex:V#Periodic:O#TOP)*A#300:V#Periodic:O#TOP"
Dim GSS_SegAlloc As String = "(A#KPI_TOT_ALLOC_SEG:E#Total_Flex:V#Periodic:O#TOP/A#300:E#Total_Flex:V#Periodic:O#TOP)*A#300:V#Periodic:O#TOP"
Dim GSS_BvAlloc As String = "(A#KPI_TOT_ALLOC_BU:E#Total_Flex:V#Periodic:O#TOP/A#300:E#Total_Flex:V#Periodic:O#TOP)*A#300:V#Periodic:O#TOP"
Dim Tot_Cop_Alloc As Decimal = api.data.getdatacell(GSS_UnivAlloc & "+" & GSS_SegAlloc &"+"& GSS_BvAlloc).CellAmount
'If the scenario is a 3YP scenario, extract year components
If povScenario.XFContainsIgnoreCase("3YP") Then
povScenarioYear = povScenario.Substring(2,4)
povScenarioYearPlusTwo = povScenarioYear + 2
povScenarioYearPlusThree = povScenarioYear + 3
End If
'Get Year ID
Dim iPOVYearID As Integer = api.Members.GetMemberId(dimtype.Time.Id, sPOVYear)
If api.Entity.HasChildren = False
If time.Contains("Q") Then
If povScenario.XFContainsIgnoreCase("3YP") And (iPOVYearID.ToString.XFContainsIgnoreCase(povScenarioYearPlusTwo) Or iPOVYearID.ToString.XFContainsIgnoreCase(povScenarioYearPlusThree)) Then
api.Data.ClearCalculatedData(True,True,True,True,"A#KPI_GSS_OP_IN_AFT_TAX_ROIC")
api.Data.Calculate("A#KPI_GSS_OP_IN_AFT_TAX_ROIC:V#Periodic:O#Import = ((A#KPI_GSS_OP_S:V#Periodic:O#TOP * 4 * " & dStatTaxRate & "))"
End If
Else
Dim priorScenarioBR As New OneStream.BusinessRule.Finance.PriorScenario.MainClass
Dim PriorScen As New Dictionary(Of Integer, String)
For i As Integer = 0 To 3
PriorScen.Add(i, priorScenarioBR.FindPriorScenario(si, api, args, api.Pov.Scenario, api.Pov.Time, i))
Next
Dim GSS_UnivAllocPrior1 As String = "(A#KPI_TOT_UNIV_REG:E#Total_Flex:S#" & PriorScen.Item(1) & ":T#PovPrior1:V#Periodic:O#TOP/A#300:E#Total_Flex:S#" & PriorScen.Item(1) & ":T#PovPrior1:V#Periodic:O#TOP)*A#300:S#" & PriorScen.Item(1) & ":T#PovPrior1:V#Periodic:O#TOP)"
Dim GSS_SegAllocPrior1 As String = "(A#KPI_TOT_ALLOC_SEG:E#Total_Flex:S#" & PriorScen.Item(1) & ":T#PovPrior1:V#Periodic:O#TOP/A#300:E#Total_Flex:S#" & PriorScen.Item(1) & ":T#PovPrior1:V#Periodic:O#TOP)*A#300:S#" & PriorScen.Item(1) & ":T#PovPrior1:V#Periodic:O#TOP)"
Dim GSS_BvAllocPrior1 As String = "((A#KPI_TOT_ALLOC_BU:E#Total_Flex:S#" & PriorScen.Item(1) & ":T#PovPrior1:V#Periodic:O#TOP/A#300:E#Total_Flex:S#" & PriorScen.Item(1) & ":T#PovPrior1:V#Periodic:O#TOP)*A#300:S#" & PriorScen.Item(1) & ":T#PovPrior1:V#Periodic:O#TOP))"
Dim GSS_UnivAllocPrior2 As String = "(A#KPI_TOT_UNIV_REG:E#Total_Flex:S#" & PriorScen.Item(2) & ":T#PovPrior2:V#Periodic:O#TOP/A#300:E#Total_Flex:S#" & PriorScen.Item(2) & ":T#PovPrior2:V#Periodic:O#TOP)*A#300:S#" & PriorScen.Item(2) & ":T#PovPrior2:V#Periodic:O#TOP)"
Dim GSS_SegAllocPrior2 As String = "(A#KPI_TOT_ALLOC_SEG:E#Total_Flex:S#" & PriorScen.Item(2) & ":T#PovPrior2:V#Periodic:O#TOP/A#300:E#Total_Flex:S#" & PriorScen.Item(2) & ":T#PovPrior2:V#Periodic:O#TOP)*A#300:S#" & PriorScen.Item(2) & ":T#PovPrior2:V#Periodic:O#TOP)"
Dim GSS_BvAllocPrior2 As String = "((A#KPI_TOT_ALLOC_BU:E#Total_Flex:S#" & PriorScen.Item(2) & ":T#PovPrior2:V#Periodic:O#TOP/A#300:E#Total_Flex:S#" & PriorScen.Item(2) & ":T#PovPrior2:V#Periodic:O#TOP)*A#300:S#" & PriorScen.Item(2) & ":T#PovPrior2:V#Periodic:O#TOP))"
Dim Tot_Cop_AllocPrior1 As Decimal = api.data.getdatacell(GSS_UnivAllocPrior1 & "+" & GSS_SegAllocPrior1 &"+"& GSS_BvAllocPrior1).CellAmount
Dim Tot_Cop_AllocPrior2 As Decimal = api.data.getdatacell(GSS_UnivAllocPrior2 & "+" & GSS_SegAllocPrior2 &"+"& GSS_BvAllocPrior2).CellAmount
api.Data.Calculate("A#KPI_GSS_OP_IN_AFT_TAX_ROIC:V#Periodic:O#Import = ((A#KPI_GSS_OP_S:T#Pov:V#Periodic:O#Top + A#KPI_GSS_OP_S:T#PovPrior1:V#Periodic:S#" & PriorScen.Item(1) & ":O#Top + A#KPI_GSS_OP_S:T#PovPrior2:V#Periodic:S#" & PriorScen.Item(2) &":O#Top) *4 *" & dStatTaxRate & ")")
End If
ElseIf (api.Pov.Entity.Name.XFContainsIgnoreCase("101113") Or api.Pov.Entity.Name.XFContainsIgnoreCase("103015") Or api.Pov.Entity.Name.XFContainsIgnoreCase("101115") _
Or api.Pov.Entity.Name.XFContainsIgnoreCase("100832")) And api.Cons.IsForeignCurrencyForEntity Then
If time.Contains("Q") Then
If povScenario.XFContainsIgnoreCase("3YP") And (iPOVYearID.ToString.XFContainsIgnoreCase(povScenarioYearPlusTwo) Or iPOVYearID.ToString.XFContainsIgnoreCase(povScenarioYearPlusThree)) Then
api.Data.Calculate("A#KPI_GSS_OP_IN_AFT_TAX_ROIC:V#Periodic:O#Import = ((A#KPI_GSS_OP_S:V#Periodic:O#TOP * 4 * " & dStatTaxRateAVG & ")")
End If
Else
Dim priorScenarioBR As New OneStream.BusinessRule.Finance.PriorScenario.MainClass
Dim PriorScen As New Dictionary(Of Integer, String)
For i As Integer = 0 To 3
PriorScen.Add(i, priorScenarioBR.FindPriorScenario(si, api, args, api.Pov.Scenario, api.Pov.Time, i))
Next
End If
End If
api.Data.Calculate("V#Periodic:O#Import = Eval(V#Periodic:O#Top)","A#KPI_GSS_OP_IN_AFT_TAX_ROIC",,,,,,,,,,,,AddressOf OnEvalDataBuffer,,True)
Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)
Dim filteredCells As New Dictionary(Of DataBufferCellPk, DataBufferCell)
For Each cell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values
If (cell.CellStatus.IsDerivedData) Then
'api.LogMessage(cell.DataBufferCellPk.GetMemberScript(api))
filteredCells(cell.DataBufferCellPk) = cell * 0
End If
Next
' Assign filtered list Of dataCells To result used In api.Data.Calculate Function.
eventArgs.DataBufferResult.DataBufferCells = filteredCells
End Sub
My best guess is that you cannot write data to T#2028 assuming that your scenario has months under it. Recheck the formula used on the account. The target period should be a base period i.e. T#2028M12.
