Forum Discussion

Rams_2022's avatar
Rams_2022
New Contributor II
6 months ago

Force consolidation error

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.

XFBR
  • MarcusH's avatar
    MarcusH
    Contributor III
    6 months ago

    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's avatar
      Rams_2022
      New Contributor II
      6 months ago

      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's avatar
        MarcusH
        Contributor III
        6 months ago

        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's avatar
    Rams_2022
    New Contributor II
    6 months ago

    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's avatar
      KarlT
      Contributor III
      6 months ago

      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's avatar
      FredLucas
      Contributor III
      6 months ago

      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