UD8 Dynamic Calc - Show Zeroes in Cube View column based on time period comparison

[benmac360]

Hello - I created a dynamic UD8 calc for a cube view that shows 12 months of actual, forecast and budget periodic amounts. The calc is designed to show zeroes in the actual scenario column for a period if its POV time is greater than than the global time. I added it to the cube view and it works perfectly when I run it for 2024M12 - the actual columns are all zeroes, while it displays the budget and forecast numbers. When I run the CV for 2023M12, since our global time is still 2023M11 I'm expecting zeroes to be in the Actual column, but I'm still getting the opposite sign YTD "no data zero view" totals instead. Below is the formula in the UD8 member - let me know what changes I need to make. I appreciate the assistance.

Dim gbTimeName As String = BRApi.Workflow.General.GetGlobalTime(si)
Dim gbTimeID As Integer = BRApi.Finance.Time.GetIdFromName(si, gbTimeName)
Dim gbYear As Integer = BRApi.Finance.Time.GetYearFromId(si, gbTimeID)
'Get pov year
Dim pvYear As Integer = api.Time.GetYearFromId(api.Pov.Time.MemberId)
'Get global month
Dim objTimeMemberSubComponents As TimeMemberSubComponents = BRApi.Finance.Time.GetSubComponentsFromName(si, gbTimeName)
Dim gbMonthStr As String = objTimeMemberSubComponents.Month.ToString
Dim gbMonth As Integer = CInt(gbMonthStr)
'Get POV month
Dim pvTimeName As String = api.Pov.Time.MemberID
Dim pvobjTimeMemberSubComponents As TimeMemberSubComponents = BRApi.Finance.Time.GetSubComponentsFromName(si, pvTimeName)
Dim pvMonthStr As String = pvobjTimeMemberSubComponents.Month.ToString
Dim pvMonth As Integer = CInt(pvMonthStr)
Dim gbTime As Integer = gbYear & gbMonth
Dim pvTime As Integer = pvYear & pvMonth
If pvTime < gbTime Then
	Return api.Data.CreateDataCellObject(0.0, False, False)
Else
	Return api.Data.GetDataCell("U8#None").CellAmount
End If

[Henning · Accepted solution]

Hi, the best approach is to debug it by writing your variables into the error log. I would start with gbTime and pvTime and see whether the results matches with what your are expecting. Usually you will see the issue pretty quickly. Best to only use a CV with a single cell so that you know which cell your message refers to and do not fill the error log with unnecessary messages.

You can use the functions api.LogMessage() or BRApi.ErrorLog.LogMessage() to write into the error log.

[benmac360]

I added a couple error log message functions and found the issue was with pvTime - it was returning zero. I made an adjustment to the code by using api.Time.GetPeriodNumFromId for the POV time instead and it now returns the correct month. I reran the cube view and its showing zeroes for the actual scenario columns after 2023M11. Thanks for everyone's help!

[MarkBird]

Hi @benmac360 

Are you just trying to hide derived data? If so, we use the following code to din a UD8 member to do so:

Dim cellStatus As DataCellStatus = api.Data.GetDataCell("U8#None").CellStatus
Dim account As Integer = api.Pov.Account.MemberId
Dim accounttype As String = api.Account.GetAccountType(account).ToString
Dim value As Decimal = api.Data.GetDataCell("U8#None").CellAmount

If cellStatus.IsAnnotationTypeViewMember Or cellStatus.IsDerivedData Then
	Return Nothing
Else If value = Nothing Then
    Return Nothing
Else
    Return value
End If

Alternatively, you can just add the a check for cell status to your logic. See below:

Dim gbTimeName As String = BRApi.Workflow.General.GetGlobalTime(si)
Dim gbTimeID As Integer = BRApi.Finance.Time.GetIdFromName(si, gbTimeName)
Dim gbYear As Integer = BRApi.Finance.Time.GetYearFromId(si, gbTimeID)
'Get pov year
Dim pvYear As Integer = api.Time.GetYearFromId(api.Pov.Time.MemberId)
'Get global month
Dim objTimeMemberSubComponents As TimeMemberSubComponents = BRApi.Finance.Time.GetSubComponentsFromName(si, gbTimeName)
Dim gbMonthStr As String = objTimeMemberSubComponents.Month.ToString
Dim gbMonth As Integer = CInt(gbMonthStr)
'Get POV month
Dim pvTimeName As String = api.Pov.Time.MemberID
Dim pvobjTimeMemberSubComponents As TimeMemberSubComponents = BRApi.Finance.Time.GetSubComponentsFromName(si, pvTimeName)
Dim pvMonthStr As String = pvobjTimeMemberSubComponents.Month.ToString
Dim pvMonth As Integer = CInt(pvMonthStr)
Dim gbTime As Integer = gbYear & gbMonth
Dim pvTime As Integer = pvYear & pvMonth
If pvTime < gbTime Then
	Return api.Data.CreateDataCellObject(0.0, False, False)
Else If cellStatus.IsAnnotationTypeViewMember Or cellStatus.IsDerivedData Then
	Return api.Data.CreateDataCellObject(0.0, False, False)
Else
	Return api.Data.GetDataCell("U8#None").CellAmount
End If

Mark