03-09-2022 11:16 AM
How can I get xfgetcell formula to not pull in the next month as a negative? i am making a YTD file and currently have Jan and Feb number but march pulls in the YTD amount but negative and causes the ytd amount to be $0.
03-09-2022 02:47 PM
We use UD8 as a Reporting Helpers dimension and have a Scaling hierarchy to scale the data in whole numbers, thousands, or millions. If U8#None was selected, we would see the inverse number as you're describing. Since the members in my screen shot below are DynamicCalc's, we do not see those inverse values. My second screen shot shows how this works in a quick view.
04-19-2022 11:16 AM
Hi aricgresko,
What is the formula you use to dynamic calc the Thousands ?
Regards
Mark
04-19-2022 01:29 PM
Here's the formula on the ScaledThousands UD8 member in my original screen shot
If viewMember.IsAnnotationType = False Then
If api.Members.HasChildren(objTimeDimPk, api.Pov.Time.MemberID) And api.Data.GetDataCell("U8#None").CellStatus.IsRealOrDerivedData = True And objAccountType <> AccountType.Flow And objAccountType <> AccountType.Balance And objAccountType <> AccountType.DynamicCalc And objAccountType <> AccountType.BalanceRecurring Then
Return api.Data.GetDataCell("(U8#None) /1000").CellAmount
Else
Dim currTime As String = api.Pov.Time.Name
Dim currTimeID As Integer = api.Pov.Time.MemberId
Dim currPeriod As Integer = api.Time.GetPeriodNumFromId(currTimeID)
Dim currYear As Integer = api.Time.GetYearFromId(currTimeID)
Dim globalTime As String = BRApi.Workflow.General.GetGlobalTime(si)
Dim globalTimeID As Integer = api.Time.GetIdFromName(globalTime)
Dim globalPeriod As Integer = api.Time.GetPeriodNumFromId(globalTimeID)
Dim globalYear As Integer = api.Time.GetYearFromId(globalTimeID)
If currYear = globalYear And currPeriod > globalPeriod Then
If api.Data.GetDataCell("U8#None").CellStatus.IsDerivedData = False Then
If api.Data.GetDataCell("U8#None").CellStatus.IsNoData = False Then
If (objAccountType <> AccountType.Flow And objAccountType <> AccountType.Balance And objAccountType <> AccountType.DynamicCalc And objAccountType <> AccountType.BalanceRecurring) Then
Return api.Data.GetDataCell("(U8#None) /1000").CellAmount
Else
Return api.Data.GetDataCell("U8#None")
End If
Else
Return Nothing
End If
End If
Else
If api.Data.GetDataCell("U8#None").CellStatus.IsNoData = False Then
If (objAccountType <> AccountType.Flow And objAccountType <> AccountType.Balance And objAccountType <> AccountType.DynamicCalc And objAccountType <> AccountType.BalanceRecurring) Then
Return api.Data.GetDataCell("(U8#None) /1000").CellAmount
Else
Return api.Data.GetDataCell("U8#None")
End If
Else
Return Nothing
End If
End If
End If
Else
Return Nothing
End If
04-20-2022 07:43 AM
Hi aricgresko,
I think some declarations are missing as it's not compiling correctly. I have added these
Dim MemberId As Integer
Dim objViewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberId)
Dim objAccountType = api.Account.GetAccountType(MemberId)
But am not sure of the declaration necessary for
objTimeDimPk
Can you see if there is any additional code to that which you posted above.
Regards
Mark
04-20-2022 09:19 AM
Sorry - I did miss a few rows of syntax.
Dim viewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId)
Dim objaccountid As Integer = api.Pov.Account.MemberId
Dim objAccountType As AccountType = api.Account.GetAccountType(objaccountid)
Dim objTimeDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, "Time")
04-20-2022 09:20 AM
Dim viewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId)
Dim objaccountid As Integer = api.Pov.Account.MemberId
Dim objAccountType As AccountType = api.Account.GetAccountType(objaccountid)
Dim objTimeDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, "Time")
If viewMember.IsAnnotationType = False Then
If api.Members.HasChildren(objTimeDimPk, api.Pov.Time.MemberID) And api.Data.GetDataCell("U8#None").CellStatus.IsRealOrDerivedData = True And objAccountType <> AccountType.Flow And objAccountType <> AccountType.Balance And objAccountType <> AccountType.DynamicCalc And objAccountType <> AccountType.BalanceRecurring Then
Return api.Data.GetDataCell("(U8#None) /1000").CellAmount
Else
Dim currTime As String = api.Pov.Time.Name
Dim currTimeID As Integer = api.Pov.Time.MemberId
Dim currPeriod As Integer = api.Time.GetPeriodNumFromId(currTimeID)
Dim currYear As Integer = api.Time.GetYearFromId(currTimeID)
Dim globalTime As String = BRApi.Workflow.General.GetGlobalTime(si)
Dim globalTimeID As Integer = api.Time.GetIdFromName(globalTime)
Dim globalPeriod As Integer = api.Time.GetPeriodNumFromId(globalTimeID)
Dim globalYear As Integer = api.Time.GetYearFromId(globalTimeID)
If currYear = globalYear And currPeriod > globalPeriod Then
If api.Data.GetDataCell("U8#None").CellStatus.IsDerivedData = False Then
If api.Data.GetDataCell("U8#None").CellStatus.IsNoData = False Then
If (objAccountType <> AccountType.Flow And objAccountType <> AccountType.Balance And objAccountType <> AccountType.DynamicCalc And objAccountType <> AccountType.BalanceRecurring) Then
Return api.Data.GetDataCell("(U8#None) /1000").CellAmount
Else
Return api.Data.GetDataCell("U8#None")
End If
Else
Return Nothing
End If
End If
Else
If api.Data.GetDataCell("U8#None").CellStatus.IsNoData = False Then
If (objAccountType <> AccountType.Flow And objAccountType <> AccountType.Balance And objAccountType <> AccountType.DynamicCalc And objAccountType <> AccountType.BalanceRecurring) Then
Return api.Data.GetDataCell("(U8#None) /1000").CellAmount
Else
Return api.Data.GetDataCell("U8#None")
End If
Else
Return Nothing
End If
End If
End If
Else
Return Nothing
End If
04-20-2022 09:28 AM
Hi,
Many thanks for that, though the first line should be Dim ObjViewMember
Dim ObjviewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId)
Then it works perfectly.
Mark
04-20-2022 09:34 AM
Other than changing the variable name you declared (viewMember vs. ObjViewMember), what's the difference? It accomplishes the same thing, just personal preference?
04-20-2022 10:08 AM
Hi,
Well it just gave me a compile error until I changed it, that's all. Otherwise, perfect. Appreciate your help in resolving this.
Mark
04-20-2022 10:16 AM
Interesting - I don't have any compile issues. Either way, glad it's working for you!