07-08-2024 05:20 PM - edited 07-08-2024 06:57 PM
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.
07-09-2024 04:42 AM
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.
07-09-2024 06:18 AM
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?
07-09-2024 06:54 AM
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
07-09-2024 10:27 AM
@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
07-10-2024 03:39 AM
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.
07-10-2024 04:01 AM - edited 07-10-2024 04:06 AM
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