Force consolidation error

[Rams_2022]

Hi OS Experts:

While I am running the ForceConsolidation in cube view, I am getting the below error message. Are there any possibilities of this error:

Error calculating member 'EndBal_Calc' in dimension 'Flows'. Unable to execute formula for member 'EndBal_Calc'. Invalid script 'A#NET:F#EndBal:V#YTD:O#Top:I#Top:U1#USACAP:U2#TOT_DEPT:U3#TOT_PROJ:U8#None:E#' near 'E#

I am checking the EndBal_Calc in flow dimension the calculation has been working from many years correctly but this time the strange issue. any suggestion. I see the similar error for the different entities' different runs.

[MarcusH]

Without the member formula it is difficult to say precisely what the problem is but I would guess that you are using a variable to hold the Entity member name and that variable is empty. If that is not the case, post the member formula so we have a better idea of where the problem might be.

[Rams_2022]

Hi @MarcusH , yes you are correct. in the member formula, we have variable that concatenates the entity, you can see the below. while execute the api.data.calculate the error is getting trigged during forececonsolidation.

Dim currMonthNum As Integer = api.Time.GetPeriodNumFromId(api.Pov.Time.MemberId)
Dim strSourceEntity As String = api.Entity.Text(2)

Dim SourceUDs As String = "V#YTD:O#Top:I#Top:U1#USACAP:U2#TOT_DEPT:U3#TOT_PROJ:U8#None:E#" & strSourceEntity

api.Data.Calculate("A#TP_FP:F#EndBal_Calc:" & TargetUDs &" = RemoveNoData(A#PDE_NET:F#EndBal:" & SourceUDs &")-RemoveNoData(A#PAS_WO:F#EndBal:" & SourceUDs &")-RemoveNoData(A#ACP_EU_TEM_CR:F#EndBal:" & SourceUDs &")")

what is the possibilities of the resolutions?

[MarcusH]

One possibility is to set the EndBal_Calc to 0 if there is no value on Text2 for the Entity:

Dim currMonthNum As Integer = api.Time.GetPeriodNumFromId(api.Pov.Time.MemberId)
Dim strSourceEntity As String = api.Entity.Text(2)

If String.IsNullOrWhiteSpace(strSourceEntity) Then
    api.Data.Calculate("A#TP_FP:F#EndBal_Calc:" & TargetUDs & " = 0"
Else
    Dim SourceUDs As String = "V#YTD:O#Top:I#Top:U1#USACAP:U2#TOT_DEPT:U3#TOT_PROJ:U8#None:E#" & strSourceEntity

    api.Data.Calculate("A#TP_FP:F#EndBal_Calc:" & TargetUDs &" = RemoveNoData(A#PDE_NET:F#EndBal:" & SourceUDs &")-RemoveNoData(A#PAS_WO:F#EndBal:" & SourceUDs &")-RemoveNoData(A#ACP_EU_TEM_CR:F#EndBal:" & SourceUDs &")")
End If

[Rams_2022]

@MarcusH thanks for recommendation. Just to let you know, user/I started getting this error since yesterday afternoon. we never experienced this issue as of Yesterday noon. we never changed any code or metadata updates that I verified. So, I synced the prod model in Development environment and ran the ForceConsolidation and it is running without any issues. I am stuck here to find out ghost. any leads suggestion please

[KarlT]

Have you used the Grid view in the dimension library to check the Text 2 property for all entities? As Marcus suggests, this being missing for an entity seems the most likely cause. It could also be that the text property has been added as time dependant and is not valid for the scenario/time you're consolidating in your cube view.

[FredLucas]

I also agree that the issue is likely to be on the Text 2 property so would start by doing what @KarlT has suggested.

If you still cannot figure out what is causing it I would suggest to do some simple debugging.

Adding the following code to under you Catch Exception part of the rule should enable you to identify what target entity, source entity (given by the Text2 property) and Time is causing it to fail by checking the error log:

Catch ex As Exception
  'debug entity, and time causing the exception
  api.LogMessage($"Target POV Entity: {api.Pov.Entity.Name}; SourceEntity: {api.Entity.Text(2)}; Time: {api.Pov.Time.Name}")
  Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try